using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HPSF; using System.Drawing; using NPOI.SS.Util; using NPOI.HSSF.Util; using System.Web; namespace Nopi { public static class NOPIHelper { #region DataTable导出到Excel /// <summary> /// 用于Web导出 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">文件名</param> public static void ExportByWeb(DataTable dtSource, 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, Encoding.UTF8)); curContext.Response.BinaryWrite(Export(dtSource).GetBuffer()); curContext.Response.End(); } /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "XXXX"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "XXXX"; //填加xls文件作者信息 si.ApplicationName = "导出程序"; //填加xls文件创建程序信息 si.LastAuthor = "XXXX"; //填加xls文件最后保存者信息 si.Comments = "XXXX"; //填加xls文件作者信息 si.Title = "XXXX"; //填加xls文件标题信息 si.Subject = "XXXX";//填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { 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; } } } 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(0); 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 = 1; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); 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; return ms; } } /// <summary> /// 获取属性名称相关联的的单元格索引 /// </summary> /// <param name="propName"></param> private static int GetCell(string propName, IRow headerRow) { var findCell = headerRow.Cells.First(cell => cell.StringCellValue.Equals(GetChineseHerder(propName))); if (findCell != null) { return headerRow.Cells.IndexOf(findCell); } else { throw new Exception(string.Format("没找发现与属性{0}相对应的中文属性", propName)); } } public static string GetChineseHerder(string propName) { propName = propName.ToUpper(); switch (propName) { case "ZCBM": return "资产编码"; case "ZCMC": return "资产名称"; case "GGXH": return "规格型号"; case "CPJH": return "车牌井号"; case "AZDD": return "安装地点"; case "FHSL": return "复合数量"; case "JSZK": return "技术状况"; case "SYZK": return "使用状况"; case "ZRR": return "责任人"; case "PDSJ": return "盘点时间"; case "PDZK": return "盘点状况"; case "PYPKYY": return "盘盈盘亏原因"; default: return string.Empty; } } //private static IRow GetRow(string zcbm, ISheet sheet) //{ // for (int i = 0; i <= sheet.LastRowNum; i++) // { // if (sheet.GetRow(i).Cells[0].StringCellValue.Equals(zcbm)) // { // return sheet.GetRow(i); // } // } // throw new Exception(string.Format("没找到与资产编码{0}匹配的行", zcbm)); //} #endregion #region 读取excel中内容 返回DataTable public static DataTable ExcelToDataTable(string excelPath, string sheetName) { return ExcelToDataTable(excelPath, sheetName, true); } public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader) { using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { HSSFWorkbook workbook = new HSSFWorkbook(fileStream); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet; return ExcelToDataTable(sheet, evaluator, firstRowAsHeader); } } private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader) { if (firstRowAsHeader) { return ExcelToDataTableFirstRowAsHeader(sheet, evaluator); } else { return ExcelToDataTable(sheet, evaluator); } } private static DataTable ExcelToDataTableFirstRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator) { using (DataTable dt = new DataTable()) { HSSFRow firstRow = sheet.GetRow(0) as HSSFRow; int cellCount = GetCellCount(sheet); for (int i = 0; i < cellCount; i++) { if (firstRow.GetCell(i) != null) { dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string)); } else { dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string)); } } for (int i = 1; i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i) as HSSFRow; DataRow dr = dt.NewRow(); FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr); } dt.TableName = sheet.SheetName; return dt; } } private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator) { using (DataTable dt = new DataTable()) { if (sheet.LastRowNum != 0) { int cellCount = GetCellCount(sheet); for (int i = 0; i < cellCount; i++) { dt.Columns.Add(string.Format("F{0}", i), typeof(string)); } for (int i = 0; i < sheet.FirstRowNum; ++i) { DataRow dr = dt.NewRow(); dt.Rows.Add(dr); } for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i) as HSSFRow; DataRow dr = dt.NewRow(); FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr); } } dt.TableName = sheet.SheetName; return dt; } } private static void FillDataRowByHSSFRow(HSSFRow row, HSSFFormulaEvaluator evaluator, ref DataRow dr) { if (row != null) { for (int j = 0; j < dr.Table.Columns.Count; j++) { HSSFCell cell = row.GetCell(j) as HSSFCell; if (cell != null) { switch (cell.CellType) { case CellType.BLANK: dr[j] = DBNull.Value; break; case CellType.BOOLEAN: dr[j] = cell.BooleanCellValue; break; case CellType.NUMERIC: if (DateUtil.IsCellDateFormatted(cell)) { dr[j] = cell.DateCellValue; } else { dr[j] = cell.NumericCellValue; } break; case CellType.STRING: dr[j] = cell.StringCellValue; break; case CellType.ERROR: dr[j] = cell.ErrorCellValue; break; case CellType.FORMULA: cell = evaluator.EvaluateInCell(cell) as HSSFCell; dr[j] = cell.ToString(); break; default: throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType)); } } } } } private static int GetCellCount(HSSFSheet sheet) { int firstRowNum = sheet.FirstRowNum; int cellCount = 0; for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i) { HSSFRow row = sheet.GetRow(i) as HSSFRow; if (row != null && row.LastCellNum > cellCount) { cellCount = row.LastCellNum; } } return cellCount; } #endregion #region 读取excel中内容,选择固定的行作为列名 返回DataTable /** * 读取excel中内容,选择固定的行作为列名 返回DataTable * 2015-03-26wytadd * excelPath url * sheetName sheet名 * rowNumber 从哪行开始读取数据并作为表头 */ public static DataTable ExcelToDataTable(string excelPath, string sheetName,int rowNumber) { if (rowNumber > 0) { return ExcelToDataTable(excelPath, sheetName, false, rowNumber); } else { return ExcelToDataTable(excelPath, sheetName, true, rowNumber); } } public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader, int rowNumber) { using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { HSSFWorkbook workbook = new HSSFWorkbook(fileStream); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet; return ExcelToDataTable(sheet, evaluator, firstRowAsHeader,rowNumber); } } private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader, int rowNumber) { if (firstRowAsHeader) { return ExcelToDataTableFirstRowAsHeader(sheet, evaluator); } else { if (rowNumber==0) { return ExcelToDataTable(sheet, evaluator); }else{ return ExcelToDataTableRowNumberRowAsHeader(sheet, evaluator,rowNumber); } } } private static DataTable ExcelToDataTableRowNumberRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, int rowNumber) { using (DataTable dt = new DataTable()) { HSSFRow firstRow = sheet.GetRow(rowNumber-1) as HSSFRow; int cellCount = GetCellCount(sheet); for (int i = 0; i < cellCount; i++) { if (firstRow.GetCell(i) != null) { //个别情况需要进行下面的设置，避免有的列为整型的取值会报异常， firstRow.GetCell(i).SetCellType(CellType.STRING); dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string)); } else { dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string)); } } for (int i = rowNumber; i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i) as HSSFRow; DataRow dr = dt.NewRow(); FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr); } dt.TableName = sheet.SheetName; return dt; } } #endregion } }
以上是修改的npoi的默认代码调用部分代码场景为
try
{
//遍历导入表返回dt
dt_FromNpoi = NOPIHelper.ExcelToDataTable(Server.MapPath("excelload") + "\" + in_File.FileName, "租赁情况统计表",3);
}
catch (Exception)
{
Page.RegisterStartupScript("", "<script>alert('导入模板有错误，无法遍历导入模板数据到系统，请重新导入！')</script>");
this.In_Div.Visible = false;
}
其中的3为想要从哪行进行取值，并且将此列作为表头