NOPIHelper

using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using System.Web; using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util; using NPOI.SS.Formula.Eval; using NPOI.SS.UserModel; using NPOI.SS.Util; using System.Collections; using System.Text.RegularExpressions; using NPOI.XSSF.UserModel;

namespace Common { public class NPOIHelper { #region 从datatable中将数据导出到excel /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> private static MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

#region 右击文件属性信息

//{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi;

// SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //}

#endregion

HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; 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() as HSSFSheet; }

#region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText);

HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion

#region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; 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); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { 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(); return ms; } }

/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> private static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; #region 右击文件属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi;

#endregion

XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式

if (rowIndex == 0) { #region 表头及样式 //{ // XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; // headerRow.HeightInPoints = 25; // headerRow.CreateCell(0).SetCellValue(strHeaderText);

// XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; // headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // XSSFFont font = workbook.CreateFont() as XSSFFont; // font.FontHeightInPoints = 20; // font.Boldweight = 700; // headStyle.SetFont(font);

// headerRow.GetCell(0).CellStyle = headStyle;

// //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); // //headerRow.Dispose(); //}

#endregion #region 列头及样式

{ XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; 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);

} //headerRow.Dispose(); } #endregion rowIndex = 1; } #endregion #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; }

} #endregion rowIndex++; } workbook.Write(fs); fs.Close(); }

/// <summary> /// 从DataTable中将数据导出到Excel文件 /// </summary> /// <param name="dtSource">提供导出数据的DataTable</param> /// <param name="headerText">表头文本</param> /// <returns></returns> public static MemoryStream ExportDataTable(DataTable dtSource, string headerText) { //创建工作表 var workbook = new HSSFWorkbook(); //创建sheet页 var sheet = workbook.CreateSheet(); #region 添加Excel文件属性信息

var dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "ZWKJ"; workbook.DocumentSummaryInformation = dsi;

var si = PropertySetFactory.CreateSummaryInformation(); si.Author = "鞠小军"; si.ApplicationName = "使用NPOI创建的Excel文件"; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si;

#endregion

//设置日期格式 var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 var columnWidth = new int[dtSource.Columns.Count]; //遍历DataTable的列 foreach (DataColumn column in dtSource.Columns) { columnWidth[column.Ordinal] = Encoding.GetEncoding(936).GetBytes(column.ColumnName).Length; } //遍历所有的Row,若当前Row内容长度超出列名长度，则将此列的长度设为该Row内容长度 for (var i = 0; i < dtSource.Rows.Count; i++) { for (var j = 0; j < dtSource.Columns.Count; j++) { var currentRowLength = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (currentRowLength > columnWidth[j]) columnWidth[j] = currentRowLength; } }

var rowIndex = 0; #region 表头及样式

var headRow = sheet.CreateRow(0); headRow.HeightInPoints = 25; headRow.CreateCell(0).SetCellValue(headerText);

var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.Boldweight = 700; font.FontHeightInPoints = 20; headStyle.SetFont(font); headRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); #endregion #region 列头及样式

var columnRow = sheet.CreateRow(1); var columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; var columnFont = workbook.CreateFont(); columnFont.Boldweight = 700; columnFont.FontHeightInPoints = 10; columnStyle.SetFont(columnFont); foreach (DataColumn column in dtSource.Columns) { //设置列头内容 columnRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //设置列头样式 columnRow.GetCell(column.Ordinal).CellStyle = columnStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (columnWidth[column.Ordinal] + 1) * 256);

} #endregion rowIndex = 2; foreach (DataRow row in dtSource.Rows) { if (rowIndex == 65535) sheet = workbook.CreateSheet(); #region 填充数据

} } rowIndex++;

#endregion

} using (var ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } }

/// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName) { string[] temp = strFileName.Split('.');

if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && dtSource.Rows.Count < 65536) { using (MemoryStream ms = ExportDT(dtSource, strHeaderText)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } else { if (temp[temp.Length - 1] == "xls") strFileName = strFileName + "x";

using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { ExportDTI(dtSource, strHeaderText, fs); } } }

#endregion

#region 从excel中将数据导出到datatable

/// <summary> /// 读取excel 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName) { DataTable dt = new DataTable(); IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { wb = WorkbookFactory.Create(file); } ISheet sheet = wb.GetSheetAt(0); dt = ImportDt(sheet, 0, true); return dt; }

