Export Excel in WinForm

Hello, everyone! I will share with you the Excel export from the database I wrote today. Please advise if you have any shortcomings! Thank you!

Effect description:

You can input different datasets to control the number of sheet in Excel. This example is based on two examples for your reference only:

# Region export Excel
Private void outPutExcel (System. Data. DataTable dt, System. Data. DataTable dt2)
{
If (dt = null) return;
Excel. Application xlApp = new Excel. Application ();
If (xlApp = null)
{
// ClsLog. m_CreateErrorLog ("an Excel object cannot be created. Excel may not be installed on the computer ","","");
Return;
}
// Create an Excel Object
Excel. Workbooks workbooks = xlApp. Workbooks;
Excel. Workbook workbook = workbooks. Add (Excel. XlWBATemplate. xlWBATWorksheet );
// Excel. Worksheet worksheet = (Excel. Worksheet) workbook. Worksheets [1]; // obtain sheet1
Excel. Worksheet worksheet = null;
For (int sheetcount = 0; sheetcount <2; sheetcount ++) // Loop Based on the number of sheet you need. Here are two
{
If (worksheet = null)
{
Worksheet = (Excel. Worksheet) workbook. Worksheets. Add (Type. Missing, Type. Missing, 1, Type. Missing );
}
Else
{
Worksheet = (Excel. Worksheet) workbook. Worksheets. Add (Type. Missing, worksheet, 1, Type. Missing );
}
Excel. Range range = null;
If (sheetcount = 0)
{
Long totalCount = dt. Rows. Count;
Long rowRead = 0;
Float percent = 0;
Worksheet. Name = "plaintext Item 1"; // the Name of the first sheet displayed in Excel
//// Write the title
For (int I = 0; I <dt. Columns. Count; I ++)
{
Worksheet. Cells [1, I + 1] = dt. Columns [I]. ColumnName;
Range = (Excel. Range) worksheet. Cells [1, I + 1];
Range. Interior. ColorIndex = 15; // background color
Range. Font. Bold = true; // Bold
Range. HorizontalAlignment = Excel. XlHAlign. xlHAlignCenter; // center
// Add a border
Range. BorderAround (Excel. XlLineStyle. xlContinuous, Excel. XlBorderWeight. xlThin, Excel. XlColorIndex. xlColorIndexAutomatic, null );
Range. ColumnWidth = 4.63; // you can specify the column width.
Range. EntireColumn. AutoFit (); // automatically adjusts the column width.
// R1.EntireRow. AutoFit (); // automatically adjust the Row Height
}

// Write content
For (int r = 0; r <dt. Rows. Count; r ++)
{
For (int I = 0; I <dt. Columns. Count; I ++)
{
Worksheet. Cells [r + 2, I + 1] = dt. Rows [r] [I];
Range = (Excel. Range) worksheet. Cells [r + 2, I + 1];
Range. Font. Size = 9; // Font Size
// Add a border
Range. BorderAround (Excel. XlLineStyle. xlContinuous, Excel. XlBorderWeight. xlThin, Excel. XlColorIndex. xlColorIndexAutomatic, null );
Range. EntireColumn. AutoFit (); // automatically adjusts the column width.
}
RowRead ++;
Percent = (float) (100 * rowRead)/totalCount;
}
Range. Borders [Excel. XlBordersIndex. xlInsideHorizontal]. Weight = Excel. XlBorderWeight. xlThin;
If (dt. Columns. Count> 1)
{
Range. Borders [Excel. XlBordersIndex. xlInsideVertical]. Weight = Excel. XlBorderWeight. xlThin;
}
}
Else if (sheetcount = 1)
{
Long totalCount = dt2.Rows. Count;
Long rowRead = 0;
Float percent = 0;
Worksheet. Name = "proposed solution ";
//// Write the title
For (int I = 0; I <dt2.Columns. Count; I ++)
{
Worksheet. Cells [1, I + 1] = dt2.Columns [I]. ColumnName;
Range = (Excel. Range) worksheet. Cells [1, I + 1];
Range. Interior. ColorIndex = 15; // background color
Range. Font. Bold = true; // Bold
Range. HorizontalAlignment = Excel. XlHAlign. xlHAlignCenter; // center
// Add a border
Range. BorderAround (Excel. XlLineStyle. xlContinuous, Excel. XlBorderWeight. xlThin, Excel. XlColorIndex. xlColorIndexAutomatic, null );
Range. ColumnWidth = 14.63; // you can specify the column width.
Range. EntireColumn. AutoFit (); // automatically adjusts the column width.
// R1.EntireRow. AutoFit (); // automatically adjust the Row Height
}
// Write content
For (int r = 0; r <dt2.Rows. Count; r ++)
{
For (int I = 0; I <dt2.Columns. Count; I ++)
{
Worksheet. Cells [r + 2, I + 1] = dt2.Rows [r] [I];
Range = (Excel. Range) worksheet. Cells [r + 2, I + 1];
Range. Font. Size = 9; // Font Size
// Add a border
Range. BorderAround (Excel. XlLineStyle. xlContinuous, Excel. XlBorderWeight. xlThin, 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 [Excel. XlBordersIndex. xlInsideHorizontal]. Weight = Excel. XlBorderWeight. xlThin;
If (dt2.Columns. Count> 1)
{
Range. Borders [Excel. XlBordersIndex. xlInsideVertical]. Weight = Excel. XlBorderWeight. xlThin;
}
}
}

// The path and name specified for storing Excel on the server are as follows:
Try
{
String tPath = System. AppDomain. CurrentDomain. BaseDirectory;
If (! Directory. Exists (tPath + "Excel "))
{
Directory. CreateDirectory (tPath + "Excel ");
}
Workbook. SaveCopyAs (tPath + "Excel" + "\" + System. DateTime. Today. ToString ("yyyyMMdd") + pCompany + "test .xls ");
}
// WriteLog ("Exce attachment generated successfully! "); // Here is one of my methods for logging log messages
}
Catch (Exception ex)
{
String strMsgErr = "when an Excel attachment is generated, it is often found that the attachment information is:" + ex. ToString ();
// WriteLog (strMsgErr); // the log file is also written.
}
// When exporting Excel, the Excel process is called in the process. After exporting, the process must be forcibly killed.
Try
{
If (xlApp! = Null)
{
Int lpdwProcessId;
GetWindowThreadProcessId (new IntPtr (xlApp. Hwnd), out lpdwProcessId );
System. Diagnostics. Process. GetProcessById (lpdwProcessId). Kill ();
}
}
Catch (Exception ex)
{
Console. WriteLine ("Delete Excel Process Error:" + ex. Message );
}
}
# Endregion

Here, the Excel file is exported from the database and generated in the corresponding directory.

This is just a simple export method. The key point is that we should use this method to apply it to other Excel export functional requirements, in this case, we may need to change the number and type of input parameters.

I will also congratulate you on the dynamic export of Excel from the database and sending it as an attachment to the specified recipient, and save the sent email as a binary or XML file for our pursuit. If the email fails to be sent, we do not need to regenerate everything, you only need to call the saved binary file or XML file to resend the email, which is convenient and convenient!

Okay. Let's talk about it today! I hope my friends with better implementation methods can reply to me and share your information! Bye!