一切福田，不離方寸，從心而覓，感無不通。

Daily Archives: 2018年6月11日

C# 导出 Excel 的各种方法总结

第一种：使用 Microsoft.Office.Interop.Excel.dll 首先需要安装 office 的 excel，然后再找到 Microsoft.Office.Interop.Excel.dll 组件，添加到引用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
public void ExportExcel(DataTable dt)
        {
            if (dt != null)
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
 
                if (excel == null)
                {
                    return;
                }
 
                //设置为不可见，操作在后台执行，为 true 的话会打开 Excel
                excel.Visible = false;
 
                //打开时设置为全屏显式
                //excel.DisplayFullScreen = true;
 
                //初始化工作簿
                Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
 
                //新增加一个工作簿，Add（）方法也可以直接传入参数 true
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                //同样是新增一个工作簿，但是会弹出保存对话框
                //Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
 
                //新增加一个 Excel 表(sheet)
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
 
                //设置表的名称
                worksheet.Name = dt.TableName;
                try
                {
                    //创建一个单元格
                    Microsoft.Office.Interop.Excel.Range range;
 
                    int rowIndex = 1;       //行的起始下标为 1
                    int colIndex = 1;       //列的起始下标为 1
 
                    //设置列名
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        //设置第一行，即列名
                        worksheet.Cells[rowIndex, colIndex + i] = dt.Columns[i].ColumnName;
 
                        //获取第一行的每个单元格
                        range = worksheet.Cells[rowIndex, colIndex + i];
 
                        //设置单元格的内部颜色
                        range.Interior.ColorIndex = 33;
 
                        //字体加粗
                        range.Font.Bold = true;
 
                        //设置为黑色
                        range.Font.Color = 0;
 
                        //设置为宋体
                        range.Font.Name = "Arial";
 
                        //设置字体大小
                        range.Font.Size = 12;
 
                        //水平居中
                        range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
 
                        //垂直居中
                        range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    }
 
                    //跳过第一行，第一行写入了列名
                    rowIndex++;
 
                    //写入数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            worksheet.Cells[rowIndex + i, colIndex + j] = dt.Rows[i][j].ToString();
                        }
                    }
 
                    //设置所有列宽为自动列宽
                    //worksheet.Columns.AutoFit();
 
                    //设置所有单元格列宽为自动列宽
                    worksheet.Cells.Columns.AutoFit();
                    //worksheet.Cells.EntireColumn.AutoFit();
 
                    //是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
                    excel.DisplayAlerts = false;
 
                    //保存写入的数据，这里还没有保存到磁盘
                    workbook.Saved = true;
 
                    //设置导出文件路径
                    string path = HttpContext.Current.Server.MapPath("Export/");
 
                    //设置新建文件路径及名称
                    string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
 
                    //创建文件
                    FileStream file = new FileStream(savePath, FileMode.CreateNew);
 
                    //关闭释放流，不然没办法写入数据
                    file.Close();
                    file.Dispose();
 
                    //保存到指定的路径
                    workbook.SaveCopyAs(savePath);
 
                    //还可以加入以下方法输出到浏览器下载
                    FileInfo fileInfo = new FileInfo(savePath);
                    OutputClient(fileInfo);
                }
                catch(Exception ex)
                {
 
                }
                finally
                {
                    workbook.Close(false, Type.Missing, Type.Missing);
                    workbooks.Close();
 
                    //关闭退出
                    excel.Quit();
 
                    //释放 COM 对象
                    Marshal.ReleaseComObject(worksheet);
                    Marshal.ReleaseComObject(workbook);
                    Marshal.ReleaseComObject(workbooks);
                    Marshal.ReleaseComObject(excel);
 
                    worksheet = null;
                    workbook = null;
                    workbooks = null;
                    excel = null;
 
                    GC.Collect();
                }
            }
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public void OutputClient(FileInfo file)
        {
            HttpContext.Current.Response.Buffer = true;
 
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.ClearContent();
 
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
 
            //导出到 .xlsx 格式不能用时，可以试试这个
            //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd-HH-mm")));
 
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
 
            HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
 
            HttpContext.Current.Response.WriteFile(file.FullName);
            HttpContext.Current.Response.Flush();
 
            HttpContext.Current.Response.Close();
        }

