Datatable to excel, datatableexcel

Datatable to excel, datatableexcel

Public static void ExportToExcel (System. Data. DataTable dt)
{
If (dt = null) return;
Microsoft. Office. Interop. Excel. Application xlApp = new Microsoft. Office. Interop. Excel. Application ();
If (xlApp = null)
{
MessageBox. Show ("an Excel object cannot be created. Excel may not be installed on your computer ");
Return;
}

System. Windows. Forms. SaveFileDialog saveDia = new SaveFileDialog ();
SaveDia. Filter = "Excel | *. xls ";
SaveDia. Title = "export as an Excel file ";

If (saveDia. ShowDialog () = System. Windows. Forms. DialogResult. OK
&&! String. Empty. Equals (saveDia. FileName ))
{
Microsoft. Office. Interop. Excel. Workbooks workbooks = xlApp. Workbooks;
Microsoft. Office. Interop. Excel. Workbook workbook = workbooks. Add (Microsoft. Office. Interop. Excel. XlWBATemplate. xlWBATWorksheet );
Microsoft. Office. Interop. Excel. Worksheet worksheet = (Microsoft. Office. Interop. Excel. Worksheet) workbook. Worksheets [1]; // get sheet1
Microsoft. Office. Interop. Excel. Range range = null;

Long totalCount = dt. Rows. Count;
Long rowRead = 0;
Float percent = 0;
String fileName = saveDia. FileName;

// Write the title
For (int I = 0; I <dt. Columns. Count; I ++)
{
Worksheet. Cells [1, I + 1] = dt. Columns [I]. ColumnName;
Range = (Microsoft. Office. Interop. Excel. Range) worksheet. Cells [1, I + 1];
// Range. Interior. ColorIndex = 15; // background color
Range. Font. Bold = true; // Bold
Range. HorizontalAlignment = Microsoft. Office. Interop. Excel. XlHAlign. xlHAlignCenter; // center
// Add a border
Range. borderAround (Microsoft. office. interop. excel. xlLineStyle. xlContinuous, Microsoft. office. interop. excel. xlBorderWeight. xlThin, Microsoft. office. interop. excel. xlColorIndex. xlColorIndexAutomatic, null );

// Range. ColumnWidth = 4.63; // you can specify the column width.
// Range. EntireColumn. AutoFit (); // automatically adjust the column width
// R1.EntireRow. AutoFit (); // automatically adjust the Row Height
}

// Write content

For (int r = 0; r <dt. DefaultView. Count; r ++)
{
For (int I = 0; I <dt. Columns. Count; I ++)
{
Worksheet. Cells [r + 2, I + 1] = dt. DefaultView [r] [I];
Range = (Microsoft. Office. Interop. Excel. Range) worksheet. Cells [r + 2, I + 1];
Range. Font. Size = 9; // Font Size
// Add a border
Range. borderAround (Microsoft. office. interop. excel. xlLineStyle. xlContinuous, Microsoft. office. interop. excel. xlBorderWeight. xlThin, Microsoft. office. interop. excel. xlColorIndex. xlColorIndexAutomatic, null );
Range. EntireColumn. AutoFit (); // automatically adjusts the column width.
}

RowRead ++;
Percent = (float) (100 * rowRead)/totalCount;
System. Windows. Forms. Application. DoEvents ();
}

Range. Borders [Microsoft. Office. Interop. Excel. XlBordersIndex. xlInsideHorizontal]. Weight = Microsoft. Office. Interop. Excel. XlBorderWeight. xlThin;
If (dt. Columns. Count> 1)
{
Range. Borders [Microsoft. Office. Interop. Excel. XlBordersIndex. xlInsideVertical]. Weight = Microsoft. Office. Interop. Excel. XlBorderWeight. xlThin;
}

Try
{
Workbook. Saved = true;
Workbook. SaveCopyAs (fileName );
}
Catch (Exception ex)
{
MessageBox. Show ("An error occurred while exporting the file. The file may be opened! \ N "+ ex. Message );
Return;
}

Workbooks. Close ();
If (xlApp! = Null)
{
XlApp. Workbooks. Close ();
XlApp. Quit ();
Int generation = System. GC. GetGeneration (xlApp );
System. Runtime. InteropServices. Marshal. ReleaseComObject (xlApp );
XlApp = null;
System. GC. Collect (generation );

}

GC. Collect (); // forcibly destroy
# Region forcibly kills the recently opened Excel Process

System. Diagnostics. Process [] excelProc = System. Diagnostics. Process. GetProcessesByName ("EXCEL ");
System. DateTime startTime = new DateTime ();
Int m, killId = 0;
For (m = 0; m <excelProc. Length; m ++)
{
If (startTime <excelProc [m]. StartTime)
{
StartTime = excelProc [m]. StartTime;
KillId = m;
}
}
If (excelProc [killId]. HasExited = false)
{
ExcelProc [killId]. Kill ();
}

# Endregion
MessageBox. Show ("exported successfully! ");
}
}