/// <summary> /// 读取Excel流到DataTable /// </summary> /// <param name="stream">Excel流</param> /// <returns>第一个sheet中的数据</returns> public static DataTable ImportExceltoDt(Stream stream) { try { DataTable dt = new DataTable(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } ISheet sheet = wb.GetSheetAt(0); dt = ImportDt(sheet, 0, true); return dt; } catch (Exception) {

throw; } }

/// <summary> /// 读取Excel流到DataTable /// </summary> /// <param name="stream">Excel流</param> /// <param name="sheetName">表单名</param> /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param> /// <returns>指定sheet中的数据</returns> public static DataTable ImportExceltoDt(Stream stream, string sheetName, int HeaderRowIndex) { try { DataTable dt = new DataTable(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } ISheet sheet = wb.GetSheet(sheetName); dt = ImportDt(sheet, HeaderRowIndex, true); return dt; } catch (Exception) {

throw; } }

/// <summary> /// 读取Excel流到DataSet /// </summary> /// <param name="stream">Excel流</param> /// <returns>Excel中的数据</returns> public static DataSet ImportExceltoDs(Stream stream) { try { DataSet ds = new DataSet(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } for (int i = 0; i < wb.NumberOfSheets; i++) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheetAt(i); dt = ImportDt(sheet, 0, true); ds.Tables.Add(dt); } return ds; } catch (Exception) {

throw; } }

/// <summary> /// 读取Excel流到DataSet /// </summary> /// <param name="stream">Excel流</param> /// <param name="dict">字典参数，key：sheet名，value：列头所在行号，-1表示没有列头</param> /// <returns>Excel中的数据</returns> public static DataSet ImportExceltoDs(Stream stream, Dictionary<string, int> dict) { try { DataSet ds = new DataSet(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } foreach (string key in dict.Keys) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheet(key); dt = ImportDt(sheet, dict[key], true); ds.Tables.Add(dt); } return ds; } catch (Exception) {

throw; } }

/// <summary> /// 读取excel /// </summary> /// <param name="strFileName">excel文件路径</param> /// <param name="sheet">需要导出的sheet</param> /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(file); } ISheet sheet = wb.GetSheet(SheetName); DataTable table = new DataTable(); table = ImportDt(sheet, HeaderRowIndex, true); //ExcelFileStream.Close(); workbook = null; sheet = null; return table; }

/// <summary> /// 读取excel /// </summary> /// <param name="strFileName">excel文件路径</param> /// <param name="sheet">需要导出的sheet序号</param> /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { wb = WorkbookFactory.Create(file); } ISheet isheet = wb.GetSheetAt(SheetIndex); DataTable table = new DataTable(); table = ImportDt(isheet, HeaderRowIndex, true); //ExcelFileStream.Close(); workbook = null; isheet = null; return table; }

/// <summary> /// 读取excel /// </summary> /// <param name="strFileName">excel文件路径</param> /// <param name="sheet">需要导出的sheet</param> /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { wb = WorkbookFactory.Create(file); } ISheet sheet = wb.GetSheet(SheetName); DataTable table = new DataTable(); table = ImportDt(sheet, HeaderRowIndex, needHeader); //ExcelFileStream.Close(); workbook = null; sheet = null; return table; }

/// <summary> /// 读取excel /// </summary> /// <param name="strFileName">excel文件路径</param> /// <param name="sheet">需要导出的sheet序号</param> /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param> /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { wb = WorkbookFactory.Create(file); } ISheet sheet = wb.GetSheetAt(SheetIndex); DataTable table = new DataTable(); table = ImportDt(sheet, HeaderRowIndex, needHeader); //ExcelFileStream.Close(); workbook = null; sheet = null; return table; }

