WinForm operations on EXCEL (3)

 

 

Finally, we have time to share the following WinForm EXCEL operations with you. The previous section describes how to get the SHEET name and how to obtain the data in the SHEET. In fact, both methods are in preparation for importing EXCEL data into the database. We will continue to share with you how to import data into the database later. The following describes how to put data in SHEET:

Method 1:

View Code

1 publicvoidDataToExcel (DataTable dt)
2 {
3 try
4 {
5 if (dt = null) return;
6
7 Microsoft. Office. Interop. Excel. ApplicationClass myExcel = newMicrosoft. Office. Interop. Excel. ApplicationClass ();
8 Microsoft. Office. Interop. Excel. Workbook xBk; // working thin 9 Microsoft. Office. Interop. Excel. Worksheet xSt; // working Sheet
10
11 xBk = myExcel. Workbooks. Add (true );
12 xSt = (Microsoft. Office. Interop. Excel. Worksheet) xBk. ActiveSheet;
13
14 myExcel. Visible = true;
15
16 for (inti = 1; I <= dt. Columns. Count; ++ I)
17 {
18 xSt. Cells [1, I] = dt. Columns [I-1]. ColumnName;
19}
20
21 for (inti = 2; I <= dt. Rows. Count + 1; ++ I)
22 {
23 for (intj = 1; j <= dt. Columns. Count; ++ j)
24 {
25 xSt. Cells [I, j] = dt. Rows [I-2] [j-1]. ToString ();
26}
27}
28
29 for (inti = 1; I <= dt. Columns. Count; ++ I)
30 {
31 Microsoft. office. interop. excel. range selectRange = xSt. get_Range (xSt. cells [1, I], xSt. cells [dt. rows. count + 1, I]);
32 selectRange. Columns. AutoFit ();
33}
34}
35 catch
36 {
37
38}
39}

Method 2:

View Code

1 publicvoiddataToExcel (DataTable dt)
2 {
3
4 SaveFileDialog dlg = newSaveFileDialog ();
5 dlg. Filter = "Execl files (*. xls) | *. xls ";
6 dlg. FilterIndex = 0;
7 dlg. RestoreDirectory = true;
8 dlg. Title = "Save As an Excel file ";
9 dlg. FileName = DateTime. Now. Ticks. ToString (). Trim ();
10
11 if (dlg. ShowDialog () = DialogResult. OK)
12 {
13 Stream myStream = dlg. OpenFile ();
14 StreamWriter sw = newStreamWriter (myStream, System. Text. Encoding. GetEncoding (-0 ));
15 stringcolumnTitle = "";
16 try
17 {
18 // write Column Title
19 for (inti = 0; I <dt. Columns. Count; I ++)
20 {
21 if (I> 0)
22 {
23 columnTitle + = "\ t ";
24}
25 columnTitle + = dt. Columns [I]. ColumnName;
26}
27 sw. WriteLine (columnTitle );
28
29 // write column content
30 for (intj = 0; j <dt. Rows. Count; j ++)
31 {
32 stringcolumnValue = "";
33 for (intk = 0; k <dt. Columns. Count; k ++)
34 {
35 if (k> 0)
36 {
37 columnValue + = "\ t ";
38}
39 if (dt. Rows [j] [k]. ToString () = "")
40 columnValue + = "null ";
41 else
42 columnValue + = dt. Rows [j] [k]. ToString (). Trim ();
43}
44 sw. WriteLine (columnValue );
45}
46}
47 catch (Exception e)
48 {
49 MessageBox. Show (e. ToString ());
50}
51 finally
52 {
53 sw. Close ();
54 myStream. Close ();
55}
56}
57}
58}

Obviously, the second method is much faster than the first method, but the second method saves files in a certain format instead of an EXCEL file. I hope this method will help you. For more information, see. For common progress.

After being instructed by fallen leaves, I also read the method he said today. It's actually much simpler and more practical. I feel like he gave me some advice. Now I will share with you the third method.

First, add the MyXls. SL2.dll reference to your project. Now, we will give you the link address:

Http://files.cnblogs.com/aland-liu/MyXls.SL2.rar

Method 3:

Private void ExportExcel (string fileName, System. Data. DataTable dt)
{
Try
{
XlsDocument xls = new XlsDocument ();
Xls. FileName = fileName;

String s = fileName;
S = s. Substring (s. LastIndexOf ('\') + 1, s. LastIndexOf ('.')-s. LastIndexOf ('\')-1 );
Org. in2bits. MyXls. Worksheet sheet = xls. Workbook. Worksheets. AddNamed (s );

ColumnInfo cinfo = new ColumnInfo (xls, sheet );
Cinfo. Collapsed = true;
Cinfo. ColumnIndexStart = 0;
Cinfo. ColumnIndexEnd = (ushort) dt. Columns. Count;
Sheet. AddColumnInfo (cinfo );

XF cellXF = xls. NewXF ();
CellXF. VerticalAlignment = verticalignments. Centered;
CellXF. HorizontalAlignment = HorizontalAlignments. Centered;
// CellXF. Font. Bold = true;

Cells cells = sheet. Cells;

For (int I = 1; I <= dt. Columns. Count; I ++)
{
Cells. Add (1, I, dt. Columns [I-1]. ColumnName, cellXF );
}

For (int I = 0; I <dt. Rows. Count; I ++)
{
For (int j = 0; j <dt. Columns. Count; j ++)
{
Cells. Add (2 + I, 1 + j, dt. Rows [I] [j]. ToString (), cellXF );
}
}

Xls. Save ();
// System. Diagnostics. Process. Start (fileName );
}
Catch
{
}
}