Generating Excel from DataTable

Feroz Khan

3y1.1kC#

Hello Everyone,

i am generating excel file and its generating as well only issue is some numeric value format is not correct format and because of that i cannot perform any formula. below snap for your refrence.

Code:

cs.dt = (System.Data.DataTable)dataGridView1.DataSource;
System.Data.DataTable dtclone = cs.dt.Clone();
dtclone.Columns[7].DataType = typeof(decimal); //In your case you need to change WaitTime and AssistTime
dtclone.Columns[8].DataType = typeof(decimal);

foreach (DataRow row in cs.dt.Rows)
{
    dtclone.ImportRow(row);
}
int b = 0;
string[,] dataa = new string[dtclone.Rows.Count, dtclone.Columns.Count];
foreach (DataRow row in dtclone.Rows)
{
    int j = 0;
    foreach (DataColumn c in dtclone.Columns)
    {
        dataa[b, j] = row[c].ToString();
        j++;
        // Thread.Sleep(100);
    }
    b++;
}
xlWorkSheet.Range[xlWorkSheet.Cells[4, 1], xlWorkSheet.Cells[dtclone.Rows.Count + 3, dtclone.Columns.Count]].value = dataa;

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Feroz Khan3y ago
Hi Cr Bhargavi,

Thank you for your reply i did the same before also its changing the format with custom but issue still not working in the last column you can see i am using Total its not coming because format not correct and my first screen short there is one option convert to number one i will press then calculating. below new screen for refrence.

+1

Cr Bhargavi3y ago

// ... (previous code)

// Assuming that columns 7 and 8 are the numeric columns
for (int colIndex = 1; colIndex <= dtclone.Columns.Count; colIndex++)
{
    if (dtclone.Columns[colIndex - 1].DataType == typeof(decimal))
    {
        xlWorkSheet.Range[xlWorkSheet.Cells[4, colIndex], xlWorkSheet.Cells[dtclone.Rows.Count + 3, colIndex]].NumberFormat = "0.00"; // Adjust the format as needed
    }
}

xlWorkSheet.Range[xlWorkSheet.Cells[4, 1], xlWorkSheet.Cells[dtclone.Rows.Count + 3, dtclone.Columns.Count]].Value = dataa;

// ... (rest of the code)

Leon D3y ago

You can export data from datatable to excel using Free Spire.XLS.

https://www.e-iceblue.com/Download/download-excel-for-net-free.html

Here is a simple example:

//Initialize a Workbook instance
Workbook workbook = new Workbook();
            
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Insert datatable to the worksheet
sheet.InsertDataTable(datatable,true,1,1,-1,-1);

//Set header style
CellStyle styleHeader = sheet.Rows[0].Style;
styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
styleHeader.VerticalAlignment = VerticalAlignType.Center;
styleHeader.KnownColor = ExcelColors.Green;
styleHeader.Font.KnownColor = ExcelColors.White;
styleHeader.Font.IsBold = true;

//Set body style
CellStyle oddStyle = workbook.Styles.Add("oddStyle");
oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
oddStyle.KnownColor = ExcelColors.LightGreen1;

CellStyle evenStyle = workbook.Styles.Add("evenStyle");
evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
evenStyle.KnownColor = ExcelColors.LightTurquoise;

foreach( CellRange range in  sheet.AllocatedRange.Rows)
{
    if (range.Row % 2 == 0)
        range.CellStyleName = evenStyle.Name;
    else
        range.CellStyleName = oddStyle.Name;
}

//Autofit column widths and row heights
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();

//Set row height of the header row
sheet.Rows[0].RowHeight = 20;

//Save the resulting file
workbook.SaveToFile("DataTableToExcel.xlsx", ExcelVersion.Version2013);