/// <summary> /// 将制定sheet中的数据导出到datatable中 /// </summary> /// <param name="sheet">需要导出的sheet</param> /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param> /// <returns></returns> private static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader) { DataTable table = new DataTable(); IRow headerRow; int cellCount; try { if (HeaderRowIndex < 0 || !needHeader) { headerRow = sheet.GetRow(0); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else { headerRow = sheet.GetRow(HeaderRowIndex); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { if (headerRow.GetCell(i) == null) { if (table.Columns.IndexOf(Convert.ToString(i)) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); }

} else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(headerRow.GetCell(i).ToString()); table.Columns.Add(column); } } } int rowCount = sheet.LastRowNum; for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++) { try { IRow row; if (sheet.GetRow(i) == null) { row = sheet.CreateRow(i); } else { row = sheet.GetRow(i); }

DataRow dataRow = table.NewRow();

for (int j = row.FirstCellNum; j <= cellCount; j++) { try { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.String: string str = row.GetCell(j).StringCellValue; if (str != null && str.Length > 0) { dataRow[j] = str.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { dataRow[j] = strFORMULA.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } break; default: dataRow[j] = ""; break; } } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); } } table.Rows.Add(dataRow); } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); } } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); } return table; } #endregion

public static void InsertSheet(string outputFile, string sheetname, DataTable dt) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); IWorkbook hssfworkbook = WorkbookFactory.Create(readfile); //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); int num = hssfworkbook.GetSheetIndex(sheetname); ISheet sheet1; if (num >= 0) sheet1 = hssfworkbook.GetSheet(sheetname); else { sheet1 = hssfworkbook.CreateSheet(sheetname); }

try { if (sheet1.GetRow(0) == null) { sheet1.CreateRow(0); } for (int coluid = 0; coluid < dt.Columns.Count; coluid++) { if (sheet1.GetRow(0).GetCell(coluid) == null) { sheet1.GetRow(0).CreateCell(coluid); }

sheet1.GetRow(0).GetCell(coluid).SetCellValue(dt.Columns[coluid].ColumnName); } } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; }

for (int i = 1; i <= dt.Rows.Count; i++) { try { if (sheet1.GetRow(i) == null) { sheet1.CreateRow(i); } for (int coluid = 0; coluid < dt.Columns.Count; coluid++) { if (sheet1.GetRow(i).GetCell(coluid) == null) { sheet1.GetRow(i).CreateCell(coluid); }

sheet1.GetRow(i).GetCell(coluid).SetCellValue(dt.Rows[i - 1][coluid].ToString()); } } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); //throw; } } try { readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); } }

#region 更新excel中的数据

/// <summary> /// 更新Excel表格// /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的数据</param> /// <param name="coluid">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid) { //FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); IWorkbook hssfworkbook = null; // WorkbookFactory.Create(outputFile); //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int i = 0; i < updateData.Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluid) == null) { sheet1.GetRow(i + rowid).CreateCell(coluid); }

sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } try { //readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); }

}

HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); readfile.Close(); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int j = 0; j < coluids.Length; j++) { for (int i = 0; i < updateData[j].Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null) { sheet1.GetRow(i + rowid).CreateCell(coluids[j]); } sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); } } } try { FileStream writefile = new FileStream(outputFile, FileMode.Create); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); } }

/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的数据</param> /// <param name="coluid">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int i = 0; i < updateData.Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluid) == null) { sheet1.GetRow(i + rowid).CreateCell(coluid); }

sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } try { readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); }

}

#endregion

public static int GetSheetNumber(string outputFile) { int number = 0; try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); number = hssfworkbook.NumberOfSheets;

} catch (Exception exception) { //wl.WriteLogs(exception.ToString()); } return number; }

public static ArrayList GetSheetName(string outputFile) { ArrayList arrayList = new ArrayList(); try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { arrayList.Add(hssfworkbook.GetSheetName(i)); } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); } return arrayList; }

public static bool isNumeric(String message, out double result) { Regex rex = new Regex(@"^[-]?\d+[.]?\d*$"); result = -1; if (rex.IsMatch(message)) { result = double.Parse(message); return true; } else return false;

}

////////// 现用导出 \\\\\\\\\\ /// <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, Encoding.UTF8)); curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer()); curContext.Response.End(); }

/// <summary> /// DataTable导出到Excel的MemoryStream 第二步 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

#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 = DateTime.Now; workbook.SummaryInformation = si; }

#endregion

HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; 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() as HSSFSheet; } #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText)) { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } }

#endregion

#region 列头及样式

{ HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; 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); } //headerRow.Dispose(); } #endregion rowIndex = 1; }

#endregion #region 填充内容

HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; 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> /// /注：分浏览器进行编码（IE必须编码，FireFox不能编码，Chrome可编码也可不编码） /// </summary> /// <param name="ds"></param> /// <param name="strHeaderText"></param> /// <param name="strFileName"></param> public static void ExportByWeb(DataSet ds, string strHeaderText, string strFileName) { HttpContext curContext = HttpContext.Current; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.Charset = ""; if (curContext.Request.UserAgent.ToLower().IndexOf("firefox", System.StringComparison.Ordinal) > 0) { curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName); } else { curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)); }

// curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" +strFileName); curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); curContext.Response.BinaryWrite(ExportDataSetToExcel(ds, strHeaderText).GetBuffer()); curContext.Response.End(); }

/// <summary> /// 由DataSet导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> private static MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); string[] sheetNames = sheetName.Split(','); for (int i = 0; i < sheetNames.Length; i++) { ISheet sheet = workbook.CreateSheet(sheetNames[i]); #region 列头 IRow headerRow = sheet.CreateRow(0); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //取得列宽 int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count]; foreach (DataColumn item in sourceDs.Tables[i].Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } // 处理列头 foreach (DataColumn column in sourceDs.Tables[i].Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1)*256); } #endregion #region 填充值 int rowIndex = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceDs.Tables[i].Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } #endregion } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; }

/// <summary> /// 验证导入的Excel是否有数据 /// </summary> /// <param name="excelFileStream"></param> /// <returns></returns> public static bool HasData(Stream excelFileStream) { using (excelFileStream) { IWorkbook workBook = new HSSFWorkbook(excelFileStream); if (workBook.NumberOfSheets > 0) { ISheet sheet = workBook.GetSheetAt(0); return sheet.PhysicalNumberOfRows > 0; } } return false; }

/// <summary> /// 后面添加 /// </summary> /// <param name="dataSet"></param> /// <param name="fileFullPath"></param>

public static void ExportToFile(DataSet dataSet, string fileFullPath) { List<DataTable> dts = new List<DataTable>(); foreach (DataTable dt in dataSet.Tables) dts.Add(dt); ExportToFile(dts, fileFullPath); } public static void ExportToFile(DataTable dataTable, string fileFullPath) { List<DataTable> dts = new List<DataTable>(); dts.Add(dataTable); ExportToFile(dts, fileFullPath); } public static void ExportToFile(IEnumerable<DataTable> dataTables, string fileFullPath) { IWorkbook workbook = new XSSFWorkbook(); int i = 0; foreach (DataTable dt in dataTables) { string sheetName = string.IsNullOrEmpty(dt.TableName) ? "Sheet " + (++i).ToString() : dt.TableName; ISheet sheet = workbook.CreateSheet(sheetName);

IRow headerRow = sheet.CreateRow(0); for (int j = 0; j < dt.Columns.Count; j++) { string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName) ? "Column " + j.ToString() : dt.Columns[j].ColumnName; headerRow.CreateCell(j).SetCellValue(columnName); }

for (int a = 0; a < dt.Rows.Count; a++) { DataRow dr = dt.Rows[a]; IRow row = sheet.CreateRow(a + 1); for (int b = 0; b < dt.Columns.Count; b++) { row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty); } } }

using (FileStream fs = File.Create(fileFullPath)) { workbook.Write(fs); } }

/// <summary> /// HSSFRow Copy Command /// /// Description: Inserts a existing row into a new row, will automatically push down /// any existing rows. Copy is done cell by cell and supports, and the /// command tries to copy all properties available (style, merged cells, values, etc...) /// </summary> /// <param name="workbook">Workbook containing the worksheet that will be changed</param> /// <param name="worksheet">WorkSheet containing rows to be copied</param> /// <param name="sourceRowNum">Source Row Number</param> /// <param name="destinationRowNum">Destination Row Number</param> private void CopyRow(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row IRow newRow = worksheet.GetRow(destinationRowNum); IRow sourceRow = worksheet.GetRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1); } else { newRow = worksheet.CreateRow(destinationRowNum); }

// Loop through source columns to add to new row for (int i = 0; i < sourceRow.LastCellNum; i++) { // Grab a copy of the old/new cell ICell oldCell = sourceRow.GetCell(i); ICell newCell = newRow.CreateCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; }