第一种方法性能实在是不敢恭维，而且局限性太多。首先必须要安装 office（如果计算机上面没有的话），而且导出时需要指定文件保存的路径。也可以输出到浏览器下载，当然前提是已经保存写入数据。   第二种：使用 Aspose.Cells.dll 这个 Aspose.Cells 是 Aspose 公司推出的导出 Excel 的控件，不依赖 Office，商业软件，收费的。 可以参考：http://www.cnblogs.com/xiaofengfeng/archive/2012/09/27/2706211.html#top

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
public void ExportExcel(DataTable dt)
        {
            try
            {
                //获取指定虚拟路径的物理路径
                string path = HttpContext.Current.Server.MapPath("DLL/") + "License.lic";
 
                //读取 License 文件
                Stream stream = (Stream)File.OpenRead(path);
 
                //注册 License
                Aspose.Cells.License li = new Aspose.Cells.License();
                li.SetLicense(stream);
 
                //创建一个工作簿
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
 
                //创建一个 sheet 表
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
 
                //设置 sheet 表名称
                worksheet.Name = dt.TableName;
 
                Aspose.Cells.Cell cell;
 
                int rowIndex = 0;   //行的起始下标为 0
                int colIndex = 0;   //列的起始下标为 0
 
                //设置列名
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    //获取第一行的每个单元格
                    cell = worksheet.Cells[rowIndex, colIndex + i];
 
                    //设置列名
                    cell.PutValue(dt.Columns[i].ColumnName);
 
                    //设置字体
                    cell.Style.Font.Name = "Arial";
 
                    //设置字体加粗
                    cell.Style.Font.IsBold = true;
 
                    //设置字体大小
                    cell.Style.Font.Size = 12;
 
                    //设置字体颜色
                    cell.Style.Font.Color = System.Drawing.Color.Black;
 
                    //设置背景色
                    cell.Style.BackgroundColor = System.Drawing.Color.LightGreen;
                }
 
                //跳过第一行，第一行写入了列名
                rowIndex++;
 
                //写入数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell = worksheet.Cells[rowIndex + i, colIndex + j];
 
                        cell.PutValue(dt.Rows[i][j]);
                    }
                }
 
                //自动列宽
                worksheet.AutoFitColumns();
 
                //设置导出文件路径
                path = HttpContext.Current.Server.MapPath("Export/");
 
                //设置新建文件路径及名称
                string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
 
                //创建文件
                FileStream file = new FileStream(savePath, FileMode.CreateNew);
 
                //关闭释放流，不然没办法写入数据
                file.Close();
                file.Dispose();
 
                //保存至指定路径
                workbook.Save(savePath);
 
 
                //或者使用下面的方法，输出到浏览器下载。
                //byte[] bytes = workbook.SaveToStream().ToArray();
                //OutputClient(bytes);
 
                worksheet = null;
                workbook = null;
            }
            catch(Exception ex)
            {
 
            }
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public void OutputClient(byte[] bytes)
        {
            HttpContext.Current.Response.Buffer = true;
 
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.ClearContent();
 
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd-HH-mm")));
 
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
 
            HttpContext.Current.Response.BinaryWrite(bytes);
            HttpContext.Current.Response.Flush();
 
            HttpContext.Current.Response.Close();
        }

设置单元格格式为文本方法：

1
2
3
4
5
cell = worksheet.Cells[rowIndex, colIndex + i];
cell.PutValue(colNames[i]);
style = cell.GetStyle();
style.Number = 49;      // 49（text|@） 表示为文本
cell.SetStyle(style);

第二种方法性能还不错，而且操作也不复杂，可以设置导出时文件保存的路径，还可以保存为流输出到浏览器下载。   第三种：Microsoft.Jet.OLEDB 这种方法操作 Excel 类似于操作数据库。下面先介绍一下连接字符串：

1
2
3
4
5
// Excel 2003 版本连接字符串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/xxx.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
 
// Excel 2007 以上版本连接字符串
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/xxx.xlsx;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";

