123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546 |
- using NPOI.HPSF;
- using NPOI.HSSF.UserModel;
- using NPOI.SS.UserModel;
- using System.Data;
- using System.IO;
- using System.Text;
- using System.Web;
- using System.Drawing;
- using NPOI.HSSF.Util;
- using System;
- using System.Collections.Generic;
- using NPOI.XSSF.UserModel;
- namespace LeaRun.Util.Offices
- {
- /// <summary>
- ///版 本 V1.0
- ///Copyright (c) 2010-2015 上海力软信息技术有限公司
- ///创建人:刘晓雷
- ///日 期:2015/11/25
- ///描 述:NPOI Excel DataTable操作类
- public class ExcelHelper
- {
- #region Excel导出方法 ExcelDownload
- /// <summary>
- /// Excel导出下载
- /// </summary>
- /// <param name="dtSource">DataTable数据源</param>
- /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
- public static void ExcelDownload(DataTable dtSource, ExcelConfig excelConfig)
- {
- HttpContext curContext = HttpContext.Current;
- // 设置编码和附件格式
- curContext.Response.ContentType = "application/ms-excel";
- curContext.Response.ContentEncoding = Encoding.UTF8;
- curContext.Response.Charset = "";
- curContext.Response.AppendHeader("Content-Disposition",
- "attachment;filename=" + HttpUtility.UrlEncode(excelConfig.FileName, Encoding.UTF8));
- //调用导出具体方法Export()
- curContext.Response.BinaryWrite(ExportMemoryStream(dtSource, excelConfig).GetBuffer());
- curContext.Response.End();
- }
- /// <summary>
- /// Excel导出下载
- /// </summary>
- /// <param name="list">数据源</param>
- /// <param name="templdateName">模板文件名</param>
- /// <param name="newFileName">文件名</param>
- public static void ExcelDownload(List<TemplateMode> list, string templdateName, string newFileName)
- {
- HttpResponse response = System.Web.HttpContext.Current.Response;
- response.Clear();
- response.Charset = "UTF-8";
- response.ContentType = "application/vnd-excel";//"application/vnd.ms-excel";
- System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + newFileName));
- System.Web.HttpContext.Current.Response.BinaryWrite(ExportListByTempale(list, templdateName).ToArray());
- }
- #endregion
- #region DataTable导出到Excel文件excelConfig中FileName设置为全路径
- /// <summary>
- /// DataTable导出到Excel文件 Export()
- /// </summary>
- /// <param name="dtSource">DataTable数据源</param>
- /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
- public static void ExcelExport(DataTable dtSource, ExcelConfig excelConfig)
- {
- using (MemoryStream ms = ExportMemoryStream(dtSource, excelConfig))
- {
- using (FileStream fs = new FileStream(excelConfig.FileName, FileMode.Create, FileAccess.Write))
- {
- byte[] data = ms.ToArray();
- fs.Write(data, 0, data.Length);
- fs.Flush();
- }
- }
- }
- #endregion
- #region DataTable导出到Excel的MemoryStream
- /// <summary>
- /// DataTable导出到Excel的MemoryStream Export()
- /// </summary>
- /// <param name="dtSource">DataTable数据源</param>
- /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
- public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
- {
- for (int i = 0; i < dtSource.Columns.Count; )
- {
- bool IsExists = false;
- DataColumn column = dtSource.Columns[i];
- for (int j = 0; j < excelConfig.ColumnEntity.Count; j++)
- {
- if (excelConfig.ColumnEntity[j].Column == column.ColumnName)
- {
- IsExists = true;
- break;
- }
- }
- if (!IsExists)
- {
- dtSource.Columns.Remove(column);
- }
- else
- {
- i++;
- }
- }
- HSSFWorkbook workbook = new HSSFWorkbook();
- ISheet sheet = workbook.CreateSheet();
- #region 右击文件 属性信息
- {
- DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
- dsi.Company = "NPOI";
- workbook.DocumentSummaryInformation = dsi;
- SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
- si.Author = "刘晓雷"; //填加xls文件作者信息
- si.ApplicationName = "力软信息"; //填加xls文件创建程序信息
- si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息
- si.Comments = "刘晓雷"; //填加xls文件作者信息
- si.Title = "标题信息"; //填加xls文件标题信息
- si.Subject = "主题信息";//填加文件主题信息
- si.CreateDateTime = System.DateTime.Now;
- workbook.SummaryInformation = si;
- }
- #endregion
- #region 设置标题样式
- ICellStyle headStyle = workbook.CreateCellStyle();
- int[] arrColWidth = new int[dtSource.Columns.Count];
- string[] arrColName = new string[dtSource.Columns.Count];//列名
- ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count];//样式表
- headStyle.Alignment = HorizontalAlignment.Center; // ------------------
- if (excelConfig.Background != new Color())
- {
- if (excelConfig.Background != new Color())
- {
- headStyle.FillPattern = FillPattern.SolidForeground;
- headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
- }
- }
- IFont font = workbook.CreateFont();
- font.FontHeightInPoints = excelConfig.TitlePoint;
- if (excelConfig.ForeColor != new Color())
- {
- font.Color = GetXLColour(workbook, excelConfig.ForeColor);
- }
- font.Boldweight = 700;
- headStyle.SetFont(font);
- #endregion
- #region 列头及样式
- ICellStyle cHeadStyle = workbook.CreateCellStyle();
- cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
- IFont cfont = workbook.CreateFont();
- cfont.FontHeightInPoints = excelConfig.HeadPoint;
- cHeadStyle.SetFont(cfont);
- #endregion
- #region 设置内容单元格样式
- foreach (DataColumn item in dtSource.Columns)
- {
- ICellStyle columnStyle = workbook.CreateCellStyle();
- columnStyle.Alignment = HorizontalAlignment.Center;
- arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
- arrColName[item.Ordinal] = item.ColumnName.ToString();
- if (excelConfig.ColumnEntity != null)
- {
- ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
- if (columnentity != null)
- {
- arrColName[item.Ordinal] = columnentity.ExcelColumn;
- if (columnentity.Width != 0)
- {
- arrColWidth[item.Ordinal] = columnentity.Width;
- }
- if (columnentity.Background != new Color())
- {
- if (columnentity.Background != new Color())
- {
- columnStyle.FillPattern = FillPattern.SolidForeground;
- columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
- }
- }
- if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
- {
- IFont columnFont = workbook.CreateFont();
- columnFont.FontHeightInPoints = 10;
- if (columnentity.Font != null)
- {
- columnFont.FontName = columnentity.Font;
- }
- if (columnentity.Point != 0)
- {
- columnFont.FontHeightInPoints = columnentity.Point;
- }
- if (columnentity.ForeColor != new Color())
- {
- columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
- }
- columnStyle.SetFont(font);
- }
- columnStyle.Alignment = getAlignment(columnentity.Alignment);
- }
- }
- arryColumStyle[item.Ordinal] = columnStyle;
- }
- if (excelConfig.IsAllSizeColumn)
- {
- #region 根据列中最长列的长度取得列宽
- for (int i = 0; i < dtSource.Rows.Count; i++)
- {
- for (int j = 0; j < dtSource.Columns.Count; j++)
- {
- if (arrColWidth[j] != 0)
- {
- int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
- if (intTemp > arrColWidth[j])
- {
- arrColWidth[j] = intTemp;
- }
- }
- }
- }
- #endregion
- }
- #endregion
- #region 填充数据
- #endregion
- ICellStyle dateStyle = workbook.CreateCellStyle();
- IDataFormat format = workbook.CreateDataFormat();
- dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
- int rowIndex = 0;
- foreach (DataRow row in dtSource.Rows)
- {
- #region 新建表,填充表头,填充列头,样式
- if (rowIndex == 65535 || rowIndex == 0)
- {
- if (rowIndex != 0)
- {
- sheet = workbook.CreateSheet();
- }
- #region 表头及样式
- {
- if (excelConfig.Title != null)
- {
- IRow headerRow = sheet.CreateRow(0);
- if (excelConfig.TitleHeight != 0)
- {
- headerRow.Height = (short)(excelConfig.TitleHeight * 20);
- }
- headerRow.HeightInPoints = 25;
- headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
- headerRow.GetCell(0).CellStyle = headStyle;
- sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
- }
- }
- #endregion
- #region 列头及样式
- {
- IRow headerRow = sheet.CreateRow(1);
- #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出
- foreach (DataColumn column in dtSource.Columns)
- {
- headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
- headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
- //设置列宽
- sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
- }
- #endregion
- }
- #endregion
- rowIndex = 2;
- }
- #endregion
- #region 填充内容
- IRow dataRow = sheet.CreateRow(rowIndex);
- foreach (DataColumn column in dtSource.Columns)
- {
- ICell newCell = dataRow.CreateCell(column.Ordinal);
- newCell.CellStyle = arryColumStyle[column.Ordinal];
- string drValue = row[column].ToString();
- SetCell(newCell, dateStyle, column.DataType, drValue);
- }
- #endregion
- rowIndex++;
- }
- using (MemoryStream ms = new MemoryStream())
- {
- workbook.Write(ms);
- ms.Flush();
- ms.Position = 0;
- return ms;
- }
- }
- #endregion
- #region ListExcel导出(加载模板)
- /// <summary>
- /// List根据模板导出ExcelMemoryStream
- /// </summary>
- /// <param name="list"></param>
- /// <param name="templdateName"></param>
- public static MemoryStream ExportListByTempale(List<TemplateMode> list, string templdateName)
- {
- try
- {
- string templatePath = HttpContext.Current.Server.MapPath("/") + "/Resource/ExcelTemplate/";
- string templdateName1 = string.Format("{0}{1}", templatePath, templdateName);
- FileStream fileStream = new FileStream(templdateName1, FileMode.Open, FileAccess.Read);
- ISheet sheet = null;
- if (templdateName.IndexOf(".xlsx") == -1)//2003
- {
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileStream);
- sheet = hssfworkbook.GetSheetAt(0);
- SetPurchaseOrder(sheet, list);
- sheet.ForceFormulaRecalculation = true;
- using (MemoryStream ms = new MemoryStream())
- {
- hssfworkbook.Write(ms);
- ms.Flush();
- return ms;
- }
- }
- else//2007
- {
- XSSFWorkbook xssfworkbook = new XSSFWorkbook(fileStream);
- sheet = xssfworkbook.GetSheetAt(0);
- SetPurchaseOrder(sheet, list);
- sheet.ForceFormulaRecalculation = true;
- using (MemoryStream ms = new MemoryStream())
- {
- xssfworkbook.Write(ms);
- ms.Flush();
- return ms;
- }
- }
- }
- catch (Exception)
- {
- throw;
- }
- }
- /// <summary>
- /// 赋值单元格
- /// </summary>
- /// <param name="sheet"></param>
- /// <param name="list"></param>
- private static void SetPurchaseOrder(ISheet sheet, List<TemplateMode> list)
- {
- try
- {
- foreach (var item in list)
- {
- IRow row = null;
- ICell cell = null;
- row = sheet.GetRow(item.row);
- if (row == null)
- {
- row = sheet.CreateRow(item.row);
- }
- cell = row.GetCell(item.cell);
- if (cell == null)
- {
- cell = row.CreateCell(item.cell);
- }
- cell.SetCellValue(item.value);
- }
- }
- catch (Exception)
- {
- throw;
- }
- }
- #endregion
- #region 设置表格内容
- private static void SetCell(ICell newCell, ICellStyle dateStyle, Type dataType, string drValue)
- {
- switch (dataType.ToString())
- {
- case "System.String"://字符串类型
- newCell.SetCellValue(drValue);
- break;
- case "System.DateTime"://日期类型
- System.DateTime dateV;
- if (System.DateTime.TryParse(drValue, out dateV))
- {
- newCell.SetCellValue(dateV);
- }
- else
- {
- newCell.SetCellValue("");
- }
- newCell.CellStyle = dateStyle;//格式化显示
- break;
- case "System.Boolean"://布尔型
- bool boolV = false;
- bool.TryParse(drValue, out boolV);
- newCell.SetCellValue(boolV);
- break;
- case "System.Int16"://整型
- case "System.Int32":
- case "System.Int64":
- case "System.Byte":
- int intV = 0;
- int.TryParse(drValue, out intV);
- newCell.SetCellValue(intV);
- break;
- case "System.Decimal"://浮点型
- case "System.Double":
- double doubV = 0;
- double.TryParse(drValue, out doubV);
- newCell.SetCellValue(doubV);
- break;
- case "System.DBNull"://空值处理
- newCell.SetCellValue("");
- break;
- default:
- newCell.SetCellValue("");
- break;
- }
- }
- #endregion
- #region 从Excel导入
- /// <summary>
- /// 读取excel ,默认第一行为标头
- /// </summary>
- /// <param name="strFileName">excel文档路径</param>
- /// <returns></returns>
- public static DataTable ExcelImport(string strFileName)
- {
- DataTable dt = new DataTable();
- ISheet sheet = null;
- using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
- {
- if (strFileName.IndexOf(".xlsx") == -1)//2003
- {
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
- sheet = hssfworkbook.GetSheetAt(0);
- }
- else//2007
- {
- XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
- sheet = xssfworkbook.GetSheetAt(0);
- }
- }
- System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
- IRow headerRow = sheet.GetRow(0);
- int cellCount = headerRow.LastCellNum;
- for (int j = 0; j < cellCount; j++)
- {
- ICell cell = headerRow.GetCell(j);
- dt.Columns.Add(cell.ToString());
- }
- for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
- {
- IRow row = sheet.GetRow(i);
- DataRow dataRow = dt.NewRow();
- for (int j = row.FirstCellNum; j < cellCount; j++)
- {
- if (row.GetCell(j) != null)
- dataRow[j] = row.GetCell(j).ToString();
- }
- dt.Rows.Add(dataRow);
- }
- return dt;
- }
- #endregion
- #region RGB颜色转NPOI颜色
- private static short GetXLColour(HSSFWorkbook workbook, Color SystemColour)
- {
- short s = 0;
- HSSFPalette XlPalette = workbook.GetCustomPalette();
- NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
- if (XlColour == null)
- {
- if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
- {
- XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
- s = XlColour.Indexed;
- }
- }
- else
- s = XlColour.Indexed;
- return s;
- }
- #endregion
- #region 设置列的对齐方式
- /// <summary>
- /// 设置对齐方式
- /// </summary>
- /// <param name="style"></param>
- /// <returns></returns>
- private static HorizontalAlignment getAlignment(string style)
- {
- switch (style)
- {
- case "center":
- return HorizontalAlignment.Center;
- case "left":
- return HorizontalAlignment.Left;
- case "right":
- return HorizontalAlignment.Right;
- case "fill":
- return HorizontalAlignment.Fill;
- case "justify":
- return HorizontalAlignment.Justify;
- case "centerselection":
- return HorizontalAlignment.CenterSelection;
- case "distributed":
- return HorizontalAlignment.Distributed;
- }
- return NPOI.SS.UserModel.HorizontalAlignment.General;
- }
- #endregion
- }
- }
|