// Copy style from old cell and apply to new cell ICellStyle newCellStyle = workbook.CreateCellStyle(); newCellStyle.CloneStyleFrom(oldCell.CellStyle); ; newCell.CellStyle = newCellStyle;

// If there is a cell comment, copy if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

// If there is a cell hyperlink, copy if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

// Set the cell data type newCell.SetCellType(oldCell.CellType);

// Set the cell data value switch (oldCell.CellType) { case CellType.Blank: newCell.SetCellValue(oldCell.StringCellValue); break; case CellType.Boolean: newCell.SetCellValue(oldCell.BooleanCellValue); break; case CellType.Error: newCell.SetCellErrorValue(oldCell.ErrorCellValue); break; case CellType.Formula: newCell.SetCellFormula(oldCell.CellFormula); break; case CellType.Numeric: newCell.SetCellValue(oldCell.NumericCellValue); break; case CellType.String: newCell.SetCellValue(oldCell.RichStringCellValue); break; case CellType.Unknown: newCell.SetCellValue(oldCell.StringCellValue); break; } }

// If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.NumMergedRegions; i++) { CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i); if (cellRangeAddress.FirstRow == sourceRow.RowNum) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum, (newRow.RowNum + (cellRangeAddress.FirstRow - cellRangeAddress.LastRow)), cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn); worksheet.AddMergedRegion(newCellRangeAddress); } }

}

public static List<DataTable> GetDataTablesFrom(string xlsxFile) { if (!File.Exists(xlsxFile)) throw new FileNotFoundException("文件不存在");

List<DataTable> result = new List<DataTable>(); Stream stream = new MemoryStream(File.ReadAllBytes(xlsxFile)); IWorkbook workbook = new XSSFWorkbook(stream); for (int i = 0; i < workbook.NumberOfSheets; i++) { DataTable dt = new DataTable(); ISheet sheet = workbook.GetSheetAt(i); IRow headerRow = sheet.GetRow(0);

int cellCount = headerRow.LastCellNum; for (int j = headerRow.FirstCellNum; j < cellCount; j++) { DataColumn column = new DataColumn(headerRow.GetCell(j).StringCellValue); dt.Columns.Add(column); }

int rowCount = sheet.LastRowNum; for (int a = (sheet.FirstRowNum + 1); a < rowCount; a++) { IRow row = sheet.GetRow(a); if (row == null) continue;

DataRow dr = dt.NewRow(); for (int b = row.FirstCellNum; b < cellCount; b++) { if (row.GetCell(b) == null) continue; dr[b] = row.GetCell(b).ToString(); }

dt.Rows.Add(dr); } result.Add(dt); } stream.Close();

return result; }

/// <summary> /// 导出清算模板 /// </summary> public static void ExportQSTemplate(string unitName,DataTable dtSf) { if (dtSf == null || dtSf.Rows.Count == 0) { return; } FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Files/Templetes/月清算导入表模板.xls"), FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheet1 = hssfworkbook.GetSheet("月清算导入模板"); ICellStyle blackBorder = hssfworkbook.CreateCellStyle(); blackBorder.Alignment = HorizontalAlignment.Center; blackBorder.VerticalAlignment = VerticalAlignment.Center; blackBorder.BorderBottom = BorderStyle.Thin; blackBorder.BorderLeft = BorderStyle.Thin; blackBorder.BorderRight = BorderStyle.Thin; blackBorder.BorderTop = BorderStyle.Thin; blackBorder.FillForegroundColor =31; blackBorder.FillPattern = FillPattern.SolidForeground; blackBorder.BottomBorderColor = HSSFColor.Black.Index; blackBorder.LeftBorderColor = HSSFColor.Black.Index; blackBorder.RightBorderColor = HSSFColor.Black.Index; blackBorder.TopBorderColor = HSSFColor.Black.Index; //blackBorder.DataFormat=HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); IFont font10 = hssfworkbook.CreateFont(); font10.FontHeightInPoints = 10; font10.FontName = "宋体"; blackBorder.SetFont(font10); string sfName = string.Empty;