Provider：驱动程序名称 Data Source：指定 Excel 文件的路径 Extended Properties：Excel 8.0 针对 Excel 2000 及以上版本；Excel 12.0 针对 Excel 2007 及以上版本。 HDR：Yes 表示第一行包含列名,在计算行数时就不包含第一行。NO 则完全相反。 IMEX：0 写入模式；1 读取模式；2 读写模式。如果报错为“不能修改表 sheet1 的设计。它在只读数据库中”，那就去掉这个，问题解决。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
public void ExportExcel(DataTable dt)
        {
            OleDbConnection conn = null;
            OleDbCommand cmd = null;
 
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
 
            Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
 
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
 
            try
            {
                //设置区域为当前线程的区域
                dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
 
                //设置导出文件路径
                string path = HttpContext.Current.Server.MapPath("Export/");
 
                //设置新建文件路径及名称
                string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
 
                //创建文件
                FileStream file = new FileStream(savePath, FileMode.CreateNew);
 
                //关闭释放流，不然没办法写入数据
                file.Close();
                file.Dispose();
 
                //由于使用流创建的 excel 文件不能被正常识别，所以只能使用这种方式另存为一下。
                workbook.SaveCopyAs(savePath);
 
 
                // Excel 2003 版本连接字符串
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + savePath + "';Extended Properties='Excel 8.0;HDR=Yes;'";
 
                // Excel 2007 以上版本连接字符串
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='"+ savePath + "';Extended Properties='Excel 12.0;HDR=Yes;'";
 
                //创建连接对象
                conn = new OleDbConnection(strConn);
                //打开连接
                conn.Open();
 
                //创建命令对象
                cmd = conn.CreateCommand();
 
                //获取 excel 所有的数据表。
                //new object[] { null, null, null, "Table" }指定返回的架构信息：参数介绍
                //第一个参数指定目录
                //第二个参数指定所有者
                //第三个参数指定表名
                //第四个参数指定表类型
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
 
                //因为后面创建的表都会在最后面，所以本想删除掉前面的表，结果发现做不到，只能清空数据。
                for (int i = 0; i < dtSheetName.Rows.Count; i++)
                {
                    cmd.CommandText = "drop table [" + dtSheetName.Rows[i]["TABLE_NAME"].ToString() + "]";
                    cmd.ExecuteNonQuery();
                }
 
                //添加一个表，即 Excel 中 sheet 表
                cmd.CommandText = "create table " + dt.TableName + " ([S_Id] INT,[S_StuNo] VarChar,[S_Name] VarChar,[S_Sex] VarChar,[S_Height] VarChar,[S_BirthDate] VarChar,[C_S_Id] INT)";
                cmd.ExecuteNonQuery();
 
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string values = "";
 
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        values += "'" + dt.Rows[i][j].ToString() + "',";
                    }
 
                    //判断最后一个字符是否为逗号，如果是就截取掉
                    if (values.LastIndexOf(',') == values.Length - 1)
                    {
                        values = values.Substring(0, values.Length - 1);
                    }
 
                    //写入数据
                    cmd.CommandText = "insert into " + dt.TableName + " (S_Id,S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate,C_S_Id) values (" + values + ")";
                    cmd.ExecuteNonQuery();
                }
 
                conn.Close();
                conn.Dispose();
                cmd.Dispose();
 
                //加入下面的方法，把保存的 Excel 文件输出到浏览器下载。需要先关闭连接。
                FileInfo fileInfo = new FileInfo(savePath);
                OutputClient(fileInfo);
            }
            catch (Exception ex)
            {
 
            }
            finally
            {
                workbook.Close(false, Type.Missing, Type.Missing);
                workbooks.Close();
                excel.Quit();
 
                Marshal.ReleaseComObject(workbook);
                Marshal.ReleaseComObject(workbooks);
                Marshal.ReleaseComObject(excel);
 
                workbook = null;
                workbooks = null;
                excel = null;
 
                GC.Collect();
            }
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public void OutputClient(FileInfo file)
        {
            HttpResponse response = HttpContext.Current.Response;
 
            response.Buffer = true;
 
            response.Clear();
            response.ClearHeaders();
            response.ClearContent();
 
            response.ContentType = "application/vnd.ms-excel";
 
            //导出到 .xlsx 格式不能用时，可以试试这个
            //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 
            response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd-HH-mm")));
 
            response.Charset = "GB2312";
            response.ContentEncoding = Encoding.GetEncoding("GB2312");
 
            response.AddHeader("Content-Length", file.Length.ToString());
 
            response.WriteFile(file.FullName);
            response.Flush();
 
            response.Close();
        }

