Ryden Choi Ryden Choi - 1 month ago 19
C# Question

How can I export datagridview cell formatting value to excel?

I`m making windows form application.

When Click Export button, datagridview`s data export to Excel file.

I already built that code and it works well. But today I updated my code.

I add CellFormatting event of Datagridview, change file size value, and display

it to the Datagridview.

And then, I exported to excel but in excel file, still original data showend

which means, original data is 451936256 and converted data is 431MB

In excel sheet, it showed 451936256.

my code is below

//Button click Event
private void mtbtnExportToExcel_Click(object sender, EventArgs e)
{
DataGridView[] dgv = new DataGridView[] { mgrdWebApplication, mgrdContentDatabase, mgrdSiteCollections, mgrdSites, mgrdLists, mgridDocumentLibrary };
mtProgressStatus.Spinning = true;
ExportDataToExcel(dgv, "MigStatus");
mtProgressStatus.Spinning = false;

}
//Export gridview data to excel
private bool ExportDataToExcel(DataGridView[] dgv, string fileName)
{
string saveFileName = "";
SaveFileDialog saveDialog1 = new SaveFileDialog();
saveDialog1.DefaultExt = "xlsx";
saveDialog1.Filter = "Excel file|*.xlsx";
saveDialog1.FileName = fileName;
saveDialog1.ShowDialog();
saveFileName = saveDialog1.FileName;

if (saveFileName.IndexOf(":") < 0)
return false;

Excel.Application xlApp = new Excel.Application();

if (xlApp == null)
{
MessageBox.Show("Can`t create Excel");
return false;
}

Excel.Workbooks workBooks = xlApp.Workbooks;
Excel.Workbook workBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

try
{
for (int index = 0; index < dgv.Length; index++)
{
for (int i = 0; i < dgv[index].ColumnCount; i++)
{
if (dgv[index].Columns[i].Visible)
workSheet.Cells[1, i + 1] = dgv[index].Columns[i].HeaderText;
}

for (int r = 0; r < dgv[index].Rows.Count; r++)
{
for (int i = 0; i < dgv[index].ColumnCount; i++)
{
if (dgv[index].Columns[i].Visible)
workSheet.Cells[r + 2, i + 1] = dgv[index].Rows[r].Cells[i].Value.ToString();
}
Application.DoEvents();
}
((Excel.Range)workSheet.Rows[1, Type.Missing]).Font.Bold = true;
workSheet.Columns.EntireColumn.AutoFit();

if (index < dgv.Length - 1)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add();
}
}
}
catch(Exception ex)
{
//LogWrite logWrites = new LogWrite();
writeLog.LogsWrite(ex.ToString());
}

if (saveFileName != "")
{
try
{
workBook.Saved = true;
workBook.SaveCopyAs(saveFileName);
}
catch(Exception ex)
{
MessageBox.Show("Error, file is already opened!\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();
MessageBox.Show("File : " + fileName + ".xls saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

return true;
}
//CellFormatting Event
private void mgrdContentDatabase_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if(this.mgrdContentDatabase.Columns[e.ColumnIndex].HeaderText== "Size(GB)")
{
if (e.Value != null)
{
CovertFileSize(e);
}
}
}
//convert to file size
private void CovertFileSize(DataGridViewCellFormattingEventArgs formatting)
{
if (formatting.Value != null)
{
try
{
long bytes;
bytes = Convert.ToInt64(formatting.Value);
string size = "0 Bytes";

//GB
if (bytes >= 1073741824.0)
size = String.Format("{0:##.##}", bytes / 1073741824.0) + " GB";
//MB
else if (bytes >= 1048576.0)
size = String.Format("{0:##.##}", bytes / 1048576.0) + " MB";
//KB
else if (bytes >= 1024.0)
size = String.Format("{0:##.##}", bytes / 1024.0) + " KB";
//Bytes
else if (bytes > 0 && bytes < 1024.0)
size = bytes.ToString() + " Bytes";

formatting.Value = size;
formatting.FormattingApplied = true;
}
catch(FormatException)
{
formatting.FormattingApplied = false;
}
}

}


I want to export converted data to excel.

Please help me how can I fix or add my code..

thanks

Answer

You should use FormattedValue property of the cell:

string value = string.Format("{0}" , dataGridView1.Rows[r].Cells[i].FormattedValue);