WinForm对EXCEL的操作（三）

终于有时间可把下面的WinForm对EXCEL的操作方法与大家分享了。前面介绍了如何把获取SHEET名称和如何获取SHEET里面数据两种方法。其实这两种方法都是在为EXCEL数据导入数据库作准备，至数据怎么导入数据库将会在以后学习不断分享给大家。下面就介绍下如何把数据放SHEET中：

方法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;                 //工作薄 9                Microsoft.Office.Interop.Excel.Worksheet xSt;      //工作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        }

方法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 = "保存为Excel文件";
 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                    //写入列标题  
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                    //写入列内容  
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    }

显然第二种方法要比第一种方法执行速度要快得多，但是第二种方法保存的文件不是真正的EXCEL文件，而用流写的一定格式的类EXCEL文件。希望这种方法都能够帮助你。如何还有其它方法，请各位提示。以供大家共同进步。

经过网友落叶潇潇雨指点，今天也看了一下他说的那种方法。确实简单实用多了，感觉他对我的指点。现将第三种方法分享给大家。

首先在自己工程中添加MyXls.SL2.dll引用，现在把链接地址给大家：

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

方法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 = VerticalAlignments.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
            {
            }
        }