这种方法需要指定一个已经存在的 Excel 文件作为写入数据的模板，不然的话就得使用流创建一个新的 Excel 文件，但是这样是没法识别的，那就需要用到 Microsoft.Office.Interop.Excel.dll 里面的 Microsoft.Office.Interop.Excel.Workbook.SaveCopyAs() 方法另存为一下，这样性能也就更差了。 使用操作命令创建的表都是在最后面的，前面的也没法删除（我是没有找到方法），当然也可以不再创建，直接写入数据也可以。   第四种：NPOI NPOI 是 POI 项目的.NET版本，它不使用 Office COM 组件，不需要安装 Microsoft Office，目前支持 Office 2003 和 2007 版本。 NPOI 是免费开源的，操作也比较方便，下载地址：http://npoi.codeplex.com/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
public void ExportExcel(DataTable dt)
        {
            try
            {
                //创建一个工作簿
                IWorkbook workbook = new HSSFWorkbook();
 
                //创建一个 sheet 表
                ISheet sheet = workbook.CreateSheet(dt.TableName);
 
                //创建一行
                IRow rowH = sheet.CreateRow(0);
 
                //创建一个单元格
                ICell cell = null;
 
                //创建单元格样式
                ICellStyle cellStyle = workbook.CreateCellStyle();
 
                //创建格式
                IDataFormat dataFormat = workbook.CreateDataFormat();
 
                //设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
                cellStyle.DataFormat = dataFormat.GetFormat("@");
 
                //设置列名
                foreach (DataColumn col in dt.Columns)
                {
                    //创建单元格并设置单元格内容
                    rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
 
                    //设置单元格格式
                    rowH.Cells[col.Ordinal].CellStyle = cellStyle;
                }
 
                //写入数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //跳过第一行，第一行为列名
                    IRow row = sheet.CreateRow(i + 1);
 
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                        cell.CellStyle = cellStyle;
                    }
                }
 
                //设置导出文件路径
                string path = HttpContext.Current.Server.MapPath("Export/");
 
                //设置新建文件路径及名称
                string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";
 
                //创建文件
                FileStream file = new FileStream(savePath, FileMode.CreateNew,FileAccess.Write);
 
                //创建一个 IO 流
                MemoryStream ms = new MemoryStream();
 
                //写入到流
                workbook.Write(ms);
 
                //转换为字节数组
                byte[] bytes = ms.ToArray();
 
                file.Write(bytes, 0, bytes.Length);
                file.Flush();
 
                //还可以调用下面的方法，把流输出到浏览器下载
                OutputClient(bytes);
 
                //释放资源
                bytes = null;
 
                ms.Close();
                ms.Dispose();
 
                file.Close();
                file.Dispose();
 
                workbook.Close();
                sheet = null;
                workbook = null;
            }
            catch(Exception ex)
            {
 
            }
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public void OutputClient(byte[] bytes)
        {
            HttpResponse response = HttpContext.Current.Response;
 
            response.Buffer = true;
 
            response.Clear();
            response.ClearHeaders();
            response.ClearContent();
 
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")));
 
            response.Charset = "GB2312";
            response.ContentEncoding = Encoding.GetEncoding("GB2312");
 
            response.BinaryWrite(bytes);
            response.Flush();
 
            response.Close();
        }

1
2
3
4
5
// 2007 版本创建一个工作簿
IWorkbook workbook = new XSSFWorkbook();
 
// 2003 版本创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();

PS：操作 2003 版本需要添加 NPOI.dll […]

龙生   11 Jun 2018
View Details

Copyright © 2025 Longsheng All rights reserved