标签：单位 eric ase font time blank 保存 == 代码
NPOI作为开源免费的组件，功能强大，可用来读写Excel(兼容xls和xlsx两种版本)、Word、PPT文件。可是要让我们记住所有的操作，这便有点困难了，至此，总结一些在开发中常用的针对Excel的简单。NPOI官网地址
本文地址：https://www.cnblogs.com/CKExp/p/9626022.html
一、NPOI的安装
下载NPOI或是通过Nuget包加入进来，然后在代码中引用如下命名空间，然后开始读写Excel文件。
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
二、NPOI写入Excel文件
在NPOI中，使用HSSFWorkbook类类来处理xls结尾的Excel文件(版本在2003及以前)，XSSFWorkbook类来处理xlsx结尾的Excel文件(版本在2007及以后)，都继承自接口IWorkbook，我们可以使用IWorkbook来统一处理两种不同格式的Excel文件。
直接参考相关代码即可，对于合并单元格的跨行跨列操作，无需将被跨掉的行生成新行，合并单元格的信息是单独保存的。设置单元格样式时，请创建一个新的样式对象，不创建将使用默认的样式对象。
1 ///
2 /// Datable导出到Excel
3 ///
4 ///
5 public static void DataTableToExcel()
6 {
7 //一些已有数据信息
8 bool fileSaved = false;
9 SaveFileDialog saveDialog = new SaveFileDialog
10 {
11 DefaultExt = "xls",
12 Filter = "Excel文件|*.xls",
13 FileName = DateTime.Now.ToString("yyyyMMdd") + "-" + enterpriseTable.Rows[0]["名称"].ToString() + "委托书"
14 };
15 saveDialog.ShowDialog();
16 string saveFileName = saveDialog.FileName;
17 if (saveFileName.IndexOf(":") 0) return; //被点了取消
18 if (saveFileName != "")
19 {
20 try
21 {
22 IWorkbook workbook;
23 string fileExt = System.IO.Path.GetExtension(saveFileName).ToLower();
24 if (fileExt == ".xlsx")
25 {
26 workbook = new XSSFWorkbook();
27 }
28 else if (fileExt == ".xls")
29 {
30 workbook = new HSSFWorkbook();
31 }
32 else
33 {
34 return;
35 }
36
37 ISheet sheet = workbook.CreateSheet("Sheet1");
38
39 sheet.AddMergedRegion(new CellRangeAddress(0, 3, 0, 9));//合并单元格
40 IRow row = sheet.CreateRow(0);//创建首行
41 ICell cell = row.CreateCell(0);//行中创建第一列
42 cell.SetCellValue("标题");
43 ICellStyle style = workbook.CreateCellStyle();//设置样式，创建新的style实例，脱离统一样式
44 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//设置单元格的样式：水平对齐居中
45 style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
46
47 IFont font = workbook.CreateFont();//新建一个字体样式对象
48 font.Boldweight = short.MaxValue; //设置字体加粗样式
49 style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中
50 cell.CellStyle = style; //将新的样式赋给单元格
51
52 sheet.AddMergedRegion(new CellRangeAddress(4, 4, 1, 4));
53 sheet.AddMergedRegion(new CellRangeAddress(4, 4, 6, 9));
54 row = sheet.CreateRow(4);
55 cell = row.CreateCell(0);
56 cell.SetCellValue("编号:");
57 cell = row.CreateCell(1);
58 cell.SetCellValue(planCode);
59 cell = row.CreateCell(5);
60 cell.SetCellValue("日期：");
61 cell = row.CreateCell(6);
62 cell.SetCellValue(taskPlantable.Rows[0]["编制日期"].ToString());
63
64 sheet.AddMergedRegion(new CellRangeAddress(5, 5, 1, 4));
65 sheet.AddMergedRegion(new CellRangeAddress(5, 5, 6, 9));
66 row = sheet.CreateRow(5);
67 cell = row.CreateCell(0);
68 cell.SetCellValue("单位:");
69 cell = row.CreateCell(1);
70 cell.SetCellValue(enterpriseTable.Rows[0]["名称"].ToString());
71 cell = row.CreateCell(5);
72 cell.SetCellValue("联系人：");
73 cell = row.CreateCell(6);
74 cell.SetCellValue(enterpriseTable.Rows[0]["联系人"].ToString());
75
76 sheet.AddMergedRegion(new CellRangeAddress(6, 6, 1, 4));
77 sheet.AddMergedRegion(new CellRangeAddress(6, 6, 6, 9));
78 row = sheet.CreateRow(6);
79 cell = row.CreateCell(0);
80 cell.SetCellValue("传真:");
81 cell = row.CreateCell(1);
82 cell.SetCellValue(enterpriseTable.Rows[0]["传真"].ToString());
83 cell = row.CreateCell(5);
84 cell.SetCellValue("联系电话：");
85 cell = row.CreateCell(6);
86 cell.SetCellValue(enterpriseTable.Rows[0]["电话"].ToString());
87
88 sheet.AddMergedRegion(new CellRangeAddress(7, 7, 1, 9));
89 row = sheet.CreateRow(7);
90 cell = row.CreateCell(0);
91 cell.SetCellValue("详细地址:");
92 cell = row.CreateCell(1);
93 cell.SetCellValue(enterpriseTable.Rows[0]["详细地址"].ToString());
94
95 int index = 7;
96 //数据
97 for (int i = 0; i )
98 {
99 index++;
100 sheet.AddMergedRegion(new CellRangeAddress(index, index, 1, 4));
101 sheet.AddMergedRegion(new CellRangeAddress(index, index, 6, 9));
102 row = sheet.CreateRow(index);
103 cell = row.CreateCell(0);
104 cell.SetCellValue("名称:");
105 cell = row.CreateCell(1);
106 cell.SetCellValue(taskProjectTable.Rows[i]["名称"].ToString());
107 cell = row.CreateCell(5);
108 cell.SetCellValue("类型:");
109 cell = row.CreateCell(6);
110 cell.SetCellValue(taskProjectTable.Rows[i]["项目类型"].ToString());
111
112 index++;
113 sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 2));
114 sheet.AddMergedRegion(new CellRangeAddress(index, index, 3, 5));
115 sheet.AddMergedRegion(new CellRangeAddress(index, index, 6, 9));
116 row = sheet.CreateRow(index);
117 cell = row.CreateCell(0);
118 cell.SetCellValue("项目");
119 cell = row.CreateCell(3);
120 cell.SetCellValue("方法");
121 cell = row.CreateCell(6);
122 cell.SetCellValue("仪器");
123
124 //获取数据信息
125 DataTable taskDataTable = mysql.GetTableFromSQL(selstr.ToString());
126 selstr.Clear();
127 for (int j = 0; j )
128 {
129 index++;
130 sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 2));
131 sheet.AddMergedRegion(new CellRangeAddress(index, index, 3, 5));
132 sheet.AddMergedRegion(new CellRangeAddress(index, index, 6, 9));
133 row = sheet.CreateRow(index);
134 cell = row.CreateCell(0);
135 cell.SetCellValue(taskDataTable.Rows[j]["名称"].ToString());
136 cell = row.CreateCell(3);
137 cell.SetCellValue(taskDataTable.Rows[j]["方法"].ToString());
138 cell = row.CreateCell(6);
139 cell.SetCellValue(taskDataTable.Rows[j]["仪器型号"].ToString());
140 }
141 }
142
143 //转为字节数组
144 MemoryStream stream = new MemoryStream();
145 workbook.Write(stream);
146 var buf = stream.ToArray();
147
148 //保存为Excel文件
149 using (FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create, FileAccess.Write))
150 {
151 fs.Write(buf, 0, buf.Length);
152 fs.Flush();
153 }
154 fileSaved = true;
155 }
156 catch (Exception ex)
157 {
158 fileSaved = false;
159 MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
160 }
161 }
162 GC.Collect();//强行销毁
163
164 if (fileSaved && File.Exists(saveFileName))
165 {
166 MessageBox.Show("导出成功！", "通知");
167 Process.Start(saveFileName);
168 }
169 else
170 {
171 MessageBox.Show("导出失败！", "通知");
172 }
173 }
三、NPOI读取Excel文件
打开指定Excel文件并读取文件中的内容，加入到DataTable中，或是加入到其它的数据载体中。
1 ///
2 /// Excel导入成DataTble
3 ///
4 /// 导入路径(包含文件名与扩展名)
5 ///
6 public static DataTable ExcelToTable(string file)
7 {
8 DataTable dt = new DataTable();
9 IWorkbook workbook;
10 string fileExt = Path.GetExtension(file).ToLower();
11 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
12 {
13 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
14 if (workbook == null) { return null; }
15 ISheet sheet = workbook.GetSheetAt(0);
16
17 //表头
18 IRow header = sheet.GetRow(sheet.FirstRowNum);
19 Listint> columns = new Listint>();
20 for (int i = 0; i )
21 {
22 object obj = GetValueType(header.GetCell(i));
23 if (obj == null || obj.ToString() == string.Empty)
24 {
25 dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
26 }
27 else
28 dt.Columns.Add(new DataColumn(obj.ToString()));
29 columns.Add(i);
30 }
31 //数据
32 for (int i = sheet.FirstRowNum + 1; i )
33 {
34 DataRow dr = dt.NewRow();
35 bool hasValue = false;
36 foreach (int j in columns)
37 {
38 dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
39 if (dr[j] != null && dr[j].ToString() != string.Empty)
40 {
41 hasValue = true;
42 }
43 }
44 if (hasValue)
45 {
46 dt.Rows.Add(dr);
47 }
48 }
49 }
50 return dt;
51 }
四、对单元格数据类型的操作
获取目标单元格的数据类型及数据值。
1 ///
2 /// 获取单元格类型
3 ///
4 /// 目标单元格
5 ///
6 private static object GetValueType(ICell cell)
7 {
8 if (cell == null)
9 return null;
10 switch (cell.CellType)
11 {
12 case CellType.Blank:
13 return null;
14 case CellType.Boolean:
15 return cell.BooleanCellValue;
16 case CellType.Numeric:
17 return cell.NumericCellValue;
18 case CellType.String:
19 return cell.StringCellValue;
20 case CellType.Error:
21 return cell.ErrorCellValue;
22 case CellType.Formula:
23 default:
24 return "=" + cell.CellFormula;
25 }
26 }
将数据设置到目标单元格中，并设置为指定数据格式。
1 ///
2 /// 设置单元格数据类型
3 ///
4 /// 目标单元格
5 /// 数据值
6 ///
7 public static void SetCellValue(ICell cell, object obj)
8 {
9 if (obj.GetType() == typeof(int))
10 {
11 cell.SetCellValue((int)obj);
12 }
13 else if (obj.GetType() == typeof(double))
14 {
15 cell.SetCellValue((double)obj);
16 }
17 else if (obj.GetType() == typeof(IRichTextString))
18 {
19 cell.SetCellValue((IRichTextString)obj);
20 }
21 else if (obj.GetType() == typeof(string))
22 {
23 cell.SetCellValue(obj.ToString());
24 }
25 else if (obj.GetType() == typeof(DateTime))
26 {
27 cell.SetCellValue((DateTime)obj);
28 }
29 else if (obj.GetType() == typeof(bool))
30 {
31 cell.SetCellValue((bool)obj);
32 }
33 else
34 {
35 cell.SetCellValue(obj.ToString());
36 }
37 }
本文地址：https://www.cnblogs.com/CKExp/p/9626022.html
2018-09-11,望技术有成后能回来看见自己的脚步
C#利用NPOI操作Excel文件
标签：单位 eric ase font time blank 保存 == 代码
原文地址：https://www.cnblogs.com/CKExp/p/9626022.html