123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407 |
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.IO;
- using System.Linq;
- using System.Reflection;
- using System.Text;
- using NPOI.HSSF.UserModel;
- using NPOI.SS.UserModel;
- using NPOI.SS.Util;
- using NPOI.XSSF.UserModel;
- using SAGA.DotNetUtils.NPOI;
- using SAGA.DotNetUtils.Extend;
- namespace SAGA.DotNetUtils.NPOI
- {
- /// <summary>
- /// NPOI操作帮助类
- /// </summary>
- public class NPOIHelper
- {
- /// <summary>
- /// DataTable导出到Excel文件
- /// </summary>
- /// <param name="dtSource">源DataTable</param>
- /// <param name="strHeaderText">表头文本</param>
- /// <param name="strFileName">保存位置</param>
- public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
- {
- using (MemoryStream ms = Export(dtSource, strHeaderText))
- {
- using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
- {
- byte[] data = ms.ToArray();
- fs.Write(data, 0, data.Length);
- fs.Flush();
- }
- }
- }
- /// <summary>
- /// DataTable导出到Excel的MemoryStream
- /// </summary>
- /// <param name="dtSource">源DataTable</param>
- /// <param name="strHeaderText">表头文本</param>
- public static MemoryStream Export(DataTable dtSource, string strHeaderText)
- {
- XSSFWorkbook workbook = new XSSFWorkbook();
- ISheet sheet = workbook.CreateSheet();
- ICellStyle dateStyle = workbook.CreateCellStyle();
- IDataFormat format = workbook.CreateDataFormat();
- dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
- #region 取得每列的列宽(最大宽度)
- int[] arrColWidth = new int[dtSource.Columns.Count];
- foreach (DataColumn item in dtSource.Columns)
- {
- //GBK对应的code page是CP936
- arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
- }
- for (int i = 0; i < dtSource.Rows.Count; i++)
- {
- for (int j = 0; j < dtSource.Columns.Count; j++)
- {
- int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
- if (intTemp > arrColWidth[j])
- {
- arrColWidth[j] = intTemp;
- }
- }
- }
- #endregion
- int rowIndex = 0;
- foreach (DataRow row in dtSource.Rows)
- {
- #region 新建表,填充表头,填充列头,样式
- if (rowIndex == 65535 || rowIndex == 0)
- {
- if (rowIndex != 0)
- {
- sheet = workbook.CreateSheet();
- }
- #region 表头及样式
- {
- IRow headerRow = sheet.CreateRow(0);
- headerRow.HeightInPoints = 25;
- headerRow.CreateCell(0).SetCellValue(strHeaderText);
- ICellStyle headStyle = workbook.CreateCellStyle();
- headStyle.Alignment = HorizontalAlignment.Center;
- IFont font = workbook.CreateFont();
- font.FontHeightInPoints = 20;
- font.Boldweight = 700;
- headStyle.SetFont(font);
- headerRow.GetCell(0).CellStyle = headStyle;
- sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
- }
- #endregion
- #region 列头及样式
- {
- IRow headerRow = sheet.CreateRow(1);
- ICellStyle headStyle = workbook.CreateCellStyle();
- headStyle.Alignment = HorizontalAlignment.Center;
- IFont font = workbook.CreateFont();
- font.FontHeightInPoints = 10;
- font.Boldweight = 700;
- headStyle.SetFont(font);
- foreach (DataColumn column in dtSource.Columns)
- {
- headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
- headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
- //设置列宽
- sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
- }
- }
- #endregion
- rowIndex = 2;
- }
- #endregion
- #region 填充内容
- ICellStyle contentStyle = workbook.CreateCellStyle();
- contentStyle.Alignment = HorizontalAlignment.Left;
- IRow dataRow = sheet.CreateRow(rowIndex);
- foreach (DataColumn column in dtSource.Columns)
- {
- ICell newCell = dataRow.CreateCell(column.Ordinal);
- newCell.CellStyle = contentStyle;
- string drValue = row[column].ToString();
- switch (column.DataType.ToString())
- {
- case "System.String"://字符串类型
- newCell.SetCellValue(drValue);
- break;
- case "System.DateTime"://日期类型
- DateTime dateV;
- DateTime.TryParse(drValue, out dateV);
- newCell.SetCellValue(dateV);
- 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
- rowIndex++;
- }
- using (MemoryStream ms = new MemoryStream())
- {
- workbook.Write(ms);
- ms.Flush();
- ms.Position = 0;
- //sheet.Dispose();
- //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
- return ms;
- }
- }
- /// <summary>
- /// 用于Web导出
- /// </summary>
- /// <param name="dtSource">源DataTable</param>
- /// <param name="strHeaderText">表头文本</param>
- /// <param name="strFileName">文件名</param>
- //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
- //{
- // HttpContext curContext = HttpContext.Current;
- // // 设置编码和附件格式
- // curContext.Response.ContentType = "application/vnd.ms-excel";
- // curContext.Response.ContentEncoding = Encoding.UTF8;
- // curContext.Response.Charset = "";
- // curContext.Response.AppendHeader("Content-Disposition",
- // "attachment;filename=" + HttpUtility.UrlEncode(strFileName + ".xls", Encoding.UTF8));
- // curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
- // curContext.Response.End();
- //}
- /// <summary>读取excel
- /// 默认第一行为标头
- /// 导出某一张表
- /// </summary>
- /// <param name="strFileName">excel文档路径</param>
- /// <returns></returns>
- public static DataTable ImportAppointSheet(XSSFWorkbook hssfworkbook, int index)
- {
- ISheet sheet = hssfworkbook.GetSheetAt(index);
- if (sheet == null) return null;
- DataTable dt = new DataTable(sheet.SheetName);
- IRow headerRow = sheet.GetRow(0);
- if (headerRow == null) return dt;
- if (headerRow.Cells.Count==0)
- {
- headerRow = sheet.GetRow(1);
- }
- int cellCount = headerRow.LastCellNum;
- for (int j = 0; j < cellCount; j++)
- {
- ICell cell = headerRow.GetCell(j);
- dt.Columns.Add(cell.ToStr());
- }
- for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
- {
- IRow row = sheet.GetRow(i);
- if(row==null)continue;
- 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;
- }
- /// <summary>
- /// 导出整个Excel
- /// </summary>
- /// <param name="strFileName"></param>
- /// <returns></returns>
- public static DataSet Import(string strFileName)
- {
- DataSet ds = new DataSet();
- XSSFWorkbook hssfworkbook;
- using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
- {
- hssfworkbook = new XSSFWorkbook(file);
- }
- var sheetCount = hssfworkbook.Count;
- for (int i = 0; i < sheetCount; i++)
- {
- DataTable dt = ImportAppointSheet(hssfworkbook, i);
- if (dt != null)
- ds.Tables.Add(dt);
- }
- return ds;
- }
- /// <summary>
- /// 将Excel数据转换为实体列表
- /// </summary>
- /// <typeparam name="T"></typeparam>
- /// <returns></returns>
- public static List<T> ConvertExcelSheetToModel<T>(string filePath)
- {
- var datas = new List<T>();
- var type = typeof(T);
- var sheetInfo = type.GetAttribute<SheetInfoAttribute>();
- if (sheetInfo == null) return datas;
- var fileName = filePath;
- if (!File.Exists(fileName)) return datas;
- using (var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
- {
- IWorkbook workbook = null; //新建IWorkbook对象
- var fileExtension = Path.GetExtension(fileName);
- if (fileExtension == ".xlsx") // 2007版本
- {
- workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
- }
- else if (fileExtension == ".xls") // 2003版本
- {
- workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
- }
- ISheet sheet = sheetInfo.SheetName == "" ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetInfo.SheetName);
- IRow row;
- // List<int> showIndex = new List<int> { 1, 2, 3, 4 };
- for (int i = sheetInfo.RowStartIndex; i < sheet.LastRowNum; i++)
- {
- row = sheet.GetRow(i);
- if (row != null)
- {
- datas.Add(ForeachClassProperties<T>(row));
- }
- }
- workbook.Close();
- }
- return datas;
- }
- /// <summary>
- /// C#反射遍历对象属性
- /// </summary>
- /// <typeparam name="T">对象类型</typeparam>
- /// <param name="model">对象</param>
- public static T ForeachClassProperties<T>(IRow row)
- {
- Type t = typeof(T);
- var model = Activator.CreateInstance<T>();
- PropertyInfo[] PropertyList = t.GetProperties();
- foreach (PropertyInfo item in PropertyList)
- {
- var index = item.GetCustomAttribute<CellIndexAttribute>()?.Index;
- ICell cell = row.GetCell(index ?? 0);
- if (cell == null)
- {
- item.SetValue(model, "");
- continue;
- }
- if (cell.IsMergedCell)
- {
- //row.Sheet.
- var sheet = row.Sheet;
- for (int i = 0; i < sheet.NumMergedRegions; i++)
- {
- var range = sheet.GetMergedRegion(i);
- if (range.IsInRange(cell.RowIndex, cell.ColumnIndex))
- {
- var useRow = row.Sheet.GetRow(range.FirstRow);
- item.SetValue(model, useRow.GetCell(range.FirstColumn) + "");
- break;
- }
- }
- }
- else
- {
- item.SetValue(model, row.GetCell(index ?? 0) + "");
- }
- }
- return model;
- }
- }
- public class SheetInfoAttribute : Attribute
- {
- public string FilePath { get; set; }
- public string SheetName { get; set; }
- public int RowStartIndex { get; set; }
- }
- public class CellIndexAttribute : Attribute
- {
- public CellIndexAttribute(int index)
- {
- Index = index;
- }
- public int Index { get; set; }
- }
- public static class RefletcExtend
- {
- public static T GetAttribute<T>(this Type type) where T : Attribute
- {
- return (T)type.GetCustomAttributes(typeof(T), false).FirstOrDefault();
- }
- }
- }
|