public FileResult OmitCheckCountExportExcel() { PatrolRouteStat_SW sw = new PatrolRouteStat_SW(); sw.orgNo = Request.Params["BYORGNO"]; sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; var list = HUReportCls.getPatrolRouteStatModel(sw); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 10 * 256); sheet1.SetColumnWidth(2, 10 * 256); sheet1.SetColumnWidth(3, 10 * 256); sheet1.SetColumnWidth(4, 10 * 256); IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("未巡统计表"); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位/姓名"); row.CreateCell(1).SetCellValue("应巡"); row.CreateCell(2).SetCellValue("已巡"); row.CreateCell(3).SetCellValue("未巡"); row.CreateCell(4).SetCellValue("完成率"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(2).CellStyle = getCellStyleHead(book); row.GetCell(3).CellStyle = getCellStyleHead(book); row.GetCell(4).CellStyle = getCellStyleHead(book); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4)); int rowI = 0; foreach (var v in list) { row = sheet1.CreateRow(rowI + 2); row.CreateCell(0).SetCellValue(v.ORGName); row.CreateCell(1).SetCellValue(v.PointCount); row.CreateCell(2).SetCellValue(v.PointCount0); row.CreateCell(3).SetCellValue(v.PointCount1); row.CreateCell(4).SetCellValue(v.PointCount2); rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public void NpoiExcel(DataTable dt, string title) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int I = 0; I <= dt.Rows.Count - 1; I++) { HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1); for (int j = 0; j <= dt.Columns.Count - 1; j++) { if (j < 2) { string DgvValue = dt.Rows[I][j].ToString(); row2.CreateCell(j).SetCellValue(DgvValue); sheet.SetColumnWidth(j, 20 * 150); } else { if (dt.Rows[I][j].ToString().Trim() != "") { double DgvValue = Convert.ToDouble(dt.Rows[I][j].ToString()); row2.CreateCell(j).SetCellValue(DgvValue); sheet.SetColumnWidth(j, 20 * 150); } else { string DgvValue = dt.Rows[I][j].ToString(); row2.CreateCell(j).SetCellValue(DgvValue); sheet.SetColumnWidth(j, 20 * 150); } } } } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); }
public void NpoiExcel(DataTable dt, string title, string s) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; string[] ss = s.Split(','); for (int i = 0; i < ss.Length; i++) { ICell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(ss[i]); } for (int I = 0; I <= dt.Rows.Count - 1; I++) { HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1); for (int j = 0; j <= dt.Columns.Count - 1; j++) { //double a = 0; //string b = ""; //try //{ // a = Convert.ToDouble(dt.Rows[I][j].ToString()); // row2.CreateCell(j).SetCellValue(a); // sheet.SetColumnWidth(j, 20 * 150); //} //catch //{ // b = dt.Rows[I][j].ToString(); // row2.CreateCell(j).SetCellValue(b); // sheet.SetColumnWidth(j, 20 * 150); //} string DgvValue = dt.Rows[I][j].ToString(); if (j == 8 || j == 9) { row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue)); } else { row2.CreateCell(j).SetCellValue(DgvValue); } sheet.SetColumnWidth(j, 20 * 150); } } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); }
static void Main(string[] args) { InitializeWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); //set the width of columns sheet1.SetColumnWidth(0, 50 * 256); sheet1.SetColumnWidth(1, 100 * 256); sheet1.SetColumnWidth(2, 150 * 256); //set the width of height sheet1.CreateRow(0).Height = 100 * 20; sheet1.CreateRow(1).Height = 200 * 20; sheet1.CreateRow(2).Height = 300 * 20; sheet1.DefaultRowHeightInPoints = 50; WriteToFile(); }
public void Export(string fileName, DataGridView dgv) { string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; IWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; cellStyle.VerticalAlignment = VerticalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.FontName = "微软雅黑"; cellStyle.SetFont(font); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("员工信息表"); sheet.SetColumnWidth(0, 50 * 256); sheet.SetColumnWidth(3, 25 * 256); int rowCount = dgv.Rows.Count; int colCount = dgv.Columns.Count; IRow row = sheet.CreateRow(0); ICell coloun0 = row.CreateCell(0); ICell coloun1 = row.CreateCell(1); ICell coloun2 = row.CreateCell(2); ICell coloun3 = row.CreateCell(3); ICell coloun4 = row.CreateCell(4); ICell coloun5 = row.CreateCell(5); coloun0.SetCellValue("编号"); coloun0.CellStyle = cellStyle; coloun1.SetCellValue("工号"); coloun1.CellStyle = cellStyle; coloun2.SetCellValue("姓名"); coloun2.CellStyle = cellStyle; coloun3.SetCellValue("入职时间"); coloun3.CellStyle = cellStyle; coloun4.SetCellValue("民族"); coloun4.CellStyle = cellStyle; coloun5.SetCellValue("籍贯"); coloun5.CellStyle = cellStyle; for (int i = 0; i < rowCount; i++) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow((sheet.LastRowNum + 1)); for (int j = 0; j < colCount; j++) { if (dgv.Columns[j].Visible && dgv.Rows[i].Cells[j].Value != null) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(j); cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString()); cell.CellStyle = cellStyle; } } } workbook.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK); }
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList) { var first = orderItemList.FirstOrDefault(); //创建表格 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(); #region 定义样式 //创建字体 var cellStyleFont = (HSSFFont)book.CreateFont(); cellStyleFont.IsBold = true; cellStyleFont.FontName = "宋体"; cellStyleFont.FontHeightInPoints = 10; //RGB自定义背景色 HSSFPalette palette = book.GetCustomPalette(); palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220); // titleStyle HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle(); //(自定义背景色)单元格背景颜色 和FillPattern必须一起设置 titleStyle.FillForegroundColor = HSSFColor.Pink.Index; titleStyle.FillPattern = FillPattern.SolidForeground; titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 titleStyle.BorderBottom = BorderStyle.Thin; titleStyle.BorderLeft = BorderStyle.Thin; titleStyle.BorderRight = BorderStyle.Thin; titleStyle.BorderTop = BorderStyle.Thin; titleStyle.SetFont(cellStyleFont);//设置字体 //headStyle HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; //水平居中 headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; headStyle.SetFont(cellStyleFont); //borderStyle HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle(); borderStyle.BorderBottom = BorderStyle.Thin; borderStyle.BorderLeft = BorderStyle.Thin; borderStyle.BorderRight = BorderStyle.Thin; borderStyle.BorderTop = BorderStyle.Thin; #endregion #region 表头 //设置单元格宽度 sheet.SetColumnWidth(0, 5000); //采购日期 sheet.SetColumnWidth(1, 5000); //门店名称 sheet.SetColumnWidth(2, 14000); //门店地址 sheet.SetColumnWidth(3, 4500); //采购数量 sheet.SetColumnWidth(4, 4500); //操作人 //表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(4); row.Height = 350; row.CreateCell(0).SetCellValue("采购日期"); row.CreateCell(1).SetCellValue("门店名称"); row.CreateCell(2).SetCellValue("门店地址"); row.CreateCell(3).SetCellValue("采购数量"); row.CreateCell(4).SetCellValue("操作人"); for (int i = 0; i < 5; i++) { row.Cells[i].CellStyle = titleStyle; } //合并单元格 CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4); sheet.AddMergedRegion(region0); IRow row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("冷链食品处理详情"); row0.Cells[0].CellStyle = headStyle; var row0Cell4 = row0.CreateCell(4); row0Cell4.CellStyle = headStyle; IRow row1 = sheet.CreateRow(1); row1.CreateCell(0).SetCellValue("处理商品批次码"); row1.CreateCell(1).SetCellValue(first.BatchNo.ToString()); row1.Cells[0].CellStyle = titleStyle; IRow row2 = sheet.CreateRow(2); row2.CreateCell(0).SetCellValue("处理日期"); row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd")); row2.Cells[0].CellStyle = titleStyle; IRow row3 = sheet.CreateRow(3); row3.CreateCell(0).SetCellValue("处理人"); row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString()); row3.Cells[0].CellStyle = titleStyle; //设置黑色边框 for (int i = 1; i < 4; i++) { var iRow = sheet.GetRow(i); for (int j = 1; j < 5; j++) { var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j); jCell.CellStyle = borderStyle; } } #endregion #region 循环数据 int index = 5; foreach (var item in orderItemList) { IRow itemRow = sheet.CreateRow(index); var rowCell0 = itemRow.CreateCell(0); rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd")); rowCell0.CellStyle = borderStyle; var rowCell1 = itemRow.CreateCell(1); rowCell1.SetCellValue(item.TargetStoreName.ToString()); rowCell1.CellStyle = borderStyle; var rowCell2 = itemRow.CreateCell(2); rowCell2.SetCellValue(item.TargetStoreAddress.ToString()); rowCell2.CellStyle = borderStyle; var rowCell3 = itemRow.CreateCell(3); rowCell3.SetCellValue(item.Number?.ToString()); rowCell3.CellStyle = borderStyle; var rowCell4 = itemRow.CreateCell(4); rowCell4.SetCellValue(item.ImportUserNames.ToString()); rowCell4.CellStyle = borderStyle; index++; } #endregion MemoryStream ms = new MemoryStream(); book.Write(ms); return(ms); }
/// <summary> /// 导出 /// </summary> /// <returns></returns> public FileResult OutRaiLExportExcel() { OutRaiLCount_SW sw = new OutRaiLCount_SW(); sw.TopORGNO = Request.Params["BYORGNO"]; sw.DateBegin = Request.Params["TIMEBegin"]; sw.DateEnd = Request.Params["TIMEEnd"]; var list = HUCheckCls.getOutRaiLCountModel(sw); DateTime dt1 = Convert.ToDateTime(sw.DateBegin); DateTime dt2 = Convert.ToDateTime(sw.DateEnd); int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数 //vMenu.MENUNAME 页面/菜单名称 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); sheet1.IsPrintGridlines = true; //打印时显示网格线 sheet1.DisplayGridlines = true; //查看时显示网格线 sheet1.SetColumnWidth(0, 30 * 256); sheet1.SetColumnWidth(1, 10 * 256); for (int i = 0; i < days; i++) { sheet1.SetColumnWidth(i + 2, 10 * 256); } IRow row = sheet1.CreateRow(0); row.CreateCell(0).SetCellValue("出围统计表"); row.GetCell(0).CellStyle = getCellStyleTitle(book); row = sheet1.CreateRow(1); row.CreateCell(0).SetCellValue("单位/姓名"); row.CreateCell(1).SetCellValue("出围合计"); row.CreateCell(2).SetCellValue("日期(日)"); row.GetCell(0).CellStyle = getCellStyleHead(book); row.GetCell(1).CellStyle = getCellStyleHead(book); row.GetCell(2).CellStyle = getCellStyleHead(book); row = sheet1.CreateRow(2); for (int i = 0; i < days; i++) { DateTime tm = dt1.AddDays(i); row.CreateCell(i + 2).SetCellValue(tm.ToString("dd")); row.GetCell(i + 2).CellStyle = getCellStyleHead(book); } sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, days + 1)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, days - 2)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1)); int rowI = 0; foreach (var v in list) { row = sheet1.CreateRow(rowI + 3); row.CreateCell(0).SetCellValue(v.ORGName); row.CreateCell(1).SetCellValue(v.Count); string[] arr = v.DayCountList.Split(','); for (int i = 0; i < days; i++) { row.CreateCell(i + 2).SetCellValue(arr[i]); } rowI++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string fileName = "出围统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }