1 /// <summary>
2 /// DataTable导出到Excel的MemoryStream Export()
3 /// </summary>
4 /// <param name="dtSource">DataTable数据源</param>
5 /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
6 /// <param name="isRemoveColumns"></param>
7 public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig, bool isRemoveColumns = false)
8 {
9 if (isRemoveColumns)
10 {
11 int colint = 0;
12 for (int i = 0; i < dtSource.Columns.Count; )
13 {
14 DataColumn column = dtSource.Columns[i];
15 if (colint>=excelConfig.ColumnEntity.Count || excelConfig.ColumnEntity[colint].Column != column.ColumnName)
16 {
17 dtSource.Columns.Remove(column.ColumnName);
18 }
19 else
20 {
21 ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == dtSource.Columns[i].ColumnName);
22 dtSource.Columns[i].ColumnName = columnentity.ExcelColumn;//修改列头名
23 i++;
24 colint++;
25 }
26
27 }
28 }
29
30
31 HSSFWorkbook workbook = new HSSFWorkbook();
32 ISheet sheet = workbook.CreateSheet();
33
34 #region 右击文件 属性信息
35 {
36 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
37 dsi.Company = "NPOI";
38 workbook.DocumentSummaryInformation = dsi;
39
40 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
41 si.Author = "zdd"; //填加xls文件作者信息
42 si.ApplicationName = "XX系统"; //填加xls文件创建程序信息
43 si.LastAuthor = "zdd"; //填加xls文件最后保存者信息
44 si.Comments = "zdd"; //填加xls文件作者信息
45 si.Title = "标题信息"; //填加xls文件标题信息
46 si.Subject = "主题信息";//填加文件主题信息
47 si.CreateDateTime = System.DateTime.Now;
48 workbook.SummaryInformation = si;
49 }
50 #endregion
51
52 #region 设置标题样式
53 ICellStyle headStyle = workbook.CreateCellStyle();
54 int[] arrColWidth = new int[dtSource.Columns.Count];
55 string[] arrColName = new string[dtSource.Columns.Count];//列名
56 ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count];//样式表
57 headStyle.Alignment = HorizontalAlignment.Center; // ------------------
58 if (excelConfig.Background != new Color())
59 {
60 if (excelConfig.Background != new Color())
61 {
62 headStyle.FillPattern = FillPattern.SolidForeground;
63 headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
64 }
65 }
66 IFont font = workbook.CreateFont();
67 font.FontHeightInPoints = excelConfig.TitlePoint;
68 if (excelConfig.ForeColor != new Color())
69 {
70 font.Color = GetXLColour(workbook, excelConfig.ForeColor);
71 }
72 font.Boldweight = 700;
73 headStyle.SetFont(font);
74 #endregion
75
76 #region 列头及样式
77 ICellStyle cHeadStyle = workbook.CreateCellStyle();
78 cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
79 IFont cfont = workbook.CreateFont();
80 cfont.FontHeightInPoints = excelConfig.HeadPoint;
81 cHeadStyle.SetFont(cfont);
82 #endregion
83
84 #region 设置内容单元格样式
85 foreach (DataColumn item in dtSource.Columns)
86 {
87 ICellStyle columnStyle = workbook.CreateCellStyle();
88 columnStyle.Alignment = HorizontalAlignment.Center;
89 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
90 arrColName[item.Ordinal] = item.ColumnName.ToString();
91 if (excelConfig.ColumnEntity != null)
92 {
93 ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
94 if (columnentity != null)
95 {
96 arrColName[item.Ordinal] = columnentity.ExcelColumn;
97 if (columnentity.Width != 0)
98 {
99 arrColWidth[item.Ordinal] = columnentity.Width;
100 }
101 if (columnentity.Background != new Color())
102 {
103 if (columnentity.Background != new Color())
104 {
105 columnStyle.FillPattern = FillPattern.SolidForeground;
106 columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
107 }
108 }
109 if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
110 {
111 IFont columnFont = workbook.CreateFont();
112 columnFont.FontHeightInPoints = 10;
113 if (columnentity.Font != null)
114 {
115 columnFont.FontName = columnentity.Font;
116 }
117 if (columnentity.Point != 0)
118 {
119 columnFont.FontHeightInPoints = columnentity.Point;
120 }
121 if (columnentity.ForeColor != new Color())
122 {
123 columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
124 }
125 columnStyle.SetFont(font);
126 }
127 columnStyle.Alignment = getAlignment(columnentity.Alignment);
128 }
129 }
130 arryColumStyle[item.Ordinal] = columnStyle;
131 }
132 if (excelConfig.IsAllSizeColumn)
133 {
134 #region 根据列中最长列的长度取得列宽
135 for (int i = 0; i < dtSource.Rows.Count; i++)
136 {
137 for (int j = 0; j < dtSource.Columns.Count; j++)
138 {
139 if (arrColWidth[j] != 0)
140 {
141 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
142 if (intTemp > arrColWidth[j])
143 {
144 arrColWidth[j] = intTemp;
145 }
146 }
147
148 }
149 }
150 #endregion
151 }
152 #endregion
153
154 #region 填充数据
155
156 #endregion
157
158 ICellStyle dateStyle = workbook.CreateCellStyle();
159 IDataFormat format = workbook.CreateDataFormat();
160 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
161 int rowIndex = 0;
162 foreach (DataRow row in dtSource.Rows)
163 {
164 #region 新建表，填充表头，填充列头，样式
165 if (rowIndex == 65535 || rowIndex == 0)
166 {
167 if (rowIndex != 0)
168 {
169 sheet = workbook.CreateSheet();
170 }
171
172 #region 表头及样式
173 {
174 if (excelConfig.Title != null)
175 {
176 IRow headerRow = sheet.CreateRow(0);
177 if (excelConfig.TitleHeight != 0)
178 {
179 headerRow.Height = (short)(excelConfig.TitleHeight * 20);
180 }
181 headerRow.HeightInPoints = 25;
182 headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
183 headerRow.GetCell(0).CellStyle = headStyle;
184 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
185 }
186
187 }
188 #endregion
189
190 #region 列头及样式
191 {
192 IRow headerRow = sheet.CreateRow(1);
193 #region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
194 foreach (DataColumn column in dtSource.Columns)
195 {
196 headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
197 headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
198 //设置列宽
199 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
200 }
201 #endregion
202 }
203 #endregion
204
205 rowIndex = 2;
206 }
207 #endregion
208
209 #region 填充内容
210 IRow dataRow = sheet.CreateRow(rowIndex);
211
212
213
214 foreach (DataColumn column in dtSource.Columns)
215 {
216 ICell newCell = dataRow.CreateCell(column.Ordinal);
217 newCell.CellStyle = arryColumStyle[column.Ordinal];
218 string drValue = row[column].ToString();
219
220 //如果是图片列
221 if (column.ToString() == "图片")
222 {
223
224
225
226 string tPath = DirFileHelper.MapPath("/ExportFile/ImageFiles/");//服务器图片存储目录
227 if (!DirFileHelper.IsExistDirectory(tPath))//判断是否存在此目录 无则创建
228 {
229 DirFileHelper.CreateDir("/ExportFile/ImageFiles/");//创建临时存储压缩后图片路径
230 }
231 string filePath = SharedImagePath + drValue;
232 string newImagePath = tPath + drValue;//压缩后图片绝对路径
233 if (DirFileHelper.IsExistFile(newImagePath))//若果本地已存在 则不从100共享盘中取图片
234 {
235 byte[] bytes = System.IO.File.ReadAllBytes(newImagePath);
236 int pictureIndex = workbook.AddPicture(bytes, PictureType.JPEG);
237 HSSFPatriarch par = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
238
239 HSSFClientAnchor anchor = new HSSFClientAnchor
240 {
241 Dx1 = 0,//起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
242 Dy1 = 0,//起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
243 Dx2 = 0,//终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
244 Dy2 = 0,//：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
245 Col1 = column.Ordinal +1, //批注起始位置的纵坐标（当前单元格位置+2）
246 Col2 = column.Ordinal + 6, //批注结束位置的纵坐标
247 Row1 = column.Ordinal + 0, //批注起始位置的横坐标
248 Row2 = column.Ordinal + 17 //批注结束位置的横坐标
249 };
250 HSSFComment comment = par.CreateComment(anchor);
251 comment.SetBackgroundImage(pictureIndex);
252 newCell.CellComment = comment;
253 }
254 else//取100共享盘图片并压缩指定大小
255 {
256 if (DirFileHelper.IsExistFile(filePath))//需要增加判断是否存在此图片
257 {
258 ImageHelper.CreateMinImageAndDel(filePath, 300, 400, tPath);//压缩指定大小图片
259 byte[] bytes = System.IO.File.ReadAllBytes(newImagePath);
260 int pictureIndex = workbook.AddPicture(bytes, PictureType.JPEG);
261 HSSFPatriarch par = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
262 HSSFClientAnchor anchor = new HSSFClientAnchor
263 {
264 Dx1 = 0,//起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
265 Dy1 = 0,//起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
266 Dx2 = 0,//终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
267 Dy2 = 0,//：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
268 Col1 = column.Ordinal + 2, //批注起始位置的纵坐标（当前单元格位置+2）
269 Col2 = column.Ordinal + 6, //批注结束位置的纵坐标
270 Row1 = column.Ordinal + 0, //批注起始位置的横坐标
271 Row2 = column.Ordinal + 17 //批注结束位置的横坐标
272 };
273 HSSFComment comment = par.CreateComment(anchor);
274 comment.SetBackgroundImage(pictureIndex);
275 }
276 }
277 //newCell.SetCellValue(drValue);
278 SetCell(newCell, dateStyle, column.DataType, drValue);
279 }
280 else
281 {
282 SetCell(newCell, dateStyle, column.DataType, drValue);
283 }
284
285
286 }
287 #endregion
288 rowIndex++;
289 }
290 using (MemoryStream ms = new MemoryStream())
291 {
292 workbook.Write(ms);
293 ms.Flush();
294 ms.Position = 0;
295 return ms;
296 }
297 }
298 #endregion
