Hi,
For formatting you will have to create a workbook using interop.
Like this:
1.) Create a sample dt, with new row/ break for a column:
System.Data.DataTable dt = new System.Data.DataTable();
DataSet ds = new DataSet();
dt.TableName = "tablename";
dt.Columns.Add("Code", typeof(string));
dt.Columns.Add("Price", typeof(int));
DataRow dr = dt.NewRow();
dr["Code"] = "ABC " + Environment.NewLine + " qrt";
dr["Price"] = 12;
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["Code"] = "PQR";
dr1["Price"] = 1;
dt.Rows.Add(dr1);
2.) Create the workbook function using Interop.
public static void CreateWorkbook(System.Data.DataTable dt, String path)
{
int rowindex = 0;
int columnindex = 0;
Microsoft.Office.Interop.Excel.Application wapp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Worksheet wsheet;
Microsoft.Office.Interop.Excel.Workbook wbook;
wapp.Visible = false;
wbook = wapp.Workbooks.Add(true);
wsheet = (Worksheet)wbook.ActiveSheet;
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
wsheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowindex++;
columnindex = 0;
foreach (DataColumn col in dt.Columns)
{
columnindex++;
wsheet.Cells[rowindex + 1, columnindex] = row[col.ColumnName];
}
}
}
catch (Exception ex)
{
String err = ex.Message;
}
wapp.UserControl = true;
wbook.SaveAs("d:\\Test", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbook.Close(null,null,null);
3.) To save the file, call the function like this:
CreateWorkbook(dt,"D:/");
You can apply many formatting types to cells, as per your requirement.
Also, this will save automatically, without user prompt.
Regards,
Praneet