private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet) { SetCellRangeAddress(sheet, 0, 0, 0, 1); SetCellRangeAddress(sheet, 0, 0, 3, 4); SetCellRangeAddress(sheet, 0, 0, 6, 7); SetCellRangeAddress(sheet, 0, 0, 9, 10); SetCellRangeAddress(sheet, 0, 0, 12, 14); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("输入功率标定"); row.CreateCell(3).SetCellValue("输出功率标定"); row.CreateCell(6).SetCellValue("反射功率标定"); row.CreateCell(9).SetCellValue("ALC功率标定"); row.CreateCell(12).SetCellValue("衰减补偿"); row.GetCell(0).CellStyle = GetCellStyle(book); row.GetCell(3).CellStyle = GetCellStyle(book); row.GetCell(6).CellStyle = GetCellStyle(book); row.GetCell(9).CellStyle = GetCellStyle(book); row.GetCell(12).CellStyle = GetCellStyle(book); row = sheet.CreateRow(1); for (int i = 0; i < 4; i++) { row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压"); row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点"); row.GetCell(3 * i).CellStyle = GetCellStyle(book); row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book); } row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值"); row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值"); row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值"); row.GetCell(12).CellStyle = GetCellStyle(book); row.GetCell(13).CellStyle = GetCellStyle(book); row.GetCell(14).CellStyle = GetCellStyle(book); }
public void TestNotCreateEmptyCells() { IWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = new SheetBuilder(wb, testData).Build(); Assert.AreEqual(sheet.PhysicalNumberOfRows, 3); NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0); NPOI.SS.UserModel.ICell firstCell = firstRow.GetCell(0); Assert.AreEqual(firstCell.CellType, CellType.NUMERIC); Assert.AreEqual(1.0, firstCell.NumericCellValue, 0.00001); NPOI.SS.UserModel.IRow secondRow = sheet.GetRow(1); Assert.IsNotNull(secondRow.GetCell(0)); Assert.IsNull(secondRow.GetCell(2)); NPOI.SS.UserModel.IRow thirdRow = sheet.GetRow(2); Assert.AreEqual(CellType.STRING, thirdRow.GetCell(0).CellType); String cellValue = thirdRow.GetCell(0).StringCellValue; Assert.AreEqual(testData[2][0].ToString(), cellValue); Assert.AreEqual(CellType.FORMULA, thirdRow.GetCell(2).CellType); Assert.AreEqual("A1+B2", thirdRow.GetCell(2).CellFormula); }
private static void SetJZDRowValue(NPOI.SS.UserModel.IRow row, JZD jzd) { IPoint point = jzd.Feature.Shape as IPoint; row.GetCell(1).SetCellValue("J" + jzd.JZDH); row.GetCell(2).SetCellValue(point.X); row.GetCell(3).SetCellValue(point.Y); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName) { DataTable dt = new DataTable(); // HSSFWorkbook hssfworkbook; IWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { string fileExt = Path.GetExtension(strFileName); if (fileExt == ".xls") { hssfworkbook = new HSSFWorkbook(file); } else if (fileExt == ".xlsx") { hssfworkbook = new XSSFWorkbook(file); } else { return(new DataTable()); } } NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { NPOI.SS.UserModel.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); }
/// <summary> /// 导入Excel /// </summary> /// <param name="importPath">导入文件路径</param> /// <param name="headerIndex">表头所在行索引 (兼容第一行为大标题的情况)</param> public static DataSet ImportExcel(string importPath, int headerIndex = 0) { DataSet ds = new DataSet(); DataTable dt = null; FileStream fs = new FileStream(importPath, FileMode.Open, FileAccess.Read); IWorkbook book; if (Path.GetExtension(importPath) == "xls") { book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); } int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(headerIndex); //从第0行开始取 列头 if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = headerIndex + 1; i <= sheet.LastRowNum; i++) //从第1行开始取数据 { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { var cell = sheet.GetRow(i).GetCell(j); cell.SetCellType(CellType.String); newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } ds.Tables.Add(dt); } return(ds); }
public string ToString(int column) { var cell = XlsRow.GetCell(column); if (cell != null) { // TODO: you can add more cell types capatibility, e. g. formula switch (cell.CellType) { case NPOI.SS.UserModel.CellType.Numeric: return(cell.NumericCellValue.ToString()); case NPOI.SS.UserModel.CellType.String: return(cell.StringCellValue); } } return(null); }
private void button1_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); DataTable dt = null; OpenFileDialog sflg = new OpenFileDialog(); sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } ds.Tables.Add(dt); } }
/// <summary> /// Get a specific cell from a row. If the cell doesn't exist, /// </summary> /// <param name="row">The row that the cell is part of</param> /// <param name="column">The column index that the cell is in.</param> /// <returns>The cell indicated by the column.</returns> public static NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.IRow row, int column) { NPOI.SS.UserModel.ICell cell = row.GetCell(column); if (cell == null) { cell = row.CreateCell(column); } return(cell); }
/// <summary> /// 设置指定行列的单元格的背景色 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="color">颜色</param> private void setAlarm(int rowIndex, int columnIndex) { rowIndex--; columnIndex--; NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICell cell = null; //if (xlSheet.LastRowNum >= rowIndex) //{ if (xlSheet.GetRow(rowIndex) != null) { row = xlSheet.GetRow(rowIndex); } else { row = xlSheet.CreateRow(rowIndex); } //if (xlSheet.LastRowNum == 0) //{ // row = xlSheet.CreateRow(rowIndex); //} //else //{ // row = xlSheet.GetRow(rowIndex); //} //} //else //{ // row = xlSheet.CreateRow(rowIndex); //} if (row.GetCell(columnIndex) != null) { cell = row.GetCell(columnIndex); } else { cell = row.CreateCell(columnIndex); } ICellStyle style = xlBook.CreateCellStyle(); style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; style.FillPattern = FillPattern.SolidForeground; cell.CellStyle = style; }
/// <summary> /// 创建EXCEL文档 /// </summary> /// <param name="path">保存路径</param> /// <param name="dt">DataTable</param> public static void CreatExcel(string fullPath, DataTable dt) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "主题"; hssfworkbook.SummaryInformation = si; NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet("Sheet"); hssfSheet.DefaultColumnWidth = 18; NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; //表头 NPOI.SS.UserModel.IRow tagRow = hssfSheet.CreateRow(0); tagRow.RowStyle = cellStyle; for (int i = 0; i < dt.Columns.Count; i++) { tagRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); tagRow.GetCell(i).CellStyle = cellStyle; } int rowNum = 1; for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(rowNum); if (rowNum == 50001)//超过五万条数据,新建一张工作表 { hssfSheet = hssfworkbook.CreateSheet(); rowNum = 1; NPOI.SS.UserModel.IRow newrow = hssfSheet.CreateRow(0); row = hssfSheet.CreateRow(1); for (int j = 0; j < dt.Columns.Count; j++) { newrow.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName.ToString()); } } //写入记录 for (int j = 0; j < dt.Columns.Count; j++) { row.CreateCell(j).SetCellValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString()); } rowNum++; } FileStream file = new FileStream(fullPath, FileMode.Create); hssfworkbook.Write(file); file.Close(); }
/// <summary> /// 获取指定行列的数据 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <returns>该单元格的内容</returns> private string getData(int rowIndex, int columnIndex) { rowIndex--; columnIndex--; if (xlSheet.LastRowNum >= rowIndex) { NPOI.SS.UserModel.IRow row = null; if (xlSheet.LastRowNum == 0) { row = xlSheet.CreateRow(rowIndex); } else { row = xlSheet.GetRow(rowIndex); } if (row.LastCellNum >= columnIndex) { if (row.GetCell(columnIndex) != null) { return(row.GetCell(columnIndex).ToString()); } else { return(""); } } else { return(""); } } else { return(""); } }
/// <summary> /// 將excel數據導入到DataTable,第一行為標題行,不導入 /// </summary> /// <param name="path">excel路徑</param> /// <param name="actiontype">獲取表結構,在存儲過程Web_GetImportSchema中</param> /// <returns>不成功返回null</returns> public DataTable ToDataTable(string path, string actiontype) { if (File.Exists(path)) { DataTable dt = GetTableSchema(actiontype); if (dt == null) { return(null); } Dictionary <int, DataType> dic = GetDataTableColType(dt); int cols = dic.Count; using (FileStream fs = new FileStream(path, FileMode.Open)) { IWorkbook book = null; ISheet sheet = null; book = WorkbookFactory.Create(fs); sheet = book.GetSheetAt(0); if (sheet != null && sheet.LastRowNum > 0 && sheet.FirstRowNum >= 0 && sheet.LastRowNum != sheet.FirstRowNum) { int begin = sheet.FirstRowNum + 1, last = sheet.LastRowNum + 1; while (begin < last) { NPOI.SS.UserModel.IRow row = sheet.GetRow(begin); if (row == null) { break; } DataRow dr = dt.NewRow(); for (int i = 0; i < cols; i++) { AddColValue(dr, row.GetCell(i), i, dic[i]); } dt.Rows.Add(dr); begin += 1; } } } return(dt); } return(null); }
private static void createCells(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet, DataTable dt, int start, int end) { for (int i = 0; i < dt.Rows.Count; i++) { int colIndex = 0; NPOI.SS.UserModel.IRow row = sheet.GetRow(i + 2); if (row == null) { row = sheet.CreateRow(i + 2); } for (int j = start; j <= end; j++) { row.CreateCell(j).SetCellValue(Convert.ToInt32(dt.Rows[i][colIndex])); row.GetCell(j).CellStyle = GetCellStyle(book); colIndex++; } } }
/// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径,为物理路径。</param> /// <param name="sheetName">Excel工作表名称</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportDataTableFromExcel(string excelFilePath) { HSSFWorkbook hssfworkbook; //excelFilePath = excelFilePath.Replace("http://","").Replace("/","\\"); using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); for (int j = 0; j < 4; j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return(dt); } }
private static DataTable GetDataTableByXls(string excelFilePath, DataTable dt) { HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); int r = 0; while (rows.MoveNext()) { r++; if (r == 1) { continue; } NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current; DataRow rw = dt.NewRow(); for (int i = 1; i <= row.LastCellNum - 1; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { continue; } else { rw[i - 1] = cell.ToString(); } } dt.Rows.Add(rw); } return(dt); } }
/// <summary> /// NPOI 导出会议的管理人员 /// </summary> /// <param name="id"></param> /// <param name="ids"></param> public void ExportMeeting() { Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计"); //excel格式化 NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss"); NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle(); numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000"); NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle(); textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@"); //设置单元格宽度 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("姓名"); row1.CreateCell(1).SetCellValue("电话号码"); row1.CreateCell(2).SetCellValue("会议名称"); row1.CreateCell(3).SetCellValue("开始时间"); row1.CreateCell(4).SetCellValue("结束时间"); //设置列宽 row1.Sheet.SetColumnWidth(0, 100 * 50); row1.Sheet.SetColumnWidth(1, 100 * 50); row1.Sheet.SetColumnWidth(2, 150 * 50); row1.Sheet.SetColumnWidth(3, 100 * 50); row1.Sheet.SetColumnWidth(4, 100 * 50); MessasgeData mgdata = new MessasgeData(); mgdata = Datafun.MgfunctionData("select Bank,UserName,b.MeetingName,convert(nvarchar(11),Start_Date,120) ,convert(nvarchar(11),End_Date,120) from tb_login a left join tb_Meeting b on a.Meeting=b.id where Lvl=1 and a.isdel=1 "); Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "会议管理人员.xlsx")); //添加Excel名字 for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString()); rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString()); rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString()); rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString()); rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString()); rowtemp.GetCell(0).CellStyle = textStyle; rowtemp.GetCell(1).CellStyle = textStyle; rowtemp.GetCell(2).CellStyle = textStyle; rowtemp.GetCell(3).CellStyle = numberStyle; rowtemp.GetCell(4).CellStyle = textStyle; } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); Response.BinaryWrite(ms.ToArray()); Response.Flush(); Response.End(); }
/// <summary> /// NPOI 宴会统计导出 /// </summary> /// <param name="id"></param> /// <param name="ids"></param> public void Export(string id, string ids) { Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计"); //excel格式化 NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss"); NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle(); numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000"); NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle(); textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@"); //设置单元格宽度 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("姓名"); row1.CreateCell(1).SetCellValue("电话号码"); row1.CreateCell(2).SetCellValue("签到号"); row1.CreateCell(3).SetCellValue("红包"); row1.CreateCell(4).SetCellValue("礼物"); row1.CreateCell(5).SetCellValue("实际人数"); row1.CreateCell(6).SetCellValue("预计人数"); //设置列宽 row1.Sheet.SetColumnWidth(0, 80 * 50); row1.Sheet.SetColumnWidth(1, 80 * 50); row1.Sheet.SetColumnWidth(2, 50 * 50); row1.Sheet.SetColumnWidth(3, 80 * 50); row1.Sheet.SetColumnWidth(4, 150 * 50); row1.Sheet.SetColumnWidth(5, 80 * 50); row1.Sheet.SetColumnWidth(6, 80 * 50); MessasgeData mgdata = new MessasgeData(); SqlParameter[] pms = { new SqlParameter("@meeting", id), new SqlParameter("@ms", ids) }; mgdata = Datafun.MgfunctionData("select MeetingName from tb_Meeting where id=@meeting", pms); if (mgdata.Mgdatacount > 0) { Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "" + mgdata.Mgdata.Rows[0][0].ToString() + ".xlsx"));//添加Excel名字 } else { Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "宴会统计.xlsx"));//添加Excel名字 } mgdata = Datafun.MgfunctionData("Tj_rstj_poc", pms, "poc"); for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString()); rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString()); rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString()); rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString()); rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString()); rowtemp.CreateCell(5).SetCellValue(mgdata.Mgdata.Rows[i][5].ToString()); rowtemp.CreateCell(6).SetCellValue(mgdata.Mgdata.Rows[i][6].ToString()); rowtemp.GetCell(0).CellStyle = textStyle; rowtemp.GetCell(1).CellStyle = textStyle; rowtemp.GetCell(2).CellStyle = textStyle; rowtemp.GetCell(3).CellStyle = numberStyle; rowtemp.GetCell(4).CellStyle = textStyle; rowtemp.GetCell(5).CellStyle = textStyle; rowtemp.GetCell(6).CellStyle = numberStyle; } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); Response.BinaryWrite(ms.ToArray()); Response.Flush(); Response.End(); }
/// <summary> /// 读取excel到datatable中 /// </summary> /// <param name="excelPath">excel地址</param> /// <param name="sheetIndex">sheet索引</param> /// <returns>成功返回datatable,失败返回null</returns> public static DataTable ImportExcel(string excelPath, int sheetIndex) { IWorkbook workbook = null; //全局workbook ISheet sheet; //sheet DataTable table = null; try { FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在 if (fileInfo.Exists) { FileStream fileStream = fileInfo.OpenRead();//打开文件,得到文件流 switch (fileInfo.Extension) { //xls是03,用HSSFWorkbook打开,.xlsx是07或者10用XSSFWorkbook打开 case ".xls": workbook = new HSSFWorkbook(fileStream); break; case ".xlsx": workbook = new XSSFWorkbook(fileStream); break; default: break; } fileStream.Close();//关闭文件流 } if (workbook != null) { sheet = workbook.GetSheetAt(sheetIndex); //读取到指定的sheet table = new DataTable(); //初始化一个table IRow headerRow = sheet.GetRow(0); //获取第一行,一般为表头 int cellCount = headerRow.LastCellNum; //得到列数 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列 table.Columns.Add(column); } //遍历读取cell for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //得到一行 DataRow dataRow = table.NewRow(); //新建一个行 for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); //得到cell if (cell == null) //如果cell为null,则赋值为空 { dataRow[j] = string.Empty; } else { dataRow[j] = row.GetCell(j).ToString();//否则赋值 } } table.Rows.Add(dataRow);//把行 加入到table中 } } return(table); } catch (Exception) { return(table); } finally { //释放资源 if (table != null) { table.Dispose(); } workbook = null; sheet = null; } }
/// <summary> /// 数据导出到excel /// </summary> /// <param name="tempName">模板名称</param> /// <param name="fileName">文件名称</param> /// <param name="dsSheet">包含工作表数据的数据集</param> private void WriteToExcel(string tempName, string fileName, DataSet dsSheet) { IWorkbook book = null; string _filePath = Server.MapPath(_exportPath) + tempName; using (FileStream fs = File.Open(_filePath, FileMode.Open, FileAccess.ReadWrite)) { book = WorkbookFactory.Create(fs); ISheet wSheet; //define worksheet ICellStyle cStyle = book.CreateCellStyle(); //define style ////HSSFDataFormat.GetBuiltinFormat("###0.00");//(short)CellType.NUMERIC; IDataFormat dataformat = book.CreateDataFormat(); cStyle.DataFormat = 194;//数字格式 ICellStyle cStyleDate = book.CreateCellStyle(); cStyleDate.DataFormat = dataformat.GetFormat("yyyy-MM-dd"); // ICellStyle cStyleTime = book.CreateCellStyle(); cStyleTime.DataFormat = dataformat.GetFormat("hh:mm"); // foreach (DataTable dt in dsSheet.Tables) { wSheet = book.GetSheet(dt.TableName);//打开指定的工作表 #region f填充数据 NPOI.SS.UserModel.IRow rowTitle = wSheet.GetRow(0); NPOI.SS.UserModel.IRow rowtemp; DataColumn dc; for (int i = 0; i < dt.Rows.Count; i++) { rowtemp = wSheet.GetRow(i + 1);//先获取现有的行,如果为空,则创建 if (rowtemp == null) { rowtemp = wSheet.CreateRow(i + 1); } ICell rowCell; string headTitle; for (int j = 0; j < rowTitle.LastCellNum; j++) { if (rowTitle.GetCell(j) == null) { break; } headTitle = rowTitle.GetCell(j).ToString(); if (!dt.Columns.Contains(headTitle)) { continue; } dc = dt.Columns[headTitle]; rowCell = rowtemp.GetCell(j); if (rowCell == null) { rowCell = rowtemp.CreateCell(j); } if ((dc.DataType.Name == "Decimal" || dc.DataType.Name.Substring(0, 3) == "Int") && !dt.Rows[i].IsNull(dc.ColumnName)) { double during = double.Parse(dt.Rows[i][dc.ColumnName].ToString()); rowCell.SetCellType(CellType.Numeric); rowCell.SetCellValue(during); rowCell.CellStyle = cStyle; } else if (dc.DataType.Name == "DateTime" && !dt.Rows[i].IsNull(dc.ColumnName)) { rowCell.SetCellValue((DateTime)dt.Rows[i][dc.ColumnName]); rowCell.CellStyle = cStyleDate; } else if (dc.DataType.Name == "TimeSpan" && !dt.Rows[i].IsNull(dc.ColumnName)) { rowCell.SetCellValue(DateTime.Today.Date + (TimeSpan)dt.Rows[i][dc.ColumnName]); rowCell.CellStyle = cStyleTime; } else if (!dt.Rows[i].IsNull(dc.ColumnName)) { rowCell.SetCellValue(dt.Rows[i][dc.ColumnName].ToString()); } } } //调整列宽 for (int j = 0; j < rowTitle.LastCellNum; j++) { wSheet.AutoSizeColumn(j); } #endregion } } //保存到本地文件 _filePath = Server.MapPath(_exportPath) + fileName; using (FileStream fs1 = File.Open(_filePath, FileMode.Create, FileAccess.ReadWrite)) { book.Write(fs1); } book = null; divSaveAs.InnerHtml = "<font color='black'>Excel文件已生成,请</font> <a href='" + _exportPath + fileName + "'>单击下载</a> "; }
DataSet ReadExcel(string fullfileName) { DataSet ds = new DataSet(); DataTable dt = null; FileStream fs = new FileStream(fullfileName, FileMode.Open, FileAccess.Read); //NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); IWorkbook book = NPOI.SS.UserModel.WorkbookFactory.Create(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) { continue; } NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) { continue; } int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) { continue; } dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { if (row.GetCell(i) == null) { continue; } var colHeader = row.GetCell(i).StringCellValue; int index = 1; while (dt.Columns.Contains(colHeader)) { colHeader += index; index++; } dt.Columns.Add(colHeader, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { if (sheet.GetRow(i) == null) { continue; } if (sheet.GetRow(i).GetCell(j) == null) { continue; } ICell cell = sheet.GetRow(i).GetCell(j); if (cell.CellType == CellType.String) { newRow[j] = cell.StringCellValue; } else if (cell.CellType == CellType.Numeric) { newRow[j] = cell.NumericCellValue; } else if (cell.CellType == CellType.Blank) { newRow[j] = DBNull.Value; } else { throw new Finance.Utils.FinanceException(FinanceResult.NOT_SUPPORT); } } } ds.Tables.Add(dt); } return(ds); }
private string GenerateExcelReport(int report, string savePath) { /// excel writer row index int rowIndex = 0; try { NPOI.XSSF.UserModel.XSSFWorkbook workbook = new XSSFWorkbook(); NPOI.SS.UserModel.ISheet wkst = workbook.CreateSheet(); workbook.SetActiveSheet(0); // sets up basic styles for Excel NPOI.SS.UserModel.ICellStyle HeaderCellStyle = workbook.CreateCellStyle(); HeaderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.VerticalAlignment = VerticalAlignment.Center; HeaderCellStyle.Alignment = HorizontalAlignment.Center; HeaderCellStyle.FillPattern = FillPattern.SolidForeground; HeaderCellStyle.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index; IFont headerFont = workbook.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; HeaderCellStyle.SetFont(headerFont); NPOI.SS.UserModel.ICellStyle DataCellStyle = workbook.CreateCellStyle(); DataCellStyle.VerticalAlignment = VerticalAlignment.Top; DataCellStyle.WrapText = true; ArrayList query_for_status = new ArrayList(); string query_columns = string.Empty, ignore_columns = "id,cohort_id,status_timestamp,published", column_headers = string.Empty; switch (report) { case 0: query_columns = "id,cohort_id,cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp"; query_for_status.Add("pending"); query_for_status.Add("rejected"); query_for_status.Add("inprogress"); ignore_columns = "id,cohort_id"; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Received,CEDCD Form Reviewed,Date CEDCD Form Published"; break; case 1: query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp"; query_for_status.Add("pending"); query_for_status.Add("rejected"); query_for_status.Add("inprogress"); ignore_columns = string.Empty; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Current Status,Current Status Date"; break; case 2: query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp"; query_for_status.Add("published"); ignore_columns = string.Empty; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Published"; break; case 3: query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp [Date CEDCD Form was Unpublished]"; query_for_status.Add("unpublished"); ignore_columns = string.Empty; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Unpublished"; break; case 4: query_columns = "*"; query_for_status.Add("published"); query_for_status.Add("pending"); break; default: query_columns = "*"; query_for_status.Add("published"); break; } DataTable dt_records = CECWebSrv.GetCohortsWithStatusesWithColumns(UserToken, (string[])query_for_status.ToArray(typeof(string)), query_columns); /// write header to excel /// NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++); //dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority)); //dataRow = wkst.CreateRow(rowIndex++); //dataRow.CreateCell(0).SetCellValue("Export Date:"); //dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy")); //rowIndex += 2; int colPos = 0; ///-------------------------------------------------------- /// column headers /// NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++); if (column_headers != string.Empty) { foreach (string s in column_headers.Split(',')) { ICell c = headerRow.GetCell(colPos++, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(s); c.CellStyle = HeaderCellStyle; } } else { using (CECHarmPublicService ps = new CECHarmPublicService()) { for (int _c = 0; _c < dt_records.Columns.Count; _c++) { if (ignore_columns.Contains(dt_records.Columns[_c].ColumnName)) { continue; } ICell c = headerRow.GetCell(colPos); if (c == null) { c = headerRow.CreateCell(colPos); } string column_name = ps.GetCohortWebFieldLabelByColumnName(UserToken, dt_records.Columns[_c].ColumnName); if (String.IsNullOrWhiteSpace(column_name)) { column_name = dt_records.Columns[_c].ColumnName; } c.SetCellValue(column_name); c.CellStyle = HeaderCellStyle; colPos++; } } } if (report != 0 && dt_records.Rows.Count >= 1) { /// data rows for (int _i = 0; _i < dt_records.Rows.Count; _i++) { colPos = 0; /// create data row object then step through each cell to populate the excel row dataRow = wkst.CreateRow(rowIndex++); for (int _p = 0; _p < dt_records.Columns.Count; _p++) { if (ignore_columns.Contains(dt_records.Columns[_p].ColumnName)) { continue; } /// get first cell and check for null, if null create cell ICell c = dataRow.GetCell(colPos); if (c == null) { c = dataRow.CreateCell(colPos); } string cellVal = dt_records.Rows[_i][_p].ToString(); if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1") { cellVal = "N/P"; c.SetCellValue(cellVal); } else if (dt_records.Columns[_p].DataType == typeof(DateTime)) { c.SetCellValue(DateTime.Parse(cellVal).ToString("MM/dd/yyyy")); } else if (dt_records.Columns[_p].ColumnName == "status") { switch (cellVal) { case "inprogress": c.SetCellValue("Draft In Progress"); break; case "pending": c.SetCellValue("Under NCI Review"); break; case "rejected": c.SetCellValue("Returned to Cohort"); break; default: c.SetCellValue(cellVal); break; } } else { c.SetCellValue(cellVal); } c.CellStyle = DataCellStyle; colPos++; } } } else if (report == 0 && dt_records.Rows.Count >= 1) { foreach (DataRow dr in dt_records.Rows) { dataRow = wkst.CreateRow(rowIndex++); // cohort acronym ICell c = dataRow.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["cohort_acronym"].ToString()); c.CellStyle = DataCellStyle; // cohort name c = dataRow.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["cohort_name"].ToString()); c.CellStyle = DataCellStyle; // pi name c = dataRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["pi_name_1"].ToString()); c.CellStyle = DataCellStyle; // pi institution c = dataRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["pi_institution_1"].ToString()); c.CellStyle = DataCellStyle; // pi email c = dataRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["pi_email_1"].ToString()); c.CellStyle = DataCellStyle; // the more complicated stuff... if (dr["status"].ToString() == "pending") { c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy")); c.CellStyle = DataCellStyle; c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue("No"); c.CellStyle = DataCellStyle; using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false)) { if (dt_temp.Rows.Count > 0) { c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy")); c.CellStyle = DataCellStyle; } } } else if (dr["status"].ToString() == "rejected" || dr["status"].ToString() == "inprogress") { c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK); if (dr["status"].ToString() == "rejected") { using (DataTable dt_temp = (new CECHarmPublicService()).AuditLog_GetActivities(UserToken, "submitted", (int)dr["id"])) { if (dt_temp.Rows.Count == 0) { c.SetCellValue(" "); } else { c.SetCellValue(((DateTime)dt_temp.Rows[0]["create_date"]).ToString("MM/dd/yyyy")); } } } else { c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy")); } c.CellStyle = DataCellStyle; c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue("Pending Revisions"); c.CellStyle = DataCellStyle; using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false)) { if (dt_temp.Rows.Count > 0) { c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy")); c.CellStyle = DataCellStyle; } } } } } else { ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(String.Format("Nothing to report")); } for (int _ic = 0; _ic <= headerRow.PhysicalNumberOfCells; _ic++) { wkst.AutoSizeColumn(_ic); } /// write output FileStream fs = new FileStream(savePath, FileMode.Create); workbook.Write(fs); fs.Close(); return(savePath); } catch (Exception ex) { throw ex; } }
/// <summary> /// 导入excel /// </summary> /// <param name="file"></param> /// <returns></returns> public ActionResult ImportExcel(HttpPostedFileBase file) { try { //思路:获取读取的文件,把文件转换为二进制数组,然后转成内存流,利用NPOI把内存流中的数据读取成Excel Session.Remove("ImportExcel"); //把session中的ImportExcel移除避免残留以前数据 string fileExtension = System.IO.Path.GetExtension(file.FileName); //读取路径文件的扩展名 if (".xls".Equals(fileExtension) || ".XLS".Equals(fileExtension)) //判断读取的文件是.xls文件 { byte[] fileBytes = new byte[file.ContentLength]; //指定数组的长度获取Excel数据的大小 file.InputStream.Read(fileBytes, 0, file.ContentLength); //读取文件内容 // 转为 内存流 System.IO.MemoryStream excelFileStream = new System.IO.MemoryStream(fileBytes); //将内存流转为 工作簿 NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream); //判断工作簿中的工作表(Sheet)的个数 if (workbook.NumberOfSheets > 0) { //查询出 学院,专业,年级,班级 的信息:目的是用来查看导入的数据是否有重复 List <PatientVo> lsitStudentVos = new List <PatientVo>(); // 获取第一个工作表 NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); //PhysicalNumberOfRows 获取的是物理行数,也就是不包括那些空行(隔行)的情况。 //判断 工作表(sheet)中有数据 if (sheet.PhysicalNumberOfRows > 0) { //将数据先装到datatable中 // 定义datatable DataTable dtExcel = new DataTable(); //获取标题行 第一行 NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); //获取一行单元格个数 LastCellNum 获取列数,比最后一列列标大 1 int cellCount = headerRow.LastCellNum; //获取数据总行数 LastRowNum 最后一行行标,比行数小 1 int rowCount = sheet.LastRowNum + 1; //创建DataTable的列Columns for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); dtExcel.Columns.Add(column); } //读取Excel中的数据 //(sheet.FirstRowNum) 第一行是标题 for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //获取行 DataRow dataRow = dtExcel.NewRow(); //DataTable创建一行 if (row != null) { //遍历Excel一行的所有单元格 for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } } //添加行DataRow到DataTable dtExcel.Rows.Add(dataRow); } //遍历datatable 获取数据 foreach (DataRow row in dtExcel.Rows) { //创建一个 StudentVo的对象 PatientVo student = new PatientVo(); try { //获取性别ID var dd = row["性别"].ToString().Trim(); var SexID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == dd select tbSex.BaseDetailID).Single(); student.sexID = Convert.ToInt32(SexID); student.Sex = row["性别"].ToString().Trim(); //病人所属ID var PatientOwnership = row["病人所属"].ToString().Trim(); var cantonID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == PatientOwnership select tbSex.BaseDetailID).Single(); student.cantonID = Convert.ToInt32(cantonID); student.PatientOwnership = row["病人所属"].ToString().Trim(); //获取人群分类id和名称 var CrowdClass = row["人群分类"].ToString().Trim(); var CrowdclassificationID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CrowdClass select tbSex.BaseDetailID).Single(); student.CrowdclassificationID = Convert.ToInt32(CrowdclassificationID); student.CrowdClass = row["人群分类"].ToString().Trim(); //获取病例分类ID和名称 var CaseClass = row["病例分类"].ToString().Trim(); var CaseClassID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CaseClass select tbSex.BaseDetailID).Single(); student.CaseClassID = Convert.ToInt32(CaseClassID); student.CaseClass = row["病例分类"].ToString().Trim(); //获取病例名称ID和名称 var DiseaseType = row["疾病名称类型"].ToString().Trim(); var DiseaseTypeID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == DiseaseType select tbSex.BaseDetailID).Single(); student.DiseaseTypeID = Convert.ToInt32(DiseaseTypeID); student.DiseaseType = row["疾病名称类型"].ToString().Trim(); //获取审核状态ID和名称 var AuditStatu = row["审核状态"].ToString().Trim(); var AuditStatusID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == AuditStatu select tbSex.BaseDetailID).Single(); student.AuditStatusID = Convert.ToInt32(AuditStatusID); student.AuditStatus = row["审核状态"].ToString().Trim(); //给创建实体赋值 student.Cardnumber = row["卡片编号"].ToString().Trim(); student.name = row["患者姓名"].ToString().Trim(); student.IDnumber = row["身份证号码"].ToString().Trim(); student.BirthDateTime = row["出生日期"].ToString().Trim(); student.workunit = row["工作单位"].ToString().Trim(); student.Addressnationalstandard = row["现详细住址国标"].ToString().Trim(); student.Detailedaddress = row["现住详细地址"].ToString().Trim(); student.CensusAddressInter = row["户籍地址国标"].ToString().Trim(); student.CensusAddDetail = row["户籍地址详细"].ToString().Trim(); student.MorbidityDateTime = row["发病日期"].ToString().Trim(); student.DiagnoseDateTime = row["诊断日期"].ToString().Trim(); student.ReportDateTime = row["录入日期"].ToString().Trim(); student.RevisedDateTime = row["订正终审日期"].ToString().Trim(); student.Occupation = row["职业"].ToString().Trim(); student.Remark = row["备注"].ToString().Trim(); lsitStudentVos.Add(student); } catch (Exception e) { Console.WriteLine(e); } } //把数据存在session当中做临时保存,这里还没有保存到数据库 Session["ImportExcel"] = lsitStudentVos; return(Json(true, JsonRequestBehavior.AllowGet)); } else { //物理行数为0 } } else { //没有工作表 } } else { //上传的文件类型不正确 } } catch (Exception e) { Console.WriteLine(e); } return(Json(false, JsonRequestBehavior.AllowGet)); }
public static DataTable ImportExcel(MemoryStream ms) { IWorkbook workbook = null; //全局workbook ISheet sheet; //sheet DataTable table = null; try { workbook = new HSSFWorkbook(ms); if (workbook != null) { sheet = workbook.GetSheetAt(0); //读取到指定的sheet table = new DataTable(); //初始化一个table IRow headerRow = sheet.GetRow(0); //获取第一行,一般为表头 int cellCount = headerRow.LastCellNum; //得到列数 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列 table.Columns.Add(column); } //遍历读取cell for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //得到一行 DataRow dataRow = table.NewRow(); //新建一个行 for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); //得到cell if (cell == null) //如果cell为null,则赋值为空 { dataRow[j] = ""; } else { dataRow[j] = row.GetCell(j).ToString();//否则赋值 } } table.Rows.Add(dataRow);//把行 加入到table中 } } return(table); } catch (Exception e) { return(table); } finally { //释放资源 if (table != null) { table.Dispose(); } workbook = null; sheet = null; } }
private string ExportDataGridToExcel(DataTable toExport, string savePath) { /// excel writer row index int rowIndex = 0; try { CECHarmPublicService ps = new CECHarmPublicService(); NPOI.XSSF.UserModel.XSSFWorkbook wkbk = new XSSFWorkbook(); NPOI.SS.UserModel.ISheet wkst = wkbk.CreateSheet(); wkbk.SetActiveSheet(0); /// write header to excel /// NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++); dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority)); dataRow = wkst.CreateRow(rowIndex++); dataRow.CreateCell(0).SetCellValue("Table Name:"); dataRow.CreateCell(1).SetCellValue("Cancer Counts"); dataRow = wkst.CreateRow(rowIndex++); dataRow.CreateCell(0).SetCellValue("Export Date:"); dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy")); // gender selections string tAss = string.Empty; dataRow = wkst.CreateRow(rowIndex++); dataRow.CreateCell(0).SetCellValue("Selected Gender(s):"); foreach (string s in Genders) { tAss += String.Format("{0}, ", s); } tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' }))); dataRow.CreateCell(1).SetCellValue(tAss); // cancer category tAss = string.Empty; //dataRow = wkst.CreateRow(rowIndex++); //dataRow.CreateCell(0).SetCellValue("Cancer Category:"); //dataRow.CreateCell(1).SetCellValue((new CultureInfo("en-US").TextInfo.ToTitleCase(CancerCategory))); // cancer selections tAss = string.Empty; dataRow = wkst.CreateRow(rowIndex++); dataRow.CreateCell(0).SetCellValue("Selected Cancer Type(s):"); foreach (string c in CancerTypes) { tAss += String.Format("{0}, ", select_cancer.GetCancerLabel(c)); } tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' }))); dataRow.CreateCell(1).SetCellValue(tAss); // cohort selections tAss = string.Empty; dataRow = wkst.CreateRow(rowIndex++); dataRow.CreateCell(0).SetCellValue("Selected Cohort(s):"); foreach (string s in CohortIDsToCompare) { tAss += String.Format("{0}, ", select_cohort.GetCohortAcronym(int.Parse(s))); } tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' }))); dataRow.CreateCell(1).SetCellValue(tAss); rowIndex += 2; ///-------------------------------------------------------- /// column headers /// NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++); for (int _c = 0; _c < toExport.Columns.Count; _c++) { ICell c = headerRow.GetCell(_c); if (c == null) { c = headerRow.CreateCell(_c); } c.SetCellValue(toExport.Columns[_c].ColumnName); } if (toExport.Rows.Count > 1) { /// data rows for (int _i = 0; _i < toExport.Rows.Count; _i++) { int colPos = 0; /// create data row object then step through each cell to populate the excel row dataRow = wkst.CreateRow(rowIndex++); for (int _p = 0; _p < toExport.Columns.Count; _p++) { /// get first cell and check for null, if null create cell ICell c = dataRow.GetCell(colPos); if (c == null) { c = dataRow.CreateCell(colPos); } string cellVal = toExport.Rows[_i][_p].ToString(); /// position 0 is cancer type, position 1 is male/female if (_p == 0) { c.SetCellValue(select_cancer.GetCancerLabel(cellVal)); } else if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1") { /// --------------------------------------- /// was N/P, per Amy 23-April, 0 is preferred /// per Amy, 4-May, 0 should appear if the cohort has incident cancers--even for prevelant; /// show N/P if incident and prevelant is not provided if (String.IsNullOrWhiteSpace(cellVal) || cellVal == " ") { //if (CECWebSrv.CohortHasCancerIndicator(UserToken, toExport.Columns[_p].ColumnName)) cellVal = "0"; //else // cellVal = "N/P"; } else { cellVal = "N/P"; } c.SetCellValue(cellVal); } else { c.SetCellValue(cellVal); } colPos++; } } } else { ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(String.Format("None of the select cohorts reported any 'todo' cancers")); } /// write output FileStream fs = new FileStream(savePath, FileMode.Create); wkbk.Write(fs); fs.Close(); return(savePath); } catch (Exception ex) { throw ex; } }
/// <summary> /// 外观检查日报Excel /// </summary> /// <param name="ds"></param> /// <param name="ReportHeader"></param> /// <returns></returns> public static MemoryStream ExportXlsByList(DataSet ds, string ReportHeader = "", string userName = "") { //创建Excel文件的对象 HSSFWorkbook book = new HSSFWorkbook(); //添加一个sheet HSSFSheet sheet1 = (HSSFSheet)book.CreateSheet(ReportHeader); sheet1.DefaultRowHeight = 150 * 4;//设置全局行高 //设置列宽度 for (int i = 0; i < ds.Tables[0].Columns.Count + 19; i++) { if (i == 1 || i == 0 || i == 28)//第一、二列 { sheet1.SetColumnWidth(i, 19 * 250); } else if (i > 1 && i < 7) { sheet1.SetColumnWidth(i, 10 * 256); } else { sheet1.SetColumnWidth(i, 6 * 256); } } //字体 IFont fontkh = book.CreateFont(); //创建一个字体样式 fontkh.FontHeightInPoints = 12; //字体大小 fontkh.FontName = "宋体"; //字体名 //显示数据字体 IFont fontdata = book.CreateFont(); //创建一个字体样式 fontdata.FontHeightInPoints = 11; //字体大小 fontdata.FontName = "宋体"; //字体名 //显示数据字体 IFont fontdata1 = book.CreateFont(); //创建一个字体样式 fontdata1.FontHeightInPoints = 11; //字体大小 fontdata1.Color = (short)ConsoleColor.Green; fontdata1.FontName = "宋体"; //字体名 //字符样式 HSSFFont row2stylefont = (HSSFFont)book.CreateFont(); row2stylefont.FontName = "宋体"; row2stylefont.FontHeightInPoints = 25; row2stylefont.IsBold = true; //显示数据格式 ICellStyle styledata = book.CreateCellStyle();//创建显示数据格式 styledata.WrapText = true; styledata.VerticalAlignment = VerticalAlignment.Center; //上下居中 styledata.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 styledata.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 styledata.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 styledata.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 styledata.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 styledata.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 styledata.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 styledata.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 styledata.SetFont(fontdata); ICellStyle styledata1 = book.CreateCellStyle(); //创建显示数据格式 styledata1.Alignment = HorizontalAlignment.Center; //左对齐 styledata1.WrapText = true; styledata1.VerticalAlignment = VerticalAlignment.Center; //上下居中 styledata1.SetFont(fontdata1); //添加考核标题样式 //单元格样式 ICellStyle stylekh = book.CreateCellStyle(); stylekh.Alignment = HorizontalAlignment.Left; //左对齐 stylekh.WrapText = true; //自动换行 stylekh.FillForegroundColor = HSSFColor.PaleBlue.Index; //前景色 stylekh.FillPattern = FillPattern.SolidForeground; //填充样式 stylekh.VerticalAlignment = VerticalAlignment.Center; //上下居中 stylekh.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh.SetFont(fontkh); ICellStyle stylekh1 = book.CreateCellStyle(); stylekh1.Alignment = HorizontalAlignment.Left; //左对齐 stylekh1.WrapText = true; //自动换行 stylekh1.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index; //前景色 stylekh1.FillPattern = FillPattern.SolidForeground; //填充样式 stylekh1.VerticalAlignment = VerticalAlignment.Center; //上下居中 stylekh1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框 stylekh1.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh1.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh1.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh1.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色 stylekh1.SetFont(fontkh); //第一行单元格样式 外观检测日报 ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.WrapText = true; IFont font = book.CreateFont(); font.FontHeightInPoints = 20;//字体大小 font.Boldweight = (short)FontBoldWeight.Bold; font.FontName = "宋体"; style.SetFont(font); //给第一行填数据 HSSFRow row2 = (HSSFRow)sheet1.CreateRow(0); row2.CreateCell(0).SetCellValue("外观检查日报"); row2.GetCell(0).CellStyle = style; //显示考核信息 HSSFRow row3 = (HSSFRow)sheet1.CreateRow(2); row3.CreateCell(0).SetCellValue("考核信息"); row3.GetCell(0).CellStyle = stylekh; //第一行设置合并单元格 //参数分别为 自左到右 开始行索引,结束行索引,开始列索引,结束列索引 CellRangeAddress firstcellRange = new CellRangeAddress(0, 1, 0, ds.Tables[0].Columns.Count / 3); CellRangeAddress nextcellRange = new CellRangeAddress(0, 1, ds.Tables[0].Columns.Count / 3, ds.Tables[0].Columns.Count + 10); sheet1.AddMergedRegion(firstcellRange); sheet1.AddMergedRegion(nextcellRange); //空出一行 CellRangeAddress khInfo1 = new CellRangeAddress(2, 2, 1, ds.Tables[0].Columns.Count + 10);//显示"考核信息" sheet1.AddMergedRegion(khInfo1); CellRangeAddress kb = new CellRangeAddress(5, 5, 0, ds.Tables[0].Columns.Count + 10);//第5行空白 sheet1.AddMergedRegion(kb); //备注合并单元格 CellRangeAddress Remark = new CellRangeAddress(3, 3, 7, 13); //合并第三行第8列到第9列 CellRangeAddress Remark1 = new CellRangeAddress(4, 4, 7, 13); //合并第四行第8列到第9列 sheet1.AddMergedRegion(Remark); sheet1.AddMergedRegion(Remark1); //给sheet1添加第一行的头部标题 HSSFRow row1 = (HSSFRow)sheet1.CreateRow(3); HSSFRow tworow = (HSSFRow)sheet1.CreateRow(6); //第7行 int twonum = 0; //列标题的索引号 string mColumnName = ""; //列标题的名称 string[] strs = new string[] { "料伤", "夹伤", "划伤", "打伤", "磨伤", "轴承不过", "倒角不良", "抛光变形", "扁错位", "刀痕", "孔径不良", "尾刺", "粗糙度", "电镀不良", "生锈", "漏工程", "料头", "跳动不良", "长度不良", "加工错误", "其他" };//数组存储不良类型,用来构造列头 //列标题 for (int k = 0; k < ds.Tables[0].Columns.Count; k++) { //考核信息标题 mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString(); if (k < 8) { row1.CreateCell(k).SetCellValue(mColumnName); row1.GetCell(k).CellStyle = stylekh; } else { if (k > ds.Tables[0].Columns.Count - 4) { if (k == 15) { tworow.CreateCell(strs.Length + 7).SetCellValue(mColumnName); tworow.GetCell(strs.Length + 7).CellStyle = stylekh1; } //此处是不良类型的标题,隐去了不良类型与不良数,利用生产的数组代替 if (k == (ds.Tables[0].Columns.Count - 1)) { for (int i = 0; i < strs.Length; i++) { tworow.CreateCell(twonum).SetCellValue(strs[i]); tworow.GetCell(twonum).CellStyle = stylekh1; twonum++; } } } else { tworow.CreateCell(twonum).SetCellValue(k == 11 ? mColumnName + "/H" : mColumnName); //因为数据库里部能包好标准时/H 在此重构一下 tworow.GetCell(twonum).CellStyle = k == 15 ? stylekh1 : stylekh; twonum++; } } } ////显示数据 按行填充 行数从0开始 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(4);//第5行 ICellStyle makeStyle = book.CreateCellStyle(); makeStyle.Alignment = HorizontalAlignment.Left; makeStyle.VerticalAlignment = VerticalAlignment.Center; IFont makeFont = book.CreateFont(); makeFont.FontName = "宋体"; makeFont.FontHeightInPoints = 16; makeStyle.SetFont(makeFont); row1.CreateCell(15).SetCellValue("作成: " + userName + " 时间:" + DateTime.Now.ToString()); row1.GetCell(15).CellStyle = makeStyle; rowtemp.CreateCell(15).SetCellValue("说明:达成率=(标准工时+其他工时)/出勤工时*100% 标准时间=∑(检查数量/标准时间)"); //考核信息部分 j为行索引 这里只遍历前八个 for (int j = 0; j < 8; j++) { if (j == 0) { //第一个日期转化格式 rowtemp.CreateCell(j).SetCellValue(DateTime.Parse(ds.Tables[0].Rows[0][j].ToString()).ToString("yyyy年MM月dd日")); } else if (j > 1 && j < 7) { if (j == 6) { //达成率加百分比 rowtemp.CreateCell(j).SetCellValue(ds.Tables[0].Rows[0][j].ToString() + "%"); } else { //工号、出勤时间等已双精度输出 rowtemp.CreateCell(j).SetCellValue(double.Parse(ds.Tables[0].Rows[0][j].ToString())); } } else { //备注姓名等 rowtemp.CreateCell(j).SetCellValue(ds.Tables[0].Rows[0][j].ToString()); } //样式填充 if (j != 7) { rowtemp.GetCell(j).CellStyle = styledata; } } int SumCheck = 0; int okCheck = 0; int NgCheck = 0; int asd; //第7行开始遍历导出数据 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { HSSFRow rowtemp1 = (HSSFRow)sheet1.CreateRow(i + 7); for (int j = 7; j < ds.Tables[0].Columns.Count - 1; j++) { if (j > ds.Tables[0].Columns.Count - 4 && ((ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1]) == null ? false : true)) { string[] types = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 2].ToString().Split(','); string[] cts = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1].ToString().Split(','); if (types.Length > 1 && cts.Length > 1) { for (int cs = 0; cs < cts.Length; cs++) { rowtemp1.CreateCell(j - 8 + int.Parse(types[cs])).SetCellValue(int.Parse(cts[cs])); rowtemp1.GetCell(j - 8 + int.Parse(types[cs])).CellStyle = styledata1; } } } else { int.TryParse(ds.Tables[0].Rows[i][j].ToString(), out asd); if (asd > 0 || j == 14) { if (j == 12) { SumCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString()); } if (j == 13) { okCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString()); } if (j == 14) { NgCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString()); } rowtemp1.CreateCell(j - 8).SetCellValue(int.Parse(ds.Tables[0].Rows[i][j].ToString())); rowtemp1.GetCell(j - 8).CellStyle = styledata; } else if (j == 7) { rowtemp1.CreateCell(strs.Length + j).SetCellValue(ds.Tables[0].Rows[i][j].ToString()); } else { rowtemp1.CreateCell(j - 8).SetCellValue(ds.Tables[0].Rows[i][j].ToString()); rowtemp1.GetCell(j - 8).CellStyle = styledata; } } } } HSSFRow rowtemp2 = (HSSFRow)sheet1.CreateRow(ds.Tables[0].Rows.Count + 7); rowtemp2.CreateCell(3).SetCellValue("total:"); rowtemp2.GetCell(3).CellStyle = styledata; rowtemp2.CreateCell(4).SetCellValue(SumCheck); rowtemp2.GetCell(4).CellStyle = styledata; rowtemp2.CreateCell(5).SetCellValue(okCheck); rowtemp2.GetCell(5).CellStyle = styledata; rowtemp2.CreateCell(6).SetCellValue(NgCheck); rowtemp2.GetCell(6).CellStyle = styledata; //写入流 System.IO.MemoryStream ms = new MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(ms); }
/// <summary> /// 导入excel文件 /// </summary> /// <param name="filePath"></param> /// <returns></returns> private static string ImportExcel(string filePath) { string result = ""; NPOI.SS.UserModel.ISheet sheet = null; try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); } } catch (Exception) { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook2 = new NPOI.XSSF.UserModel.XSSFWorkbook(file); sheet = hssfworkbook2.GetSheetAt(0); } } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } if (row.LastCellNum != -1) { if ((dr[0] == null | dr[0].ToString() == "") && (dr[1] == null | dr[1].ToString() == "") && (dr[2] == null | dr[2].ToString() == "")) { } else { dt.Rows.Add(dr); } } } dt = Distinct(dt, new string[] { "A", "B", "C" }); DataView myDataView = new DataView(dt); string[] strComuns = { "C" }; if (myDataView.ToTable(true, strComuns).Rows.Count < dt.Rows.Count) { result = "用户表存在相同的账号数据"; return(result); } DbHelper dp = new DbHelper(); foreach (DataRow dr in dt.Rows) { string strsql = "insert into hs_user1 values ('" + dr["B"] + "','" + dr["C"] + "')"; dp.RunTxt(strsql); } result = "导入成功"; return(result); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null) { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } #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 NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); NPOI.SS.UserModel.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(); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } } #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText) == false) { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); rowIndex += 1; } //headerRow.Dispose(); } #endregion #region 列头及样式 { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.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); } //headerRow.Dispose(); rowIndex += 1; } #endregion } #endregion #region 填充内容 NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { NPOI.SS.UserModel.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; // sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// 将DataTable导出到Excel /// </summary> /// <param name="dt">DataTable</param> /// <param name="fileName">仅文件名(非路径)</param> /// <returns>返回Excel文件绝对路径</returns> public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response) { #region 表头 HSSFWorkbook hssfworkbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName); hssfSheet.DefaultColumnWidth = 13; hssfSheet.SetColumnWidth(0, 25 * 256); hssfSheet.SetColumnWidth(3, 20 * 256); // 表头 NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0); tagRow0.Height = 40 * 40; ICell cell0 = tagRow0.CreateCell(0); //设置单元格内容 cell0.SetCellValue("力诺瑞特制造工厂"); hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7)); NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1); tagRow1.Height = 20 * 20; ICell cell1 = tagRow1.CreateCell(0); //设置单元格内容 cell1.SetCellValue("计划外领料单"); hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7)); NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle(); tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle.VerticalAlignment = VerticalAlignment.Center; //tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; //tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 16; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "宋体"; tagStyle.SetFont(font);//HEAD 样式 cell0.CellStyle = tagStyle; NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle(); tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; tagStyle1.VerticalAlignment = VerticalAlignment.Center; cell1.CellStyle = tagStyle1; // 标题样式 NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; #endregion #region 表数据 // 表数据 for (int k = 0; k < 2; k++) { DataRow dr = dt.Rows[k]; NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2); row.Height = 30 * 20; for (int i = 0; i < dt.Columns.Count; i += 2) { row.CreateCell(i).SetCellValue(dr[i / 2].ToString()); row.GetCell(i).CellStyle = cellStyle; } row.CreateCell(1).SetCellValue(""); row.GetCell(1).CellStyle = cellStyle; row.CreateCell(3).SetCellValue(""); row.GetCell(3).CellStyle = cellStyle; row.CreateCell(5).SetCellValue(""); row.GetCell(5).CellStyle = cellStyle; row.CreateCell(7).SetCellValue(""); row.GetCell(7).CellStyle = cellStyle; hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7)); } // 表数据 for (int k = 2; k < dt.Rows.Count; k++) { if (k == dt.Rows.Count - 1) { DataRow drlast = dt.Rows[k]; NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2); rowlast.Height = 30 * 20; for (int i = 0; i < dt.Columns.Count; i++) { rowlast.CreateCell(i).SetCellValue(drlast[i].ToString()); rowlast.GetCell(i).CellStyle = cellStyle; } //hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7)); } else { DataRow dr = dt.Rows[k]; NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2); row.Height = 30 * 20; for (int i = 0; i < 4; i += 2) { row.CreateCell(i).SetCellValue(dr[i / 2].ToString()); row.GetCell(i).CellStyle = cellStyle; } for (int i = 4; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dr[i - 2].ToString()); row.GetCell(i).CellStyle = cellStyle; } row.CreateCell(1).SetCellValue(""); row.GetCell(1).CellStyle = cellStyle; row.CreateCell(3).SetCellValue(""); row.GetCell(3).CellStyle = cellStyle; #region 合并单元格 hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1)); hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3)); #endregion } } //NPOI.SS.UserModel.IRow tagRow5 = hssfSheet.CreateRow(dt.Rows.Count + 5); //tagRow5.Height = 20 * 20; //ICell cell12 = tagRow5.CreateCell(0); ////设置单元格内容报告 //cell12.SetCellValue("Rev: 1.0"); //hssfSheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count + 5, dt.Rows.Count + 5, 0, 7)); //cell12.CellStyle = TelNoStyle; #endregion hssfSheet.PrintSetup.NoColor = true; hssfSheet.PrintSetup.Landscape = true; hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4; //是否自适应界面 hssfSheet.FitToPage = true; string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/"; if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath); //return (basePath + "Temp/" + fileName + ".xls"); string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径,可用相对路径 FileInfo fileInfo = new FileInfo(fileURL); Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名 Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小 Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.Default; Response.WriteFile(fileURL); }
/// <summary> /// DataTable导出到Excel的MemoryStream xlsx /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">文件名</param> public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null) { XSSFWorkbook workbook = new XSSFWorkbook(); int i = 0; NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #region 右击文件 属性信息 { // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "NPOI"; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "文件作者信息"; //填加xlsx文件作者信息 // si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息 // si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 // si.Comments = "作者信息"; //填加xls文件作者信息 // si.Title = "标题信息"; //填加xls文件标题信息 // si.Subject = "主题信息";//填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI"); // if (!workbook.GetProperties().CustomProperties.Contains("Company")) // workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company); } #endregion foreach (DataTable dt in dataTables) { string sheetName = string.IsNullOrEmpty(dt.TableName) ? "Sheet " + (++i).ToString() : dt.TableName; ISheet sheet = workbook.CreateSheet(sheetName); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } int rowIndex = 0; #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText) == false) { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); rowIndex += 1; } //headerRow.Dispose(); } #endregion #region 列头及样式 { //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int k = 0; k < dt.Rows.Count; k++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dt.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(); rowIndex += 1; } #endregion //#region 表头 //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); //} //#endregion #region 内容 for (int a = 0; a < dt.Rows.Count; a++) { DataRow dr = dt.Rows[a]; IRow row = sheet.CreateRow(a + rowIndex); for (int b = 0; b < dt.Columns.Count; b++) { row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty); DataColumn dc = dt.Columns[b]; NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal); string drValue = dr[b].ToString(); switch (dc.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 } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); // ms.Position = 0; // sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// 读取excel到datatable中 /// </summary> /// <param name="excelPath">excel地址</param> /// <param name="sheetIndex">sheet索引</param> /// <returns>成功返回datatable,失败返回null</returns> public static DataTable ImportExcel(string excelPath, int sheetIndex) { List <string> lstRows = new List <string>(); IWorkbook workbook = null; //全局workbook ISheet sheet; //sheet DataTable table = null; bool isFirst = true; try { FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在 if (fileInfo.Exists) { FileStream fileStream = fileInfo.OpenRead();//打开文件,得到文件流 switch (fileInfo.Extension) { //xls是03,用HSSFWorkbook打开,.xlsx是07或者10用XSSFWorkbook打开 case ".xls": workbook = new HSSFWorkbook(fileStream); break; case ".xlsx": workbook = new XSSFWorkbook(fileStream); break; default: break; } fileStream.Close();//关闭文件流 } if (workbook != null) { sheet = workbook.GetSheetAt(sheetIndex); //读取到指定的sheet table = new DataTable(); //初始化一个table IRow headerRow = sheet.GetRow(0); //获取第一行,一般为表头 int cellCount = headerRow.Cells.Count; //得到列数 if (cellCount != CommonUtils.heads.Length) { //Program.logger.LogInfo(string.Format("导入的已分配额度表格式不一致,应为“{0}”{1}列", string.Join(",", CommonUtils.heads), CommonUtils.heads.Length)); return(table); } else { for (int i = 0; i < CommonUtils.heads.Length; i++) { DataColumn dc = new DataColumn(CommonUtils.heads[i]); table.Columns.Add(dc); } } try { int code = int.Parse(headerRow.GetCell(1).ToString()); } catch (Exception) { isFirst = false; } int startIdx = sheet.FirstRowNum; if (isFirst == false) { startIdx = sheet.FirstRowNum + 1; } //遍历读取cell for (int i = startIdx; i <= sheet.LastRowNum; i++) { StringBuilder sb = new StringBuilder(); NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //得到一行 DataRow dataRow = table.NewRow(); //新建一个行 bool flag = true; for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); //得到cell if (cell == null) //如果cell为null,则赋值为空 { dataRow[j] = ""; } else { if (j == 1)//股票列表 { if (string.IsNullOrEmpty(row.GetCell(j).ToString().Trim())) { flag = false; break; } dataRow[j] = row.GetCell(j).ToString().Trim().PadLeft(6, '0'); } else { dataRow[j] = row.GetCell(j).ToString().Trim();//否则赋值 } } sb.Append(dataRow[j]); } if (flag) { if (lstRows.Contains(sb.ToString())) { continue; } lstRows.Add(sb.ToString()); table.Rows.Add(dataRow);//把行 加入到table中 } } } return(table); } catch (Exception e) { //Program.logger.LogInfo("读取excel文件异常,{0}", e.Message); return(table); } finally { //释放资源 if (table != null) { table.Dispose(); } workbook = null; sheet = null; } }