sheet1.GetRow(0).GetCell(0).SetCellValue(unitName); sheet1.GetRow(1).GetCell(10).SetCellValue(dtSf.Rows[0]["SF_NAME"].ToString()); for (int i = 1; i < dtSf.Rows.Count; i++) { sfName = dtSf.Rows[i]["SF_NAME"].ToString(); ICell cell = sheet1.GetRow(1).CopyCell(10, 10 + (i) * 4); //cell.CellStyle = fontStyle; cell.SetCellValue(sfName); CellRangeAddress region = new CellRangeAddress(1, 1, 10 + (i) * 4, 13+(i) * 4); sheet1.AddMergedRegion(region); for (int ii = region.FirstRow; ii <= region.LastRow; ii++) { IRow row = sheet1.GetRow(ii); for (int j = region.FirstColumn; j <= region.LastColumn; j++) { ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); //ICell singleCell = row.GetCell(j); if (singleCell != null) { singleCell.CellStyle = blackBorder; } } } sheet1.GetRow(2).CopyCell(10, 10+i*4); sheet1.GetRow(2).CopyCell(11, 11+i*4); sheet1.GetRow(2).CopyCell(12, 12 + i * 4); sheet1.GetRow(2).CopyCell(13, 13 + i * 4); sheet1.GetRow(3).CopyCell(10, 10 + i * 4); sheet1.GetRow(3).CopyCell(11, 11 + i * 4); sheet1.GetRow(3).CopyCell(12, 12 + i * 4); sheet1.GetRow(3).CopyCell(13, 13 + i * 4); sheet1.ForceFormulaRecalculation = true; } //sheet1.GetRow(2).GetCell(10).SetCellValue("bbbbbb"); // sheet1.GetRow(2).CopyCell(10, 14).SetCellValue("bijiang"); for (int k = 1; k < 51; k++) { sheet1.CopyRow(3, 3 + k).Height = sheet1.GetRow(3).Height; }

// sheet1.GetRow(7).Height = 40; MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; //curContext.Response.ContentType = "application/x-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode("bbbb_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); curContext.Response.End(); hssfworkbook = null; ms.Close(); ms.Dispose(); }

public static IList<T> ReadListFromStream<T>(string fileName, Stream stream, bool ignoreFirstLine) where T : new() { string extendsion = Path.GetExtension(fileName).TrimStart('.');

IList<T> list = new List<T>(); for (int i = 0; i < 2; i++) { ISheet sheet = workBook.GetSheetAt(i);

if (sheet.PhysicalNumberOfRows > 0) { if (!ignoreFirstLine) { //检查列是否与ExcelAttribute定义的一致 ValidTableHeader<T>(sheet); }

for (int j = ignoreFirstLine ? 0 : 1; j < sheet.PhysicalNumberOfRows; j++) { var row = sheet.GetRow(j);

T entity = new T();

var propertys = typeof(T).GetProperties();

foreach (var p in propertys) { var excel = Attribute.GetCustomAttribute(p, typeof(ExcelAttribute)) as ExcelAttribute;

if (excel != null) { var cellValue = row.GetCell(excel.ColumnIndex);

if (cellValue == null || string.IsNullOrEmpty(cellValue.ToString())) {} //throw new NPOIException(string.Format("第{0}行“{1}”不能为空", j + 1, excel.ColumnName));

string cellValueStr = cellValue.ToString(); if (p.PropertyType == typeof(int)) { int temp; if (!int.TryParse(cellValueStr, out temp)) //throw new NPOIException(string.Format("第{0}行“{1}”应为{2}类型", j + 1, excel.ColumnName, "整数")); p.SetValue(entity, temp, null); } else if (p.PropertyType == typeof(DateTime)) { DateTime temp; if (!DateTime.TryParse(cellValueStr, out temp)) //throw new NPOIException(string.Format("第{0}行“{1}”应为{2}类型", j + 1, excel.ColumnName, "时间")); p.SetValue(entity, temp, null); } else if (p.PropertyType == typeof(bool)) { bool temp; if (!bool.TryParse(cellValueStr, out temp)) // throw new NPOIException(string.Format("第{0}行“{1}”应为{2}类型", j + 1, excel.ColumnName, "布尔")); p.SetValue(entity, cellValueStr, null); } else if (p.PropertyType == typeof(string)) { p.SetValue(entity, cellValueStr, null); } else { //throw new NPOIException(string.Format("第{0}行“{1}”类型未知，请联系开发人员", j + 1, excel.ColumnName)); } } } list.Add(entity); } } } return list; }

/// <summary> /// 检查表头与定义是否匹配 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="firstRow"></param> /// <returns></returns> private static void ValidTableHeader<T>(ISheet sheet) where T : new() { var firstRow = sheet.GetRow(0);

var propertys = typeof(T).GetProperties();

foreach (var p in propertys) { var excel = Attribute.GetCustomAttribute(p, typeof(ExcelAttribute)) as ExcelAttribute;

if (excel != null) { if (!firstRow.GetCell(excel.ColumnIndex).StringCellValue.Trim().Equals(excel.ColumnName)) { //throw new NPOIException(string.Format("Excel表格第{0}列标题应为{1}", excel.ColumnIndex + 1, excel.ColumnName)); } } } }

/// <summary> /// 导入清算基础数据 /// </summary> /// <param name="fileName"></param> /// <param name="stream"></param> /// <param name="unitID"></param> /// <param name="sf"></param> /// <returns></returns> public static Dictionary<DataTable, DataTable> GetQsImportFileData(string fileName,Stream stream,string unitID,DataTable sf) { string[] mainColumnNames = { "ID", "PJNUM", "PJJDUSER", "STAGE", "YEARPREPROPORTION", "YEARPREQS", "QSYEAR", "QSMONTH", "MONTHFINISHPROPORTION", "TOTALFEE" }; string[] childColumnNames = {"ID","PID", "UNITID", "SFID", "YEARTOTAL", "SUMFEE", "MONTHPROPORTION", "MONTHQS" }; DataTable mainData = new DataTable(); DataTable childData = new DataTable(); int starRow = 3; for (int i = 0; i < mainColumnNames.Length; i++) { DataColumn column = new DataColumn(mainColumnNames[i]); mainData.Columns.Add(column); } for (int j = 0; j < childColumnNames.Length; j++) { DataColumn column = new DataColumn(childColumnNames[j]); childData.Columns.Add(column); } string extendsion = Path.GetExtension(fileName).TrimStart('.');

IWorkbook workBook = null; switch (extendsion) { case "xls": workBook = new HSSFWorkbook(stream); break; case "xlsx": workBook = new XSSFWorkbook(stream); break; } if (workBook != null) { ISheet sheet = workBook.GetSheetAt(0); IRow row; string idMain; string idChild; for (int r = starRow; r < sheet.LastRowNum; r++)//循环行 { row = sheet.GetRow(r); if (string.IsNullOrEmpty(row.GetCell(1).StringCellValue))//项目编号 { continue; } DataRow drMain = mainData.NewRow(); idMain = Guid.NewGuid().ToString(); drMain["ID"] = idMain; drMain["PJNUM"] = row.GetCell(1).StringCellValue; drMain["PJJDUSER"] = row.GetCell(3).StringCellValue; drMain["STAGE"] = row.GetCell(4).StringCellValue; drMain["YEARPREPROPORTION"] = row.GetCell(5).NumericCellValue; drMain["YEARPREQS"] = row.GetCell(6).NumericCellValue; drMain["QSYEAR"] = row.GetCell(7).DateCellValue.Year; drMain["QSMONTH"] = row.GetCell(7).DateCellValue.Month; drMain["MONTHFINISHPROPORTION"] = row.GetCell(8).NumericCellValue; drMain["TOTALFEE"] = row.GetCell(9).NumericCellValue; mainData.Rows.Add(drMain); for (int rc = 0; rc < sf.Rows.Count; rc++) { DataRow drChild = childData.NewRow(); idChild = Guid.NewGuid().ToString(); drChild["ID"] = idChild; drChild["PID"] = idMain; drChild["UNITID"] = unitID; drChild["SFID"] = sf.Rows[rc]["SF_ID"].ToString(); drChild["YEARTOTAL"] = row.GetCell(10+rc*4).NumericCellValue; drChild["SUMFEE"] = row.GetCell(11 + rc * 4).NumericCellValue; drChild["MONTHPROPORTION"] = row.GetCell(12 + rc * 4).NumericCellValue; drChild["MONTHQS"] = row.GetCell(13 + rc * 4).NumericCellValue; } } Dictionary<DataTable,DataTable> dt=new Dictionary<DataTable, DataTable>(); dt.Add(mainData, childData); return dt; } return null; }

} }

posted on 2016-09-01 00:02 bijiang100 阅读(265) 评论(0) 编辑收藏举报