I'm trying to generate an Excel spreadsheet based on a DataTable, all data is in the given format, however the Data format is in the American standard ("MM / dd / YYYY"). Here is the code for the function:
//Metodo que exporta um grid para planilha exel
public static void exportaExcelFormatado(DataGridView grid, String cabecalho, bool style = false, int retirarUltimasColunas = 0, DataTable dat = null, String rodape = "")
{
Log.Trace(logger, "exportaExcelFormatado");
int linha = 2;
int qtdeColunas = grid.Columns.Count - retirarUltimasColunas;
try
{
if (grid.Rows.Count > 0)
{
SaveFileDialog salvar = new SaveFileDialog();// novo
Excel.Application App; // Aplicação Excel
Excel.Workbook pasta; // Pasta
Excel.Worksheet planilha; // Planilha
object misValue = System.Reflection.Missing.Value;
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.* | *.*";
DialogResult resultado = salvar.ShowDialog(); // mostra
if (resultado == DialogResult.OK)
{
App = new Excel.Application();
pasta = App.Workbooks.Add(misValue);
planilha = (Excel.Worksheet)pasta.Worksheets.get_Item(1);
planilha.Cells[1, 1] = cabecalho;
planilha.Range["A1", GetColumnLetterByIndex(qtdeColunas) + "1"].Merge();
linha = 3;
for (int x = 1; x < qtdeColunas + 1; x++)
{
planilha.Cells[linha - 1, x] = grid.Columns[x - 1].HeaderText;
}
planilha.Range["A2", GetColumnLetterByIndex(qtdeColunas) + "1"].Font.Size = 10;
planilha.Range["A2", GetColumnLetterByIndex(qtdeColunas) + "1"].Font.Bold = true;
planilha.Range["A2", GetColumnLetterByIndex(qtdeColunas) + "1"].Font.Name = "Tahoma";
// passa as celulas do DataGridView para a Pasta do Excel
for (int i = 0; i <= grid.Rows.Count - 1; i++)
{
for (int j = 0; j <= qtdeColunas - 1; j++)
{
DataGridViewCell cell = grid[j, i];
if (style)
{
if (cell.Style.BackColor.IsKnownColor == true)
planilha.Cells[i + linha, j + 1].Interior.Color = ColorTranslator.ToOle(cell.Style.BackColor);
if (grid.Rows[i].DefaultCellStyle.BackColor.IsKnownColor == true)
planilha.Cells[i + linha, j + 1].Interior.Color = ColorTranslator.ToOle(grid.Rows[i].DefaultCellStyle.BackColor);
if (cell.Style.ForeColor.IsKnownColor == true)
planilha.Cells[i + linha, j + 1].Font.Color = ColorTranslator.ToOle(cell.Style.ForeColor);
}
DateTime data;
if (cell.ValueType.Name == "DateTime" || DateTime.TryParse(cell.Value.ToString(),out data))
{
try
{
Excel.Range rg = (Excel.Range)planilha.Cells[i + linha, j + 1];
data = new DateTime();
data = DateTime.Parse(cell.Value.ToString());
if (data.Hour == 0 && data.Minute == 0 && data.Second == 0 && data.Millisecond == 0)
{
rg.EntireColumn.NumberFormatLocal = "dd/MM/aaaa";
planilha.Cells[i + linha, j + 1] = data.Day.ToString("00") + "/" + data.Month.ToString("00") + "/" + data.Year.ToString("0000");
}
else
{
planilha.Cells[i + linha, j + 1] = data.ToString();
}
}
catch
{
planilha.Cells[i + linha, j + 1] = "";
}
}
else
{
try
{
if (cell.ValueType.Name == "String")
{
String valor = "";
try
{
valor = cell.Value.ToString();
}
catch (Exception)
{
valor = "";
}
if (valor.Length > 50)
{
valor = valor.Substring(0, 50);
}
planilha.Cells[i + linha, j + 1] = valor;
}
else
{
if (cell.ValueType.Name == "Nullable'1")
{
if (cell.Style.NullValue != "")
{
planilha.Cells[i + linha, j + 1] = cell.Style.NullValue.ToString();
}
else
{
planilha.Cells[i + linha, j + 1] = cell.EditedFormattedValue.ToString();
}
}
else
{
try
{
planilha.Cells[i + linha, j + 1] = cell.Value.ToString();
}
catch (Exception)
{
planilha.Cells[i + linha, j + 1] = "";
}
}
}
}
catch (Exception)
{
planilha.Cells[i + linha, j + 1] = cell.Value.ToString();
}
}
}
}
if (dat != null)
{
for (int lin = 0; lin < dat.Rows.Count; lin++)
{
for (int coluna = 0; coluna < dat.Columns.Count; coluna++)
{
if (dat.Rows[lin][coluna].GetType().UnderlyingSystemType.Name == "PictureBox")
{
PictureBox box = (PictureBox)dat.Rows[lin][coluna];
if (box != null)
{
planilha.Cells[grid.Rows.Count + 3 + lin, coluna + 2].Interior.Color = ColorTranslator.ToOle(box.BackColor);
}
}
else
{
planilha.Cells[grid.Rows.Count + 3 + lin, coluna + 2] = dat.Rows[lin][coluna];
planilha.Cells[grid.Rows.Count + 3 + lin, coluna + 2].Font.Bold = true;
}
}
}
}
//Cabeçaho
planilha.Range["A1", GetColumnLetterByIndex(qtdeColunas) + "1"].Font.Size = 20;
planilha.Range["A1", GetColumnLetterByIndex(qtdeColunas) + "1"].Font.Name = "Tahoma";
planilha.Range["A1", GetColumnLetterByIndex(qtdeColunas) + "1"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Corpo
planilha.Range["A3", GetColumnLetterByIndex(qtdeColunas) + grid.Rows.Count + 2].Font.Size = 8;
planilha.Range["A3", GetColumnLetterByIndex(qtdeColunas) + grid.Rows.Count + 2].Font.Name = "Tahoma";
//colunas
planilha.Range["A2", GetColumnLetterByIndex(qtdeColunas) + "2"].Font.Size = 10;
planilha.Range["A2", GetColumnLetterByIndex(qtdeColunas) + "2"].Font.Bold = true;
planilha.Range["A2", GetColumnLetterByIndex(qtdeColunas) + "2"].Font.Name = "Tahoma";
int cont = 0;
if (dat != null)
{
cont = dat.Rows.Count;
}
int linhaFinal = grid.Rows.Count + cont + 3;
planilha.Cells[linhaFinal, qtdeColunas] = "Relatório gerado em: " + DateTime.Now.Date.ToShortDateString();
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].Font.Size = 7;
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].Font.Name = "Tahoma";
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
if (rodape != "")
{
linhaFinal += 1;
planilha.Cells[linhaFinal, qtdeColunas] = rodape;
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].Merge();
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].Font.Size = 7;
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].Font.Name = "Tahoma";
planilha.Range["A" + linhaFinal, GetColumnLetterByIndex(qtdeColunas) + linhaFinal].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
}
//ORIENTAÇÃO DE PÁGINA A4 E RETRATO
planilha.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
try
{
planilha.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
}
catch (Exception erro1)
{
}
//Ajustando as colunas do excel
planilha.Cells.EntireColumn.AutoFit();
pasta.SaveAs(salvar.FileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
pasta.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
questions.confirmaExportacao();
}
}
else
{
questions.erroExportação();
}
}
catch (Exception erro)
{
Log.Erro(logger, "exportaExcelFormatado", erro.Message);
questions.erroExportação();
}
}
The section responsible for formatting this column:
DateTime data;
if (cell.ValueType.Name == "DateTime" || DateTime.TryParse(cell.Value.ToString(),out data))
{
try
{
Excel.Range rg = (Excel.Range)planilha.Cells[i + linha, j + 1];
data = new DateTime();
data = DateTime.Parse(cell.Value.ToString());
if (data.Hour == 0 && data.Minute == 0 && data.Second == 0 && data.Millisecond == 0)
{
rg.EntireColumn.NumberFormatLocal = "dd/MM/aaaa";
planilha.Cells[i + linha, j + 1] = data.Day.ToString("00") + "/" + data.Month.ToString("00") + "/" + data.Year.ToString("0000");
}
else
{
planilha.Cells[i + linha, j + 1] = data.ToString();
}
}
catch
{
planilha.Cells[i + linha, j + 1] = "";
}
}
UPDATE:
So I can leave the date in the right format, but the year appears as "yyyy" in excel.
if (cell.ValueType.Name == "DateTime" || IsDate(cell.Value.ToString()))
{
try
{
Excel.Range rg = (Excel.Range)planilha.Cells[i + linha, j + 1];
DateTime data = DateTime.Parse(cell.Value.ToString());
if (data.Hour == 0 && data.Minute == 0 && data.Second == 0 && data.Millisecond == 0)
{
rg.EntireColumn.NumberFormatLocal = "dd/MM/aaaa";
//DateTime x = data.Day.ToString("00") + "/" + data.Month.ToString("00") + "/" + data.Year.ToString("0000");
String x = String.Format("{0:dd/MM/aaaa}", data);
planilha.Cells[i + linha, j + 1] = x;
// planilha.Cells[i + linha, j + 1] = data.Day.ToString("00") + "/" + data.Month.ToString("00") + "/" + data.Year.ToString("0000");
}
else
{
planilha.Cells[i + linha, j + 1] = data.TimeOfDay.ToString();
}
}
catch
{
planilha.Cells[i + linha, j + 1] = "";
}
}