public static NPOI.SS.UserModel.ICellStyle GetCellStyle(this NPOI.SS.UserModel.IWorkbook workbook, short backColorIndex, short fontColorIndex, System.Drawing.Font font, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General, NPOI.SS.UserModel.VerticalAlignment verticalAlignment = NPOI.SS.UserModel.VerticalAlignment.None, NPOI.SS.UserModel.BorderStyle borderLeft = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderTop = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderRight = NPOI.SS.UserModel.BorderStyle.None, NPOI.SS.UserModel.BorderStyle borderBottom = NPOI.SS.UserModel.BorderStyle.None) { NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle(); if (backColorIndex >= 8 && backColorIndex <= 63) { cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; cellStyle.FillForegroundColor = backColorIndex; } cellStyle.Alignment = horizontalAlignment; cellStyle.VerticalAlignment = verticalAlignment; cellStyle.BorderLeft = borderLeft; cellStyle.BorderTop = borderTop; cellStyle.BorderRight = borderRight; cellStyle.BorderBottom = borderBottom; if (font != null) { NPOI.SS.UserModel.IFont cellFont = workbook.CreateFont(); if (fontColorIndex >= 8 && fontColorIndex <= 63) { cellFont.Color = fontColorIndex; } cellFont.FontName = font.Name; cellFont.FontHeight = font.Size; cellFont.Boldweight = (short)(font.Bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : NPOI.SS.UserModel.FontBoldWeight.Normal); cellStyle.SetFont(cellFont); } return(cellStyle); }
/// <summary> /// 设置表头样式 /// </summary> /// <param name="table">工作表</param> /// <param name="style">单元格样式</param> /// <returns></returns> public IExcel HeadStyle(IWorkSheet table, CellStyle style) { if (_headStyle == null) { _headStyle = CellStyleResolver.Resolve(_workbook, style); } Style(0, table.HeadRowCount - 1, 0, table.ColumnCount - 1, _headStyle); return(this); }
/// <summary> /// 设置页脚样式 /// </summary> /// <param name="table">工作表</param> /// <param name="style">单元格样式</param> /// <returns></returns> public IExcel FootStyle(IWorkSheet table, CellStyle style) { if (_footStyle == null) { _footStyle = CellStyleResolver.Resolve(_workbook, style); } Style(table.HeadRowCount + table.BodyRowCount, table.RowCount - 1, 0, table.ColumnCount - 1, _footStyle); return(this); }
/// <summary> /// 导出excel /// </summary> /// <param name="response"></param> /// <param name="dt"></param> /// <param name="fileName"></param> /// <param name="sheetname"></param> public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.IFont font = book.CreateFont(); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "微软雅黑"; style.SetFont(font); for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } } } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls")); response.ContentType = "application/vnd.ms-excel"; //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
/// <summary> /// 写入DataTable到Excel /// </summary> /// <param name="dt"></param> /// <param name="excelFile"></param> public static void writeDataTableToExcel(DataTable dt, string excelFile) { //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion //写入基本数据 writeSheet(workbook, cellStyleA, cellStyleB, dt); #region 输出文件 //输出到流 workbook.Write(memoryStream); //写Excel文件 File.WriteAllBytes(excelFile, memoryStream.ToArray()); #endregion }
/// <summary> /// 创建日期样式 /// </summary> /// <returns></returns> private NPOI.SS.UserModel.ICellStyle CreateDateStyle() { if (_dateStyle != null) { return(_dateStyle); } _dateStyle = CellStyleResolver.Resolve(_workbook, CellStyle.Body()); var format = _workbook.CreateDataFormat(); _dateStyle.DataFormat = format.GetFormat(_dateFormat); return(_dateStyle); }
/// <summary> /// 设置样式 /// </summary> /// <param name="startRowIndex">起始行索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="startColumnIndex">起始列索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <param name="style">单元格样式</param> /// <returns></returns> protected IExcel Style(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex, NPOI.SS.UserModel.ICellStyle style) { for (var i = startRowIndex; i <= endRowIndex; i++) { var row = GetOrCreateRow(i); for (var j = startColumnIndex; j <= endColumnIndex; j++) { GetOrCreateCell(row, j).CellStyle = style; } } return(this); }
private NPOI.SS.UserModel.ICellStyle CloneStyle() { if (myStyle == null) { myStyle = this._cell.Sheet.Workbook.CreateCellStyle(); myStyle.BorderBottom = this._cell.CellStyle.BorderBottom; myStyle.BorderLeft = this._cell.CellStyle.BorderLeft; myStyle.BorderRight = this._cell.CellStyle.BorderRight; myStyle.BorderTop = this._cell.CellStyle.BorderTop; myStyle.FillForegroundColor = this._cell.CellStyle.FillForegroundColor; myStyle.FillPattern = this._cell.CellStyle.FillPattern; } return(myStyle); }
protected void btnExport_Click(object sender, EventArgs e) { DateTime start = Common.St.ToDateTime(txtStart.Value + " 00:00:00"); DateTime end = Common.St.ToDateTime(txtEnd.Value + " 23:59:59"); var list = DAL.WorkPlanRule.Get(start, end); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_2.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("周上线记录"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; NPOI.SS.UserModel.IRow row = sheet.GetRow(0); NPOI.SS.UserModel.ICell cell = row.GetCell(0); cell.SetCellValue("技术测试周上线记录(" + start.ToString("yyyy年MM月dd日") + "-" + end.ToString("yyyy年MM月dd日") + ")"); // 内容 int i = 2; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(o.SheepNo); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.Project.Name); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2); cell2.CellStyle = style; cell2.SetCellValue(Common.St.ToDateTimeString(o.PublishTime, "yyyy-MM-dd")); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("每周项目上线记录", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue) { if (iCell == null) { return; } if (cellValue != null) { if (cellValue.GetType() == typeof(DBNull)) { iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank); } else if (cellValue.GetType() == typeof(bool)) { iCell.SetCellValue((bool)cellValue); } else if (cellValue.GetType() == typeof(string)) { if (cellValue.ToString() == "#NUM!") { iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank); } else { iCell.SetCellValue((string)cellValue); } } else if (cellValue.GetType() == typeof(DateTime)) { NPOI.SS.UserModel.ICellStyle iCellStyle = iCell.Sheet.Workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat(); iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss"); iCell.CellStyle = iCellStyle; iCell.SetCellValue((DateTime)cellValue); } else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float)) { iCell.SetCellValue((double)cellValue); } else { iCell.SetCellValue(cellValue.ToString()); } } }
protected void btnExport_Click(object sender, EventArgs e) { var list = GetList().AsEnumerable().Select(a => new { Name = GetBugzillaUserName(a.Field <int>("reporter")), CC = a.Field <Int64>("cc") }).Where(a => a.Name != ""); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_5.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("bug统计"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; // 内容 int i = 2; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(o.Name); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.CC.ToString()); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("Bug数统计", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
static void CopyStyleFromCell(NPOI.SS.UserModel.ICellStyle To, NPOI.SS.UserModel.ICell Cell) { To.Alignment = Cell.CellStyle.Alignment; To.BorderBottom = Cell.CellStyle.BorderBottom; To.BorderLeft = Cell.CellStyle.BorderLeft; To.BorderRight = Cell.CellStyle.BorderRight; To.BorderTop = Cell.CellStyle.BorderTop; To.BottomBorderColor = Cell.CellStyle.BottomBorderColor; To.FillBackgroundColor = Cell.CellStyle.FillBackgroundColor; To.FillForegroundColor = Cell.CellStyle.FillForegroundColor; To.FillPattern = Cell.CellStyle.FillPattern; To.SetFont(Cell.CellStyle.GetFont(Cell.Row.Sheet.Workbook)); To.IsHidden = Cell.CellStyle.IsHidden; To.IsLocked = Cell.CellStyle.IsLocked; To.LeftBorderColor = Cell.CellStyle.LeftBorderColor; To.RightBorderColor = Cell.CellStyle.RightBorderColor; To.Rotation = Cell.CellStyle.Rotation; To.ShrinkToFit = Cell.CellStyle.ShrinkToFit; To.TopBorderColor = Cell.CellStyle.TopBorderColor; To.VerticalAlignment = Cell.CellStyle.VerticalAlignment; To.WrapText = Cell.CellStyle.WrapText; To.DataFormat = Cell.CellStyle.DataFormat; }
protected void ExportAward_Click(object sender, EventArgs e) { int iPrizeID = 0; int.TryParse(hfPrizeID.Value, out iPrizeID); OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString); conn.Open(); List <Dal.Models.AwardResult> list = BLL.Vote.GetAwardResultReport(iPrizeID, conn); Dal.Models.Prize prize = BLL.Prize.GetPrize(iPrizeID, conn); string ReportFileName = System.Web.HttpContext.Current.Server.MapPath("~/Content/Temp/ExcelTemp/投票结果_" + prize.PrizeName + ".xls"); try { NPOI.HSSF.UserModel.HSSFWorkbook wk = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet ws = wk.CreateSheet("投票结果"); NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICellStyle style = wk.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; row = ws.CreateRow(0); row.HeightInPoints = 35; SetNewCell(row, 0, "排名", style); //增加同名奖内部排序 SetNewCell(row, 1, "同名奖内部排序", style); SetNewCell(row, 2, "项目名称", style); SetNewCell(row, 3, "申报单位", style); //SetNewCell(row, 3, "申报奖项", style); SetNewCell(row, 4, "获奖结果", style); ws.SetColumnWidth(0, 2000); //设置同名奖内部排序列宽 ws.SetColumnWidth(1, 3500); ws.SetColumnWidth(2, 10000); ws.SetColumnWidth(3, 10000); ws.SetColumnWidth(4, 4000); for (int i = 0; i < list.Count(); i++) { row = ws.CreateRow(i + 1); SetNewCell(row, 0, list[i].Ordinal.ToString(), style); //写入同名奖内部排序 SetNewCell(row, 1, list[i].InnerOrdinal.ToString(), style); SetNewCell(row, 2, list[i].DeclarationName.ToString(), style); SetNewCell(row, 3, list[i].OrganizationName.ToString(), style); //SetNewCell(row, 3, list[i].PrizeName.ToString(), style); //SetNewCell(row, 3, BLL.Vote.GetAwardsStatus(list[i].VoteCode.ToString(), list[i].PrizeLevelCode), style); SetNewCell(row, 4, list[i].AwardCodeText, style); } //添加专家签名 row = ws.CreateRow(list.Count() + 2); SetNewCell(row, 1, "专家签名:"); //添加日期 row = ws.CreateRow(list.Count() + 6); SetNewCell(row, 3, "日期:"); ws.ForceFormulaRecalculation = true; //先删除上一个临时文件 if (File.Exists(ReportFileName)) { FileInfo fi = new FileInfo(ReportFileName); if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1) { fi.Attributes = FileAttributes.Normal; } File.Delete(ReportFileName); } //创建临时文件写入数据 using (FileStream filess = File.OpenWrite(ReportFileName)) { wk.Write(filess); } } catch (Exception ex) { Response.Write(JsonConvert.SerializeObject(ex.Message)); if (conn != null && conn.State != ConnectionState.Closed) { conn.Close(); } conn.Dispose(); return; } if (conn != null && conn.State != ConnectionState.Closed) { conn.Close(); } conn.Dispose(); System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AddHeader("Content-Disposition", "attachment; filename=AwardResult_" + DateTime.Now.ToFileTime().ToString() + ".xls"); Response.AddHeader("Content-Length", filet.Length.ToString()); Response.ContentType = "application/ms-excel"; Response.WriteFile(filet.FullName); Response.End(); }
public static void CreateExcelColorFile(string fileName) { System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName); // 创建Workbook。 NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook(); if (fileInfo.Extension == ".xlsx") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else if (fileInfo.Extension == ".xls") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { return; } // 创建Sheet NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor"); // 设置列宽。 sheet.SetColumnWidth(0, 15 * 256); sheet.SetColumnWidth(1, 15 * 256); sheet.SetColumnWidth(2, 20 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 15 * 256); // 创建标题。 int rowIndex = 0; NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0); cell.SetCellValue("Excel颜色"); cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold)); // 空一行 rowIndex++; // 创建标题行 rowIndex++; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); row.Height = 30 * 20; NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center); cell = row.CreateCell(0); cell.SetCellValue("索引"); cell.CellStyle = columnHeadCellStyle; cell = row.CreateCell(1); cell.SetCellValue("索引(NPOI)"); cell.CellStyle = columnHeadCellStyle; cell = row.CreateCell(2); cell.SetCellValue("名称"); cell.CellStyle = columnHeadCellStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3)); cell = row.CreateCell(4); cell.SetCellValue("十六进制代码"); cell.CellStyle = columnHeadCellStyle; ExcelColor.KnownColors.ForEach(x => { rowIndex++; NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex); contentRow.Height = 30 * 20; NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center); NPOI.SS.UserModel.ICell contentCell = contentRow.CreateCell(0); contentCell.SetCellValue(x.Index); contentCell.CellStyle = contentCellStyle; contentCell = contentRow.CreateCell(1); contentCell.SetCellValue(x.IndexNPOI + "(NPOI)"); contentCell.CellStyle = contentCellStyle; contentCell = contentRow.CreateCell(2); contentCell.SetCellValue(x.Name); contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center); contentCell = contentRow.CreateCell(3); contentCell.SetCellValue(x.Description); contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center); contentCell = contentRow.CreateCell(4); contentCell.SetCellValue(x.HexString); contentCell.CellStyle = contentCellStyle; }); // 保存到文件。 System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite); workbook.Write(fileStream); fileStream.Close(); workbook.Close(); }
protected void btnExport_Click(object sender, EventArgs e) { int year = Common.St.ToInt32(selYear.Value); var list = DAL.WorkPlanRule.Get(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_3.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("项目月测试次数"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index; style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index; style.FillPattern = NPOI.SS.UserModel.FillPattern.AltBars; NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle(); style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style2.WrapText = true; style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 内容 int i = 0; int a1, a2, a3, a4, a11 = 0, a22 = 0, a33 = 0, a44 = 0; var userlist = DAL.UserRule.Get(); for (int j = 1; j <= 12; j++) { a3 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).Count(); //上线次数 a4 = list.Where(a => a.State == 2 && a.PublishTime.Year == year && a.PublishTime.Month == j).GroupBy(a => a.ProjectId).Count(); //上线项目数 string t = year + "-" + (j + 1) + "-1"; if (j == 12) { t = (year + 1) + "-1-1"; } a1 = list.Where(a => (a.State == 1 && a.RealStartTime < DateTime.Parse(t)) || (a.State == 2 && (!(a.RealStartTime >= DateTime.Parse(t) || a.RealEndTime < DateTime.Parse(year + "-" + j + "-1"))))).Count(); //工单数 项目数 if (year == DateTime.Today.Year && j > DateTime.Today.Month) { a1 = 0; a2 = 0; } else { a2 = userlist.Where(a => a.Status == 1 || a.LeaveTime.Year * 12 + a.LeaveTime.Month > year * 12 + j).Count(); //测试用户数 } //第一行 NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell00 = row0.CreateCell(0); cell00.CellStyle = style; cell00.SetCellValue(GetMonthName(j)); NPOI.SS.UserModel.ICell cell01 = row0.CreateCell(1); cell01.CellStyle = style; NPOI.SS.UserModel.ICell cell02 = row0.CreateCell(2); cell02.CellStyle = style; NPOI.SS.UserModel.ICell cell03 = row0.CreateCell(3); cell03.CellStyle = style; NPOI.SS.UserModel.ICell cell04 = row0.CreateCell(4); cell04.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4)); //第二行 NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell10 = row1.CreateCell(0); cell10.CellStyle = style; cell10.SetCellValue("工单数"); NPOI.SS.UserModel.ICell cell11 = row1.CreateCell(1); cell11.CellStyle = style; cell11.SetCellValue("项目数"); NPOI.SS.UserModel.ICell cell12 = row1.CreateCell(2); cell12.CellStyle = style; cell12.SetCellValue("测试人数"); NPOI.SS.UserModel.ICell cell13 = row1.CreateCell(3); cell13.CellStyle = style; cell13.SetCellValue("上线次数"); NPOI.SS.UserModel.ICell cell14 = row1.CreateCell(4); cell14.CellStyle = style; cell14.SetCellValue("上线项目数"); //第三行 NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell20 = row2.CreateCell(0); cell20.CellStyle = style2; cell20.SetCellValue(a1); NPOI.SS.UserModel.ICell cell21 = row2.CreateCell(1); cell21.CellStyle = style2; cell21.SetCellValue(a1); NPOI.SS.UserModel.ICell cell22 = row2.CreateCell(2); cell22.CellStyle = style2; cell22.SetCellValue(a2); NPOI.SS.UserModel.ICell cell23 = row2.CreateCell(3); cell23.CellStyle = style2; cell23.SetCellValue(a3); NPOI.SS.UserModel.ICell cell24 = row2.CreateCell(4); cell24.CellStyle = style2; cell24.SetCellValue(a4); i++; a11 += a1; a22 += a2; a33 += a3; a44 += a4; } //第一行 NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0); cell40.CellStyle = style; cell40.SetCellValue(year + "年总和统计"); NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1); cell41.CellStyle = style; NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2); cell42.CellStyle = style; NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3); cell43.CellStyle = style; NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4); cell44.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i - 1, 0, 4)); //第二行 NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0); cell50.CellStyle = style; cell50.SetCellValue("工单数"); NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1); cell51.CellStyle = style; cell51.SetCellValue("项目数"); NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2); cell52.CellStyle = style; cell52.SetCellValue("测试人数"); NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3); cell53.CellStyle = style; cell53.SetCellValue("上线次数"); NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4); cell54.CellStyle = style; cell54.SetCellValue("上线项目数"); //第三行 NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(i++); NPOI.SS.UserModel.ICell cell60 = row6.CreateCell(0); cell60.CellStyle = style2; cell60.SetCellValue(a11); NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1); cell61.CellStyle = style2; cell61.SetCellValue(a11); NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2); cell62.CellStyle = style2; cell62.SetCellValue(a22); NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3); cell63.CellStyle = style2; cell63.SetCellValue(a33); NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4); cell64.CellStyle = style2; cell64.SetCellValue(a44); // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("项目月测试次数", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public static bool OtchProtokolType2(Protokols_class.SGroup_class.Protokol_class Protokol, bool CreateNew = true, bool Open = true) { { var MsgErr = ""; for (int i = 0; i < Protokol.MarkCount; i++) { var PAMIndex = -1; for (int j = 0; j < Protokol.SampleCount; j++) { if (Protokol[j][i].LocalAlow && Protokol[j][i].Method.Length > 0) { if (PAMIndex < 0) { PAMIndex = j; } else if (Protokol[j][i].Method != Protokol[PAMIndex][i].Method) { MsgErr += '\n' + Protokol[j][i].Mark + " имеет различные методы у нормативов " + T.Object.Rows.Get <string>(Protokol[j].ObjectID, C.Object.Norm, C.Norm.Name) + " и " + T.Object.Rows.Get <string>(Protokol[PAMIndex].ObjectID, C.Object.Norm, C.Norm.Name); } } } } if (MsgErr.Length > 0) { MessageBox.Show(MsgErr, "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } } string NewFileName; { int Month, Year; ATMisc.GetYearMonthFromYM(Employe_Form.SPoints.YM, out Year, out Month); { NewFileName = Application.StartupPath + "\\Отчеты\\"; if (!Directory.Exists(NewFileName)) { Directory.CreateDirectory(NewFileName); } NewFileName += T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName) + "\\"; if (!Directory.Exists(NewFileName)) { Directory.CreateDirectory(NewFileName); } NewFileName += ATMisc.GetMonthName1(Month) + "\\"; if (!Directory.Exists(NewFileName)) { Directory.CreateDirectory(NewFileName); } } NewFileName += ProtokolFileName(Protokol); } if (CreateNew || !File.Exists(NewFileName)) { var WorkBook = ATMisc.GetGenericExcel("Протокол испытаний тип 2.xls"); if (WorkBook == null) { return(false); } var TitleSheet = WorkBook.GetSheet("Заголовок"); NPOI.SS.UserModel.ISheet Sheet1; if (TitleSheet == null) { MessageBox.Show("В шаблоне не найден лист \"Заголовок\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } var DT = Protokol.Date; GetProtokolsExchanges(TitleSheet , ATMisc.GetDateTimeFromYM(Employe_Form.SPoints.YM).Year , Protokol.Number.ToString() + "-АВ-" + DT.Month.ToString() + "/" + DT.Year.ToString() , Protokol.Objects + ' ' + T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName) , Protokol.Objects , Protokol.ProtoType , Protokol.ObjectsLocations , Protokol.DateOstr , Protokol.DateP , Protokol.StrTime , Protokol.Peoples , Protokol.Causes , Protokol.Numbers , Protokol.Day.ToString() , ATMisc.GetMonthName2(DT.Month) , DT.Month.ToString() , RCache.PSG.GetMethodName(Protokol.PodrID) , T.PaPoS.Rows.Get <string>(Protokol.PaPoSID, C.PaPoS.Name) , T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.FllName) , T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.ShrName) , T.Podr.Rows.Get <string>(Protokol.PodrID, C.Podr.Contact) , 0).Exchange(); { var Exchanges = new CellExchange_Class(TitleSheet); Exchanges.ClearExchanges(); Exchanges.AddColumn("{имя свойства}"); Exchanges.AddColumn("{ед. свойства}"); Exchanges.AddColumn("{значение свойства}"); if (Exchanges.CheckTableColumns()) { for (int i = 0; i < Protokol[0].TCSCount; i++) { var Values = new object[3]; Values[0] = Protokol[0].TCSName(i); Values[1] = Protokol[0].TCSEdType(i); Values[2] = Protokol[0].TCSValue(i); Exchanges.SetRow(Values); } } } if (Protokol.MarkCount == 0) { MessageBox.Show("Заполненые показатели не найдены.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } string UnDeleteSheetName; switch (Protokol.SGroup) { case data.SGroup.Group2: UnDeleteSheetName = "Изначальный"; { int Index = WorkBook.GetSheetIndex(UnDeleteSheetName); Sheet1 = WorkBook.GetSheetAt(Index); WorkBook.SetSheetName(Index, "Концентрации"); } if (Sheet1 == null) { MessageBox.Show("В шаблоне не найден лист \"" + UnDeleteSheetName + "\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } var SPointIndex = new SColumn_struct(-1, null); var OEdTypeIndex = new SColumn_struct(-1, null); var OMethodIndex = new SColumn_struct(-1, null); var OMarkNameIndex = new SColumn_struct(-1, null); var NumberIndex = new SColumn_struct(-1, null); var ResultIndex = new SColumn_struct(-1, null); var ProbeIndex = new SColumn_struct(-1, null); int RowIndex = -1; var SPStyle = new NPOI.SS.UserModel.ICellStyle[3]; //верх, середина, низ var PStyle = new NPOI.SS.UserModel.ICellStyle[3]; //верх, середина, низ { var OEdTypeRowIndex = -1; var OMethodRowIndex = -1; var OMarkNameRowIndex = -1; var NumberRowIndex = -1; var SPointRowIndex = -1; var ResultRowIndex = -1; var ProbeRowIndex = -1; var ExistColumn = new CellExchange_Class(Sheet1); ExistColumn.AddExchange("{номер п/п}", (Cell) => { NumberRowIndex = Cell.RowIndex; NumberIndex = new SColumn_struct(Cell); }, 5); ExistColumn.AddExchange("{место отбора}", (Cell) => { SPointRowIndex = Cell.RowIndex; SPointIndex = new SColumn_struct(Cell); SPStyle[0] = Sheet1.Workbook.CreateCellStyle(); //верх CopyStyleFromCell(SPStyle[0], Cell); SPStyle[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.None; SPStyle[1] = Sheet1.Workbook.CreateCellStyle(); //середина CopyStyleFromCell(SPStyle[1], Cell); SPStyle[1].BorderBottom = SPStyle[1].BorderTop = NPOI.SS.UserModel.BorderStyle.None; SPStyle[2] = Sheet1.Workbook.CreateCellStyle(); //низ CopyStyleFromCell(SPStyle[2], Cell); SPStyle[2].BorderTop = NPOI.SS.UserModel.BorderStyle.None; }, 5); ExistColumn.AddExchange("{показатель}", (Cell) => { OMarkNameRowIndex = Cell.RowIndex; OMarkNameIndex = new SColumn_struct(Cell); }, 5); ExistColumn.AddExchange("{проба}", (Cell) => { ProbeRowIndex = Cell.RowIndex; ProbeIndex = new SColumn_struct(Cell); PStyle[0] = Sheet1.Workbook.CreateCellStyle(); //верх CopyStyleFromCell(PStyle[0], Cell); PStyle[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.None; PStyle[1] = Sheet1.Workbook.CreateCellStyle(); //середина CopyStyleFromCell(PStyle[1], Cell); PStyle[1].BorderBottom = PStyle[1].BorderTop = NPOI.SS.UserModel.BorderStyle.None; PStyle[2] = Sheet1.Workbook.CreateCellStyle(); //низ CopyStyleFromCell(PStyle[2], Cell); PStyle[2].BorderTop = NPOI.SS.UserModel.BorderStyle.None; }, 5); ExistColumn.AddExchange("{ед.изм.}", (Cell) => { OEdTypeRowIndex = Cell.RowIndex; OEdTypeIndex = new SColumn_struct(Cell); }, 5); ExistColumn.AddExchange("{методика}", (Cell) => { OMethodRowIndex = Cell.RowIndex; OMethodIndex = new SColumn_struct(Cell); }, 5); ExistColumn.AddExchange("{результат}", (Cell) => { ResultRowIndex = Cell.RowIndex; ResultIndex = new SColumn_struct(Cell); }, 5); ExistColumn.AddExchange("{Дата}", DT.ToShortDateString(), 5); ExistColumn.AddExchange("{Номер протокола}", Protokol.Number.ToString() + "-АВ-" + DT.Month.ToString() + "/" + DT.Year.ToString(), 5); SetResp(ExistColumn, Protokol.PodrID, data.TResp.LaboratoryProtokol); if (SPointRowIndex == -1 || OMarkNameRowIndex == -1 || OEdTypeRowIndex == -1 || OMethodRowIndex == -1 || ResultRowIndex == -1) { MessageBox.Show("Не все табличные метки найдены.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } if (ProbeRowIndex > -1 && ProbeRowIndex != OMarkNameRowIndex || NumberRowIndex > -1 && NumberRowIndex != OMarkNameRowIndex || SPointRowIndex != OMarkNameRowIndex || OMarkNameRowIndex != OEdTypeRowIndex || OEdTypeRowIndex != OMethodRowIndex || OMethodRowIndex != ResultRowIndex) { MessageBox.Show("Все табличные метки должны распологаться в одной строке.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } RowIndex = SPointRowIndex; } Sheet1.ShiftRows(RowIndex, Sheet1.LastRowNum, Protokol.TableCount * Protokol.MarkCount - 1); int ONumber = 0; for (int i = 0; i < Protokol.TableCount; i++) { NPOI.SS.UserModel.IRow Row; for (int j = 0; j < Protokol.MarkCount; j++) { Row = Sheet1.CreateRow(RowIndex++); if (NumberIndex.Index > -1) { ATMisc.SetValue(Row, ++ONumber, NumberIndex.Index, NumberIndex.Style); } if (ProbeIndex.Index > -1) { ATMisc.SetValue(Row, Protokol[i].Number, ProbeIndex.Index, ProbeIndex.Style); } ATMisc.SetValue(Row, Protokol.GetMethod(j), OMethodIndex.Index, OMethodIndex.Style); ATMisc.SetValue(Row, Protokol.GetMarkName(j), OMarkNameIndex.Index, OMarkNameIndex.Style); ATMisc.SetValue(Row, Protokol.GetMarkEdType(j), OEdTypeIndex.Index, OEdTypeIndex.Style); if (Protokol.IsSpetialOut(j, i)) { ATMisc.SetValue(Row, Protokol.GetSpetialOut(j, i), ResultIndex.Index, ResultIndex.Style); } else { ATMisc.SetValue(Row, Protokol.GetMarkAmount(j, i), ResultIndex.Index, ResultIndex.Style); } } Row = Sheet1.GetRow(RowIndex - Protokol.MarkCount); switch (Protokol.MarkCount) { case 1: ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPointIndex.Style); break; case 2: Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, SPointIndex.Index, SPointIndex.Index)); ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPStyle[0]); Sheet1.GetRow(RowIndex - 1).CreateCell(SPointIndex.Index).CellStyle = SPStyle[2]; if (ProbeIndex.Index > -1) { Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2]; Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, ProbeIndex.Index, ProbeIndex.Index)); } break; default: Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, SPointIndex.Index, SPointIndex.Index)); ATMisc.SetValue(Row, Protokol.GetTableName(i), SPointIndex.Index, SPStyle[0]); if (ProbeIndex.Index > -1) { Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2]; Sheet1.GetRow(RowIndex - 1).CreateCell(SPointIndex.Index).CellStyle = PStyle[2]; Sheet1.AddMergedRegion(new CellRangeAddress(RowIndex - Protokol.MarkCount, RowIndex - 1, ProbeIndex.Index, ProbeIndex.Index)); for (int s = Protokol.MarkCount - 2; s > 1; s--) { Sheet1.GetRow(RowIndex - 1 - s).GetCell(ProbeIndex.Index).CellStyle = PStyle[1]; //Sheet1.GetRow(RowIndex - 1 - s).GetCell(SPointIndex.Index).CellStyle = SPStyle[1]; //это тут лишнее } Sheet1.GetRow(RowIndex - 1).CreateCell(ProbeIndex.Index).CellStyle = PStyle[2]; Sheet1.GetRow(RowIndex - 1).GetCell(SPointIndex.Index).CellStyle = SPStyle[2]; } else { for (int s = Protokol.MarkCount - 2; s > 1; s--) { Sheet1.GetRow(RowIndex - 1 - s).GetCell(SPointIndex.Index).CellStyle = SPStyle[1]; } Sheet1.GetRow(RowIndex - 1).GetCell(SPointIndex.Index).CellStyle = SPStyle[2]; } break; } } break; default: throw new Exception("Не верный тип протокола"); } for (int i = 0; i < WorkBook.NumberOfSheets; i++) { if (WorkBook.GetSheetAt(i).SheetName.ToLower() != "заголовок" && WorkBook.GetSheetAt(i).SheetName.ToLower() != "концентрации") { WorkBook.RemoveSheetAt(i); i--; } } return(SaveExcel(WorkBook, NewFileName, Open)); } else { if (Open) { System.Diagnostics.Process.Start(NewFileName); } return(true); } }
/// <summary> /// 输出数据表格 /// </summary> /// <param name="workbook">工作文档</param> /// <param name="normalStyle">普通样式(用于表格内容)</param> /// <param name="boldStyle">粗体样式(用于表格头部)</param> /// <param name="table">表格数据</param> public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table) { //创建Sheet页 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); //行号 int rowIndex = 0; //是否需要输出表头 bool isNeedCreateHeader = true; //输出数据到Excel foreach (DataRow rowData in table.Rows) { //忽略空数据行 if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count) { continue; } //列号 int colIndex = 0; //Excel行 NPOI.SS.UserModel.IRow row = null; //是否需要输入表头 if (isNeedCreateHeader) { isNeedCreateHeader = false; //创建行 row = sheet.CreateRow(rowIndex); //输出列名到Excel colIndex = 0; foreach (DataColumn kvp in table.Columns) { //列名 //创建列 NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex); //设置样式 cell.CellStyle = boldStyle; //设置数据 cell.SetCellValue(kvp.ColumnName); colIndex++; } rowIndex++; } //创建行 row = sheet.CreateRow(rowIndex); //输出列值到Excel colIndex = 0; foreach (object val in rowData.ItemArray) { //列值 //创建列 NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex); //设置样式 cell.CellStyle = normalStyle; //设置数据 //判断是否为空 if (val != null) { //不为空 //判断是否为RTF内容 if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name)) { //RTF内容 cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val); } else { //文本内容 cell.SetCellValue(val.ToString()); } } else { //为空 cell.SetCellValue(string.Empty); } colIndex++; } rowIndex++; } //Excel列宽自动适应 if (table.Rows.Count >= 1 && sheet.GetRow(0) != null) { for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++) { sheet.AutoSizeColumn(k); } } }
/// <summary> /// 获取EXCEL数组 /// </summary> /// <param name="dt"></param> public static byte[] GetExcelFileByte(DataSet ds) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //设置样式 NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle(); NPOI.SS.UserModel.IFont font1 = book.CreateFont(); style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; font1.IsBold = true; style1.SetFont(font1); NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle(); style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; byte[] fileByte = null; for (int k = 0; k < ds.Tables.Count; k++) { try { DataTable dt = ds.Tables[k]; if (null != dt && dt.Rows.Count > 0) { if (dt.TableName == null || dt.TableName == "") { dt.TableName = "Sheet1"; } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName); sheet.DefaultColumnWidth = 20; NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); row.Cells[i].CellStyle = style1; } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); row2.Cells[j].CellStyle = style2; } } } } catch (Exception ex) { throw new Exception(ex.Message); } } try { // 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); fileByte = ms.GetBuffer(); //using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) //{ // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); //} } } catch (Exception) { throw; } finally { if (null != book) { book.Close(); book = null; } } return(fileByte); }
public static bool OtchMassOutgo(uint PodrID, int YMFrom, Podrs_class.PeriodType pt, uint OTypeID, bool ShowErrorMessage = true, bool CreateNew = true, bool Open = true) { bool Returning = false; RCache.Podrs = new RCache.Podrs_class(); var Podrs = new Podrs_class(PodrID, pt, YMFrom, OTypeID); int Year, Month; ATMisc.GetYearMonthFromYM(YMFrom, out Year, out Month); if (Podrs.MarksCount == 0) { if (ShowErrorMessage) { MessageBox.Show("Показатели не обнаружены", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } { int tMCount = 0; for (int i = 0; i < Podrs.MarkCount; i++) { if (Podrs.ShowMark(i)) { tMCount++; } } if (tMCount == 0) { if (ShowErrorMessage) { MessageBox.Show("Показатели не обнаружены", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } } G.OPType.QUERRY().SHOW.DO(); var StylesEDType = new NPOI.SS.UserModel.ICellStyle[G.OPType.Rows.Count]; if (!Directory.Exists(Application.StartupPath + "\\Отчеты")) { Directory.CreateDirectory(Application.StartupPath + "\\Отчеты"); } for (int i = 0; i < Podrs.Count; i++) //подразделения { if (Podrs[i].MarksCount > 0) { var NewFileName = Application.StartupPath + "\\Отчеты\\Расчет массы " + Podrs.PeriodName() + ' ' + Year.ToString() + ' ' + T.Podr.Rows.Get <string>(Podrs[i].PodrID, C.Podr.ShrName) + ".xls"; if (CreateNew || !File.Exists(NewFileName)) { var WorkBook = ATMisc.GetGenericExcel("Расчет массы.xls"); if (WorkBook == null) { return(false); } var Sheet1 = WorkBook.GetSheet("Таблица"); if (Sheet1 == null) { if (ShowErrorMessage) { MessageBox.Show("В шаблоне не найден лист \"Заголовок\", вывод невозможен.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } var Exchanges = new CellExchange_Class(Sheet1); NPOI.SS.UserModel.ICellStyle NumberStyle = null , NameStyle = null , EdTypeStyle = null , CodeStyle = null , FactStyle = null , BackStyle = null , CalcStyle = null , MassStyle = null; var Podr = RCache.Podrs[Podrs[i].PodrID]; Exchanges.AddExchange("{сооружение}", Podr.ShortName, 2); for (int s = 0; s < Podrs[i].Count; s++) { if (Podrs[i][s].ObjectID > 0) { Exchanges.AddExchange("{объект}", T.Object.Rows.Get <string>(Podrs[i][s].ObjectID, C.Object.OType, C.OType.Name).ToLower(), 2); break; } } if (Podr.Ppls.Count == 0 || (data.PnMean)Podr.Ppls[0].PnMeanID != data.PnMean.Nachalnic) { if (ShowErrorMessage) { MessageBox.Show("У сооружения " + Podr.ShortName + " нет начальника.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } Exchanges.AddExchange("{начальник сооружения}", Podr.Ppls[0].PeopleName, 2); Exchanges.AddExchange("{профессия начальника}", Podr.Ppls[0].Profession, 2); { var PeopleID = RCache.PSG.GetPeopleID(Podr.PSG); Exchanges.AddExchange("{профессия ответственного}", T.People.Rows.Get <string>(PeopleID, C.People.Prfssn, C.Prfssn.Name), 2); Exchanges.AddExchange("{ФИО ответственного}", Misc.GetShortFIO(PeopleID), 2); } Exchanges.AddExchange("{год}", Year.ToString(), 5); int NumberIndex = -1, NameIndex = -1, EdTypeIndex = -1, CodeIndex = -1, FactIndex = -1, BackIndex = -1, CalcIndex = -1, MassIndex = -1, RowIndex = -1; { int NumberRowIndex = -1, NameRowIndex = -1, EdTypeRowIndex = -1, CodeRowIndex = -1, FactRowIndex = -1, BackRowIndex = -1, CalcRowIndex = -1, MassRowIndex = -1; Exchanges.AddExchange("{выпуск}", Cell => { NumberIndex = Cell.ColumnIndex; NumberRowIndex = Cell.RowIndex; NumberStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{имя}", Cell => { NameIndex = Cell.ColumnIndex; NameRowIndex = Cell.RowIndex; NameStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{едизм}", Cell => { EdTypeIndex = Cell.ColumnIndex; EdTypeRowIndex = Cell.RowIndex; EdTypeStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{код}", Cell => { CodeIndex = Cell.ColumnIndex; CodeRowIndex = Cell.RowIndex; CodeStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{факт}", Cell => { FactIndex = Cell.ColumnIndex; FactRowIndex = Cell.RowIndex; FactStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{фон}", Cell => { BackIndex = Cell.ColumnIndex; BackRowIndex = Cell.RowIndex; BackStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{расчет}", Cell => { CalcIndex = Cell.ColumnIndex; CalcRowIndex = Cell.RowIndex; CalcStyle = Cell.CellStyle; }, 1); Exchanges.AddExchange("{масса}", Cell => { MassIndex = Cell.ColumnIndex; MassRowIndex = Cell.RowIndex; MassStyle = Cell.CellStyle; for (int j = 0; j < StylesEDType.Length; j++) { StylesEDType[j] = WorkBook.CreateCellStyle(); CopyStyleFromCell(StylesEDType[j], Cell); StylesEDType[j].DataFormat = WorkBook.CreateDataFormat().GetFormat("#,##0.000\" " + G.OPType.Rows.Get <string>(j, C.OPType.EdTypeT, C.EdType.Name) + "\""); } }, 1); Exchanges.AddExchange("{период}", Podrs.PeriodName(), 2); SetResp(Exchanges, Podr.ID, data.TResp.LaboratoryProtokol); if (NameRowIndex == NumberRowIndex && NumberRowIndex == FactRowIndex && NumberRowIndex == BackRowIndex && BackRowIndex == CalcRowIndex && NumberRowIndex == MassRowIndex) { if (MassRowIndex == -1) { if (ShowErrorMessage) { MessageBox.Show("Критически важные колонки таблицы не найдены(выпуск,наименование, факт, фон, расчет, масса).", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } } else { if (ShowErrorMessage) { MessageBox.Show("Метки всех колонок должны находиться на одной строке.", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(false); } RowIndex = NumberRowIndex; Sheet1.ShiftRows(RowIndex + 1, Sheet1.LastRowNum, -1); } Sheet1.SetColumnHidden(BackIndex, !Podrs.AllowBack); Sheet1.SetColumnHidden(CalcIndex, !Podrs.AllowBack); if (!Podrs.AllowBack) { int NewWidth = Sheet1.GetColumnWidth(BackIndex) + Sheet1.GetColumnWidth(FactIndex) + Sheet1.GetColumnWidth(CalcIndex); Sheet1.SetColumnWidth(FactIndex, NewWidth); } for (int j = 0; j < Podrs[i].Count; j++) //выпуск { Sheet1.ShiftRows(RowIndex, Sheet1.LastRowNum, Podrs[i][j].Count + 2); var LastRow = Sheet1.CreateRow(RowIndex + Podrs[i][j].Count); if (EdTypeIndex > -1) { ATMisc.SetValue(LastRow, "тыс.м3", EdTypeIndex, EdTypeStyle); } ATMisc.SetValue(LastRow, T.Object.Rows.Get <string>(Podrs[i][j].ObjectID, C.Object.OLocationTo, C.OLocation.Name), MassIndex, MassStyle); if (Podrs[i][j].Count > 0) { for (int k = 0; k < Podrs[i][j].Count; k++) { var MarkID = Podrs[i][j].MarkID(k); var Row = Sheet1.CreateRow(RowIndex++); if (NameIndex > -1) { ATMisc.SetValue(Row, T.Mark.Rows.Get <string>(MarkID, C.Mark.Name), NameIndex, NameStyle); } if (EdTypeIndex > -1) { ATMisc.SetValue(Row, T.Mark.Rows.Get <string>(MarkID, C.Mark.EdType, C.EdType.Name), EdTypeIndex, EdTypeStyle); } if (CodeIndex > -1) { ATMisc.SetValue(Row, T.Mark.Rows.Get <int>(MarkID, C.Mark.Code), CodeIndex, CodeStyle); } double Front = RCache.Marks[MarkID].GetRound(Podrs[i][j][k].FMiddle) , Back = RCache.Marks[MarkID].GetRound(Podrs[i][j][k].BMiddle); ATMisc.SetValue(Row, Front, FactIndex, FactStyle); ATMisc.SetValue(Row, Back, BackIndex, BackStyle); ATMisc.SetFormula(Row, "if(" + GetColCharName(FactIndex) + RowIndex.ToString() + "<=" + GetColCharName(BackIndex) + RowIndex.ToString() + ", \"<=фон\", " + GetColCharName(FactIndex) + RowIndex.ToString() + "-" + GetColCharName(BackIndex) + RowIndex.ToString() + ")", CalcIndex, CalcStyle); ATMisc.SetFormula(Row, "if(" + GetColCharName(CalcIndex) + RowIndex.ToString() + "=\"<=фон\",\"\", " + GetColCharName(CalcIndex) + RowIndex.ToString() + "*" + GetColCharName(FactIndex) + (LastRow.RowNum + 1).ToString() + "*" + DTSF(RCache.Marks[MarkID].Multyply) + ")", MassIndex, StylesEDType[G.OPType.Rows.GetIndex(RCache.Marks[MarkID].OPTypeID)]); if (NumberIndex > -1) { ATMisc.SetValue(Row, "", NumberIndex, NumberStyle); } } if (NumberIndex > -1) { ATMisc.SetValue(Sheet1.GetRow(RowIndex - Podrs[i][j].Count), Podrs[i][j].VShortName + " [" + (j + 1).ToString() + "]", NumberIndex, NumberStyle); } if (NumberIndex > -1) { ATMisc.SetValue(LastRow, "", NumberIndex, NumberStyle); } } else { if (NumberIndex > -1) { ATMisc.SetValue(LastRow, Podrs[i][j].VShortName + " [" + (j + 1).ToString() + "]", NumberIndex, NumberStyle); } } RowIndex++; ATMisc.SetValue(LastRow, Podrs[i][j].VolumeSumm, FactIndex, FactStyle); ATMisc.SetValue(LastRow, "Объём сброшеных вод", NameIndex, NameStyle); if (EdTypeIndex > -1) { ATMisc.SetValue(LastRow, "тыс.м3", EdTypeIndex, EdTypeStyle); } if (CodeIndex > -1) { ATMisc.SetValue(LastRow, "", CodeIndex, CodeStyle); } ATMisc.SetValue(LastRow, "", BackIndex, BackStyle); ATMisc.SetValue(LastRow, "", CalcIndex, CalcStyle); Sheet1.CreateRow(RowIndex++).Height = 50; } Returning = Returning || SaveExcel(WorkBook, NewFileName, Open); } else if (Open && File.Exists(NewFileName)) { System.Diagnostics.Process.Start(NewFileName); Returning = true; } } } return(Returning); }
public static void CreateCell(NPOI.SS.UserModel.IRow row, int index, object value, NPOI.SS.UserModel.ICellStyle cellStyle) { var cell = row.CreateCell(index); if (value is decimal) { cell.SetCellValue((double)((decimal)value)); } else if (value is int) { cell.SetCellValue((int)value); } else { cell.SetCellValue((string)value); } cell.CellStyle = cellStyle; }
/// <summary> /// 设置样式 /// </summary> private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style) { NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.ICell newCell = null; for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } else if (strColDataType.Equals("System.DateTime")) { DateTime dateV; DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV); newCell = row2.CreateCell(j); newCell.SetCellValue(dateV); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-m-d"); newCell.CellStyle = cellStyle; } } } }
protected void lbtn_export_ribao_export_Click(object sender, EventArgs e) { string url = datemin.Value; if (url == "") { SystemTool.AlertShow(this, "开始日期不能为空"); return; } url = datemax.Value; if (url == "") { SystemTool.AlertShow(this, "结束日期不能为空"); return; } url = img1.ImageUrl; if (url == "") { SystemTool.AlertShow(this, "图片为空,请先刷新"); return; } if (ViewState["date"].ToString() != datemin.Value || ViewState["date2"].ToString() != datemax.Value) { SystemTool.AlertShow(this, "当前图片与日期不一致,请先刷新后再导出此报表"); return; } string imgurl = Server.MapPath("~/" + url); //Response.Write("<script>window.open('print.aspx?date="+datemin.Value+"&url="+url+"','_blank')</script>"); string sql = @"select facename,sensorNo,bracketNo,distance,max(zlmax) zlmax,AVG(zlavg) zlavg,min(zlmin) zlmin,MAX(cclmax) cclmax,AVG(cclavg) cclavg,MAX(mzlmax) mzlmax,AVG(mzlavg) mzlavg from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' group by bracketNo,sensorNo,distance,facename order by bracketno"; DataSet ds = DB.ExecuteSqlDataSet(sql, null); if (ds.Tables[0].Rows.Count <= 0) { SystemTool.AlertShow(this, "报表为空,请在初撑力与末阻力页面查询数据后再导出此报表"); return; } //整面最大值、最小值 string zhengmianmax = ds.Tables[0].Compute("Max(zlmax)", "true").ToString(); string zhengmianmin = ds.Tables[0].Compute("Min(zlmin)", "true").ToString(); string zhengmianavg = Convert.ToDecimal(ds.Tables[0].Compute("avg(zlavg)", "true").ToString()).ToString("0.00"); string shangbumax = "0.00"; string shangbumin = "0.00"; string shangbuavg = "0.00"; string zhongbumax = "0.00"; string zhongbumin = "0.00"; string zhongbuavg = "0.00"; string xiabumax = "0.00"; string xiabumin = "0.00"; string xiabuavg = "0.00"; string sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='上部'"; DataSet dsszx = DB.ExecuteSqlDataSet(sqlszx, null); if (dsszx.Tables[0].Rows.Count > 0) { shangbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00"); shangbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00"); shangbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00"); } sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='中部'"; dsszx = DB.ExecuteSqlDataSet(sqlszx, null); if (dsszx.Tables[0].Rows.Count > 0) { zhongbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00"); zhongbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00"); zhongbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00"); } sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='下部'"; dsszx = DB.ExecuteSqlDataSet(sqlszx, null); if (dsszx.Tables[0].Rows.Count > 0) { xiabumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00"); xiabumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00"); xiabuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00"); } NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); //设置单元格的样式:水平对齐居中 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.ICellStyle styleleft = book.CreateCellStyle(); //设置单元格的样式:水平对齐居左 styleleft.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; styleleft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleleft.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleleft.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleleft.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.ICellStyle styleleftcenter = book.CreateCellStyle(); //设置单元格的样式:居左居中 styleleftcenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; styleleftcenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; styleleftcenter.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleleftcenter.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleleftcenter.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleleftcenter.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; styleleftcenter.WrapText = true; //将新的样式赋给单元格 //cell.CellStyle = style; //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域 //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 //第一行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9)); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0); cell0.SetCellValue("综采支架压力综合日报表【报表日期" + datemin.Value + "-" + datemax.Value + "】"); cell0.CellStyle = style; row.CreateCell(1).CellStyle = style; row.CreateCell(2).CellStyle = style; row.CreateCell(3).CellStyle = style; row.CreateCell(4).CellStyle = style; row.CreateCell(5).CellStyle = style; row.CreateCell(6).CellStyle = style; row.CreateCell(7).CellStyle = style; row.CreateCell(8).CellStyle = style; row.CreateCell(9).CellStyle = style; //第二行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9)); NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1); NPOI.SS.UserModel.ICell cell1 = row1.CreateCell(0); cell1.SetCellValue("单位:兆帕 工作面名称:" + ds.Tables[0].Rows[0]["facename"].ToString() + " 认证编号: 打印日期:" + DateTime.Now.ToString("yyyy-MM-dd")); cell1.CellStyle = style; row1.CreateCell(1).CellStyle = style; row1.CreateCell(2).CellStyle = style; row1.CreateCell(3).CellStyle = style; row1.CreateCell(4).CellStyle = style; row1.CreateCell(5).CellStyle = style; row1.CreateCell(6).CellStyle = style; row1.CreateCell(7).CellStyle = style; row1.CreateCell(8).CellStyle = style; row1.CreateCell(9).CellStyle = style; // 第三行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9)); NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(2); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(0); cell2.CellStyle = style; row2.CreateCell(1).CellStyle = style; row2.CreateCell(2).CellStyle = style; row2.CreateCell(3).CellStyle = style; row2.CreateCell(4).CellStyle = style; row2.CreateCell(5).CellStyle = style; row2.CreateCell(6).CellStyle = style; row2.CreateCell(7).CellStyle = style; row2.CreateCell(8).CellStyle = style; row2.CreateCell(9).CellStyle = style; row2.Height = 4800; //将图片文件读入一个字符串 byte[] bytes = System.IO.File.ReadAllBytes(imgurl); int pictureIdx = book.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); NPOI.HSSF.UserModel.HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.CreateDrawingPatriarch(); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 //dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一 //dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 //dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。 //dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 //col1和row1 :图片左上角的位置,以excel单元格为参考,比喻这两个值为(1,1),那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。 //col2和row2:图片右下角的位置,以excel单元格为参考,比喻这两个值为(2,2),那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。 NPOI.HSSF.UserModel.HSSFClientAnchor anchor = new NPOI.HSSF.UserModel.HSSFClientAnchor(10, 10, 10, 10, 0, 2, 9, 3); //把图片插到相应的位置 NPOI.HSSF.UserModel.HSSFPicture pict = (NPOI.HSSF.UserModel.HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //第三行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 9)); NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(3); NPOI.SS.UserModel.ICell cell3 = row3.CreateCell(0); cell3.SetCellValue("监测数据统计表:"); cell3.CellStyle = styleleft; row3.CreateCell(1).CellStyle = styleleft; row3.CreateCell(2).CellStyle = styleleft; row3.CreateCell(3).CellStyle = styleleft; row3.CreateCell(4).CellStyle = styleleft; row3.CreateCell(5).CellStyle = styleleft; row3.CreateCell(6).CellStyle = styleleft; row3.CreateCell(7).CellStyle = styleleft; row3.CreateCell(8).CellStyle = styleleft; row3.CreateCell(9).CellStyle = styleleft; //第四行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 8, 0, 1)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 2, 3)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 4, 5)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 6, 7)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 8, 9)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 2, 3)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 4, 5)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 6, 7)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 8, 9)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 2, 3)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 4, 5)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 6, 7)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 8, 9)); NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(4); NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0); cell40.SetCellValue("工作面工作阻力统计:"); cell40.CellStyle = style; NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1); cell41.CellStyle = style; NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2); cell42.SetCellValue("整面"); cell42.CellStyle = style; NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3); cell43.CellStyle = style; NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4); cell44.SetCellValue("上部"); cell44.CellStyle = style; NPOI.SS.UserModel.ICell cell45 = row4.CreateCell(5); cell45.CellStyle = style; NPOI.SS.UserModel.ICell cell46 = row4.CreateCell(6); cell46.SetCellValue("中部"); cell46.CellStyle = style; NPOI.SS.UserModel.ICell cell47 = row4.CreateCell(7); cell47.CellStyle = style; NPOI.SS.UserModel.ICell cell48 = row4.CreateCell(8); cell48.SetCellValue("下部"); cell48.CellStyle = style; NPOI.SS.UserModel.ICell cell49 = row4.CreateCell(9); cell49.CellStyle = style; NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(5); NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0); cell50.CellStyle = style; NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1); cell51.CellStyle = style; NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2); cell52.SetCellValue("最大"); cell52.CellStyle = style; NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3); cell53.SetCellValue("最小"); cell53.CellStyle = style; NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4); cell54.SetCellValue("最大"); cell54.CellStyle = style; NPOI.SS.UserModel.ICell cell55 = row5.CreateCell(5); cell55.SetCellValue("最小"); cell55.CellStyle = style; NPOI.SS.UserModel.ICell cell56 = row5.CreateCell(6); cell56.SetCellValue("最大"); cell56.CellStyle = style; NPOI.SS.UserModel.ICell cell57 = row5.CreateCell(7); cell57.SetCellValue("最小"); cell57.CellStyle = style; NPOI.SS.UserModel.ICell cell58 = row5.CreateCell(8); cell58.SetCellValue("最大"); cell58.CellStyle = style; NPOI.SS.UserModel.ICell cell59 = row5.CreateCell(9); cell59.SetCellValue("最小"); cell59.CellStyle = style; NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(6); NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1); cell61.CellStyle = style; NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2); cell62.SetCellValue("平均"); cell62.CellStyle = style; NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3); cell63.CellStyle = style; NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4); cell64.SetCellValue("平均"); cell64.CellStyle = style; NPOI.SS.UserModel.ICell cell65 = row6.CreateCell(5); cell65.CellStyle = style; NPOI.SS.UserModel.ICell cell66 = row6.CreateCell(6); cell66.SetCellValue("平均"); cell66.CellStyle = style; NPOI.SS.UserModel.ICell cell67 = row6.CreateCell(7); cell67.CellStyle = style; NPOI.SS.UserModel.ICell cell68 = row6.CreateCell(8); cell68.SetCellValue("平均"); cell68.CellStyle = style; NPOI.SS.UserModel.ICell cell69 = row6.CreateCell(9); cell69.CellStyle = style; NPOI.SS.UserModel.IRow row7 = sheet.CreateRow(7); NPOI.SS.UserModel.ICell cell70 = row7.CreateCell(0); cell70.CellStyle = style; NPOI.SS.UserModel.ICell cell71 = row7.CreateCell(1); cell71.CellStyle = style; NPOI.SS.UserModel.ICell cell72 = row7.CreateCell(2); cell72.SetCellValue(zhengmianmax); cell72.CellStyle = style; NPOI.SS.UserModel.ICell cell73 = row7.CreateCell(3); cell73.SetCellValue(zhengmianmin); cell73.CellStyle = style; NPOI.SS.UserModel.ICell cell74 = row7.CreateCell(4); cell74.SetCellValue(shangbumax); cell74.CellStyle = style; NPOI.SS.UserModel.ICell cell75 = row7.CreateCell(5); cell75.SetCellValue(shangbumin); cell75.CellStyle = style; NPOI.SS.UserModel.ICell cell76 = row7.CreateCell(6); cell76.SetCellValue(zhongbumax); cell76.CellStyle = style; NPOI.SS.UserModel.ICell cell77 = row7.CreateCell(7); cell77.SetCellValue(zhongbumin); cell77.CellStyle = style; NPOI.SS.UserModel.ICell cell78 = row7.CreateCell(8); cell78.SetCellValue(xiabumax); cell78.CellStyle = style; NPOI.SS.UserModel.ICell cell79 = row7.CreateCell(9); cell79.SetCellValue(xiabumin); cell79.CellStyle = style; NPOI.SS.UserModel.IRow row8 = sheet.CreateRow(8); NPOI.SS.UserModel.ICell cell80 = row8.CreateCell(0); cell80.CellStyle = style; NPOI.SS.UserModel.ICell cell81 = row8.CreateCell(1); cell81.CellStyle = style; NPOI.SS.UserModel.ICell cell82 = row8.CreateCell(2); cell82.SetCellValue(zhengmianavg); cell82.CellStyle = style; NPOI.SS.UserModel.ICell cell83 = row8.CreateCell(3); cell83.CellStyle = style; NPOI.SS.UserModel.ICell cell84 = row8.CreateCell(4); cell84.SetCellValue(shangbuavg); cell84.CellStyle = style; NPOI.SS.UserModel.ICell cell85 = row8.CreateCell(5); cell85.CellStyle = style; NPOI.SS.UserModel.ICell cell86 = row8.CreateCell(6); cell86.SetCellValue(zhongbuavg); cell86.CellStyle = style; NPOI.SS.UserModel.ICell cell87 = row8.CreateCell(7); cell87.CellStyle = style; NPOI.SS.UserModel.ICell cell88 = row8.CreateCell(8); cell88.SetCellValue(xiabuavg); cell88.CellStyle = style; NPOI.SS.UserModel.ICell cell89 = row8.CreateCell(9); cell89.CellStyle = style; //第5行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 0, 0)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 1, 1)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 2, 3)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 4, 5)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 6, 7)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 8, 9)); NPOI.SS.UserModel.IRow row9 = sheet.CreateRow(9); NPOI.SS.UserModel.ICell cell90 = row9.CreateCell(0); cell90.SetCellValue("分机编号"); cell90.CellStyle = style; NPOI.SS.UserModel.ICell cell91 = row9.CreateCell(1); cell91.SetCellValue("支架编号"); cell91.CellStyle = style; NPOI.SS.UserModel.ICell cell92 = row9.CreateCell(2); cell92.SetCellValue("安装位置"); cell92.CellStyle = style; NPOI.SS.UserModel.ICell cell93 = row9.CreateCell(3); cell93.CellStyle = style; NPOI.SS.UserModel.ICell cell94 = row9.CreateCell(4); cell94.SetCellValue("工作阻力"); cell94.CellStyle = style; NPOI.SS.UserModel.ICell cell95 = row9.CreateCell(5); cell95.CellStyle = style; NPOI.SS.UserModel.ICell cell96 = row9.CreateCell(6); cell96.SetCellValue("初撑力"); cell96.CellStyle = style; NPOI.SS.UserModel.ICell cell97 = row9.CreateCell(7); cell97.CellStyle = style; NPOI.SS.UserModel.ICell cell98 = row9.CreateCell(8); cell98.SetCellValue("末阻力"); cell98.CellStyle = style; NPOI.SS.UserModel.ICell cell99 = row9.CreateCell(9); cell99.CellStyle = style; NPOI.SS.UserModel.IRow row10 = sheet.CreateRow(10); NPOI.SS.UserModel.ICell cell100 = row10.CreateCell(0); cell100.CellStyle = style; NPOI.SS.UserModel.ICell cell101 = row10.CreateCell(1); cell101.CellStyle = style; NPOI.SS.UserModel.ICell cell102 = row10.CreateCell(2); cell102.CellStyle = style; NPOI.SS.UserModel.ICell cell103 = row10.CreateCell(3); cell103.CellStyle = style; NPOI.SS.UserModel.ICell cell104 = row10.CreateCell(4); cell104.SetCellValue("最大"); cell104.CellStyle = style; NPOI.SS.UserModel.ICell cell105 = row10.CreateCell(5); cell105.SetCellValue("平均"); cell105.CellStyle = style; NPOI.SS.UserModel.ICell cell106 = row10.CreateCell(6); cell106.SetCellValue("最大"); cell106.CellStyle = style; NPOI.SS.UserModel.ICell cell107 = row10.CreateCell(7); cell107.SetCellValue("平均"); cell107.CellStyle = style; NPOI.SS.UserModel.ICell cell108 = row10.CreateCell(8); cell108.SetCellValue("最大"); cell108.CellStyle = style; NPOI.SS.UserModel.ICell cell109 = row10.CreateCell(9); cell109.SetCellValue("平均"); cell109.CellStyle = style; // string jiancefenxi = "支架编号:"; for (int i = 11; i < ds.Tables[0].Rows.Count + 11; i++) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, 2, 3)); NPOI.SS.UserModel.IRow row11 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell110 = row11.CreateCell(0); cell110.SetCellValue(ds.Tables[0].Rows[i - 11]["sensorNo"].ToString()); cell110.CellStyle = style; NPOI.SS.UserModel.ICell cell111 = row11.CreateCell(1); cell111.SetCellValue(ds.Tables[0].Rows[i - 11]["bracketno"].ToString()); cell111.CellStyle = style; NPOI.SS.UserModel.ICell cell112 = row11.CreateCell(2); cell112.SetCellValue(ds.Tables[0].Rows[i - 11]["distance"].ToString()); cell112.CellStyle = style; NPOI.SS.UserModel.ICell cell113 = row11.CreateCell(3); cell113.CellStyle = style; NPOI.SS.UserModel.ICell cell114 = row11.CreateCell(4); cell114.SetCellValue(ds.Tables[0].Rows[i - 11]["zlmax"].ToString()); cell114.CellStyle = style; NPOI.SS.UserModel.ICell cell115 = row11.CreateCell(5); cell115.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["zlavg"].ToString()).ToString("0.00")); cell115.CellStyle = style; NPOI.SS.UserModel.ICell cell116 = row11.CreateCell(6); cell116.SetCellValue(ds.Tables[0].Rows[i - 11]["cclmax"].ToString()); cell116.CellStyle = style; NPOI.SS.UserModel.ICell cell117 = row11.CreateCell(7); cell117.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["cclavg"].ToString()).ToString("0.00")); cell117.CellStyle = style; NPOI.SS.UserModel.ICell cell118 = row11.CreateCell(8); cell118.SetCellValue(ds.Tables[0].Rows[i - 11]["mzlmax"].ToString()); cell118.CellStyle = style; NPOI.SS.UserModel.ICell cell119 = row11.CreateCell(9); cell119.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["mzlavg"].ToString()).ToString("0.00")); cell119.CellStyle = style; if (ViewState["yujingzhi"].ToString() != "0") { decimal fenxi = Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == "" ? "0.00" : ds.Tables[0].Rows[i - 11]["zlavg"].ToString()); decimal yujingzhi = Convert.ToDecimal(ViewState["yujingzhi"].ToString()); if (fenxi >= yujingzhi) { jiancefenxi += "" + ds.Tables[0].Rows[i - 11]["bracketno"].ToString() + "超压(" + fenxi.ToString("0.00") + "),"; } } } jiancefenxi += "请注意观察"; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 11, ds.Tables[0].Rows.Count + 11, 0, 9)); NPOI.SS.UserModel.IRow rowa = sheet.CreateRow(ds.Tables[0].Rows.Count + 11); NPOI.SS.UserModel.ICell cella0 = rowa.CreateCell(0); cella0.SetCellValue("监测分析结论:"); cella0.CellStyle = styleleft; NPOI.SS.UserModel.ICell cella1 = rowa.CreateCell(1); cella1.CellStyle = style; NPOI.SS.UserModel.ICell cella2 = rowa.CreateCell(2); cella2.CellStyle = style; NPOI.SS.UserModel.ICell cella3 = rowa.CreateCell(3); cella3.CellStyle = style; NPOI.SS.UserModel.ICell cella4 = rowa.CreateCell(4); cella4.CellStyle = style; NPOI.SS.UserModel.ICell cella5 = rowa.CreateCell(5); cella5.CellStyle = style; NPOI.SS.UserModel.ICell cella6 = rowa.CreateCell(6); cella6.CellStyle = style; NPOI.SS.UserModel.ICell cella7 = rowa.CreateCell(7); cella7.CellStyle = style; NPOI.SS.UserModel.ICell cella8 = rowa.CreateCell(8); cella8.CellStyle = style; NPOI.SS.UserModel.ICell cella9 = rowa.CreateCell(9); cella9.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 12, ds.Tables[0].Rows.Count + 12, 0, 9)); NPOI.SS.UserModel.IRow rowb = sheet.CreateRow(ds.Tables[0].Rows.Count + 12); rowb.Height = 2400; NPOI.SS.UserModel.ICell cellb0 = rowb.CreateCell(0); cellb0.SetCellValue("" + jiancefenxi); cellb0.CellStyle = styleleftcenter; NPOI.SS.UserModel.ICell cellb1 = rowb.CreateCell(1); cellb1.CellStyle = style; NPOI.SS.UserModel.ICell cellb2 = rowb.CreateCell(2); cellb2.CellStyle = style; NPOI.SS.UserModel.ICell cellb3 = rowb.CreateCell(3); cellb3.CellStyle = style; NPOI.SS.UserModel.ICell cellb4 = rowb.CreateCell(4); cellb4.CellStyle = style; NPOI.SS.UserModel.ICell cellb5 = rowb.CreateCell(5); cellb5.CellStyle = style; NPOI.SS.UserModel.ICell cellb6 = rowb.CreateCell(6); cellb6.CellStyle = style; NPOI.SS.UserModel.ICell cellb7 = rowb.CreateCell(7); cellb7.CellStyle = style; NPOI.SS.UserModel.ICell cellb8 = rowb.CreateCell(8); cellb8.CellStyle = style; NPOI.SS.UserModel.ICell cellb9 = rowb.CreateCell(9); cellb9.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 13, ds.Tables[0].Rows.Count + 13, 0, 9)); NPOI.SS.UserModel.IRow rowc = sheet.CreateRow(ds.Tables[0].Rows.Count + 13); NPOI.SS.UserModel.ICell cellc0 = rowc.CreateCell(0); cellc0.SetCellValue("区队意见:"); cellc0.CellStyle = styleleft; NPOI.SS.UserModel.ICell cellc1 = rowc.CreateCell(1); cellc1.CellStyle = style; NPOI.SS.UserModel.ICell cellc2 = rowc.CreateCell(2); cellc2.CellStyle = style; NPOI.SS.UserModel.ICell cellc3 = rowc.CreateCell(3); cellc3.CellStyle = style; NPOI.SS.UserModel.ICell cellc4 = rowc.CreateCell(4); cellc4.CellStyle = style; NPOI.SS.UserModel.ICell cellc5 = rowc.CreateCell(5); cellc5.CellStyle = style; NPOI.SS.UserModel.ICell cellc6 = rowc.CreateCell(6); cellc6.CellStyle = style; NPOI.SS.UserModel.ICell cellc7 = rowc.CreateCell(7); cellc7.CellStyle = style; NPOI.SS.UserModel.ICell cellc8 = rowc.CreateCell(8); cellc8.CellStyle = style; NPOI.SS.UserModel.ICell cellc9 = rowc.CreateCell(9); cellc9.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 14, ds.Tables[0].Rows.Count + 14, 0, 9)); NPOI.SS.UserModel.IRow rowd = sheet.CreateRow(ds.Tables[0].Rows.Count + 14); rowd.Height = 2400; NPOI.SS.UserModel.ICell celld0 = rowd.CreateCell(0); celld0.SetCellValue(" 领导签字:____________部门签字:____________报表人:____________"); celld0.CellStyle = styleleft; NPOI.SS.UserModel.ICell celld1 = rowd.CreateCell(1); celld1.CellStyle = style; NPOI.SS.UserModel.ICell celld2 = rowd.CreateCell(2); celld2.CellStyle = style; NPOI.SS.UserModel.ICell celld3 = rowd.CreateCell(3); celld3.CellStyle = style; NPOI.SS.UserModel.ICell celld4 = rowd.CreateCell(4); celld4.CellStyle = style; NPOI.SS.UserModel.ICell celld5 = rowd.CreateCell(5); celld5.CellStyle = style; NPOI.SS.UserModel.ICell celld6 = rowd.CreateCell(6); celld6.CellStyle = style; NPOI.SS.UserModel.ICell celld7 = rowd.CreateCell(7); celld7.CellStyle = style; NPOI.SS.UserModel.ICell celld8 = rowd.CreateCell(8); celld8.CellStyle = style; NPOI.SS.UserModel.ICell celld9 = rowd.CreateCell(9); celld9.CellStyle = style; // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public byte[] ExportXls(List <GroupOperation> list, GroupByEnum groupBy) { NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Plan 1"); int rowNumer = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumer); NPOI.SS.UserModel.ICell cell; NPOI.SS.UserModel.IFont hFont = workbook.CreateFont(); hFont.FontHeightInPoints = 12; hFont.FontName = "Arial"; NPOI.SS.UserModel.ICellStyle styleHeader = workbook.CreateCellStyle(); styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleHeader.SetFont(hFont); NPOI.SS.UserModel.ICellStyle styleDisabled = workbook.CreateCellStyle(); styleDisabled.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; styleDisabled.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; styleDisabled.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; var listColumns = new List <ExportColumnInfo>(); listColumns.Add(new ExportColumnInfo() { Name = groupBy.GetDescription() }); listColumns.Add(new ExportColumnInfo() { Name = "Quantidade" }); listColumns.Add(new ExportColumnInfo() { Name = "Preço Médio" }); for (int i = 0; i < listColumns.Count; i++) { cell = row.CreateCell(i); cell.SetCellValue(listColumns[i].Name); cell.CellStyle = styleHeader; } //---- row foreach (var item in list) { rowNumer++; row = sheet.CreateRow(rowNumer); row.CreateCell(0).SetCellValue(item.AccountNumber ?? item.Active ?? item.OperationType); row.CreateCell(1).SetCellValue(item.Quantity); row.CreateCell(2).SetCellValue((double)item.AveragePrice); } for (int i = 0; i < listColumns.Count; i++) { sheet.AutoSizeColumn(i); } byte[] byteArray; using (System.IO.MemoryStream stream = new System.IO.MemoryStream()) { workbook.Write(stream); byteArray = stream.ToArray(); } return(byteArray); }
private void btnExportExcel_Click(object sender, EventArgs e) { if (sfdReporters.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { //输出的Excel路径 string excelFile = sfdReporters.FileName; //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion #region 生成详细页 //创建一张数据表 DataTable dt = new DataTable(); #region 添加列字段 dt.Columns.Add("领域", typeof(string)); dt.Columns.Add("技术方向代码", typeof(string)); dt.Columns.Add("技术方向名称", typeof(string)); dt.Columns.Add("项目名称", typeof(string)); dt.Columns.Add("密级", typeof(string)); dt.Columns.Add("关键词", typeof(string)); dt.Columns.Add("单位名称", typeof(string)); dt.Columns.Add("单位常用名", typeof(string)); dt.Columns.Add("联系人", typeof(string)); dt.Columns.Add("联系电话", typeof(string)); dt.Columns.Add("项目负责人", typeof(string)); dt.Columns.Add("项目负责人电话", typeof(string)); dt.Columns.Add("身份证号", typeof(string)); dt.Columns.Add("通信地址", typeof(string)); dt.Columns.Add("研究周期", typeof(string)); dt.Columns.Add("研究经费", typeof(string)); dt.Columns.Add("研究目标", typeof(string)); dt.Columns.Add("项目摘要", typeof(string)); dt.Columns.Add("基础性问题", typeof(string)); string[] chsNumbers = new string[] { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十" }; for (int k = 0; k < 10; k++) { dt.Columns.Add("课题" + chsNumbers[k] + "名称", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "密级", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "负责人", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "身份证号", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "承担单位名称", typeof(string)); dt.Columns.Add("课题" + chsNumbers[k] + "研究经费", typeof(string)); } dt.Columns.Add("项目负责人具体情况", typeof(string)); dt.Columns.Add("与有关计划关系", typeof(string)); #endregion //拼凑数据 List <Catalog> nowCatalogs = ConnectionManager.Context.table("Catalog").select("*").getList <Catalog>(new Catalog()); foreach (Catalog catalog in nowCatalogs) { //项目信息 Project mainProj = ConnectionManager.Context.table("Project").where ("CatalogID = '" + catalog.CatalogID + "' and Type='项目'").select("*").getItem <Project>(new Project()); //承担单位 Unit mainUnit = ConnectionManager.Context.table("Unit").where ("ID = '" + mainProj.UnitID + "' and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Unit>(new Unit()); //项目负责人 Person mainPerson = ConnectionManager.Context.table("Person").where ("ID in (select PersonID from Task where Type = '项目' and CatalogID = '" + catalog.CatalogID + "') and CatalogID = '" + catalog.CatalogID + "'").select("*").getItem <Person>(new Person()); if (string.IsNullOrEmpty(mainProj.ID) || string.IsNullOrEmpty(mainUnit.ID) || string.IsNullOrEmpty(mainPerson.ID)) { MessageBox.Show("对不起,项目(" + catalog.ProjectName + ")信息不全无法改出!"); //信息不全 continue; } else { //拼装每个项目的信息 List <object> cells = new List <object>(); cells.Add(mainProj.Domain); cells.Add(mainProj.DirectionCode); cells.Add(mainProj.Direction); cells.Add(mainProj.Name); cells.Add(mainProj.SecretLevel); cells.Add(mainProj.Keywords); cells.Add(mainUnit.UnitName); cells.Add(mainUnit.NormalName); cells.Add(mainUnit.ContactName); cells.Add(mainUnit.Telephone); cells.Add(mainPerson.Name); cells.Add(mainPerson.MobilePhone); cells.Add(mainPerson.IDCard); cells.Add(mainPerson.Address); cells.Add(mainProj.TotalTime); cells.Add(mainProj.TotalMoney); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_4.rtf")); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_0.rtf")); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_5.rtf")); //创建课题信息 List <Project> subjectList = ConnectionManager.Context.table("Project").where ("Type='课题' and CatalogID = '" + catalog.CatalogID + "'").select("*").getList <Project>(new Project()); foreach (Project sub in subjectList) { Unit subUnits = ConnectionManager.Context.table("Unit").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + sub.UnitID + "'").select("*").getItem <Unit>(new Unit()); Task subTasks = ConnectionManager.Context.table("Task").where ("Role = '负责人' and CatalogID = '" + catalog.CatalogID + "' and ProjectID = '" + sub.ID + "'").select("*").getItem <Task>(new Task()); if (string.IsNullOrEmpty(subUnits.ID) || string.IsNullOrEmpty(subTasks.ID)) { MessageBox.Show("对不起,项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全!"); //信息不全 continue; } else { Person subPersons = ConnectionManager.Context.table("Person").where ("CatalogID = '" + catalog.CatalogID + "' and ID = '" + subTasks.PersonID + "'").select("*").getItem <Person>(new Person()); if (string.IsNullOrEmpty(subPersons.ID)) { MessageBox.Show("对不起,项目(" + catalog.ProjectName + ")中的课题(" + sub.Name + ")信息不全!"); //信息不全 continue; } else { cells.Add(sub.Name); cells.Add(sub.SecretLevel); cells.Add(subPersons.Name); cells.Add(subPersons.IDCard); cells.Add(subUnits.UnitName); cells.Add(subTasks.TotalMoney); } } } //给其它的课题信息项填充空格 for (int kk = 0; kk < (60 - (subjectList.Count * 6)); kk++) { cells.Add(string.Empty); } cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_14.rtf")); cells.Add(getRTFContent(catalog.CatalogNumber, "rtpinput_19.rtf")); dt.Rows.Add(cells.ToArray()); } } writeSheet(workbook, cellStyleA, cellStyleB, dt); #endregion //输出到Excel文件 workbook.Write(memoryStream); File.WriteAllBytes(excelFile, memoryStream.ToArray()); MessageBox.Show("导出完成!路径:" + excelFile, "提示"); //打开Excel文件 System.Diagnostics.Process.Start(excelFile); } catch (Exception ex) { MessageBox.Show("对不起,导出失败!Ex:" + ex.ToString()); } } }
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style) { NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String); cell.SetCellValue(strValue); cell.CellStyle = style; }
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style = null) { NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String); cell.SetCellValue(strValue); if (style != null) { cell.CellStyle = style; } else { cell.CellStyle.BorderBottom = 0; } }
public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName) { NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet c = wb.CreateSheet(tableName); List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>(); foreach (DataGridViewColumn i in dataGridView1.Columns) { if (i.Visible == true) { ListColumns.Add(i); } } if (dataGridView1.Rows.Count <= 0) { return(false); } foreach (DataGridViewColumn dc in ListColumns) { if (dc.Visible == false) { continue; } if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double)) { c.SetColumnWidth(dc.Index, 10 * 256); } else { c.SetColumnWidth(dc.Index, 20 * 256); } } #region 表头 NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0); var FirstCell = RowHeader.CreateCell(0); FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName); NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle(); cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cellstyleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cellstyleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cellstyleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont(); CellFontHeader.FontName = "微软雅黑"; CellFontHeader.FontHeightInPoints = 16; cellstyleHeader.SetFont(CellFontHeader); cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; FirstCell.CellStyle = cellstyleHeader; c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1)); #endregion #region 标题行 居中并且有框线 NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle(); CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleTitles.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1); int cindex = 0; foreach (DataGridViewColumn hc in ListColumns) { if (!hc.Visible) { continue; } NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex); cindex++; cell.CellStyle = CellStyleTitles; if (!string.IsNullOrEmpty(hc.HeaderText)) { cell.SetCellValue(hc.HeaderText); } } #endregion #region 列表 有框线,默认左对齐 NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle(); CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleLeftAlignmentCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleLeftAlignmentCell.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; CellStyleLeftAlignmentCell.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; foreach (DataGridViewRow i in dataGridView1.Rows) { NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2); cindex = 0; foreach (DataGridViewCell col in i.Cells) { if (!col.Visible) { continue; } NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex); cindex++; //设置居中对齐,如果是string则左对齐 if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid)) { xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格 } else { xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格,与标题栏一致 } if (col.Value == null) { continue; } if (col.ValueType == typeof(string)) { xcell.SetCellValue(col.Value.ToString()); } else if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double)) { xcell.SetCellValue(double.Parse(col.Value.ToString())); } else { xcell.SetCellValue(col.Value.ToString()); } } } #endregion using (SaveFileDialog sfd = new SaveFileDialog()) { sfd.Filter = "Excel电子表格|*.xls"; sfd.FileName = tableName + DateTime.Now.Ticks.ToString(); if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName)) { wb.Write(fs); MessageBox.Show("导出成功!"); } } catch (System.IO.IOException ex) { MessageBox.Show("导出失败!\n" + ex.Message); return(false); } } } return(true); }
protected void btnExport_Click(object sender, EventArgs e) { //http://www.itnose.net/detail/476834.html //http://blog.csdn.net/gjban/article/details/39030669 var list = GetData(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_workplan.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("工作计划表"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; // 内容 int i = 3; foreach (var o in list) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(i - 2); NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.SheepNo); NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2); cell2.CellStyle = style; cell2.SetCellValue(o.Project.Name); NPOI.SS.UserModel.ICell cell3 = row2.CreateCell(3); cell3.CellStyle = style; cell3.SetCellValue(o.WorkRemark); NPOI.SS.UserModel.ICell cell4 = row2.CreateCell(4); cell4.CellStyle = style; cell4.SetCellValue(o.PlanTypeStr); NPOI.SS.UserModel.ICell cell5 = row2.CreateCell(5); cell5.CellStyle = style; cell5.SetCellValue(Common.St.ToDateTimeString(o.StartTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell6 = row2.CreateCell(6); cell6.CellStyle = style; cell6.SetCellValue(Common.St.ToDateTimeString(o.RealStartTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell7 = row2.CreateCell(7); cell7.CellStyle = style; cell7.SetCellValue(Common.St.ToDateTimeString(o.EndTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell8 = row2.CreateCell(8); cell8.CellStyle = style; cell8.SetCellValue(Common.St.ToDateTimeString(o.RealEndTime, "yyyy-MM-dd")); NPOI.SS.UserModel.ICell cell9 = row2.CreateCell(9); cell9.CellStyle = style; cell9.SetCellValue(o.StateStr); NPOI.SS.UserModel.ICell cell10 = row2.CreateCell(10); cell10.CellStyle = style; cell10.SetCellValue(o.Needer.RealName); NPOI.SS.UserModel.ICell cell11 = row2.CreateCell(11); cell11.CellStyle = style; cell11.SetCellValue(o.Remark); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("工作计划表", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
private NPOI.SS.UserModel.ICellStyle CloneStyle() { if (myStyle == null) { myStyle = this._cell.Sheet.Workbook.CreateCellStyle(); myStyle.BorderBottom = this._cell.CellStyle.BorderBottom; myStyle.BorderLeft = this._cell.CellStyle.BorderLeft; myStyle.BorderRight = this._cell.CellStyle.BorderRight; myStyle.BorderTop = this._cell.CellStyle.BorderTop; myStyle.FillForegroundColor = this._cell.CellStyle.FillForegroundColor; myStyle.FillPattern = this._cell.CellStyle.FillPattern; } return myStyle; }
protected void btnExport_Click(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString); conn.Open(); Dal.Models.UserInfo user = (Dal.Models.UserInfo)Session["UserInfo"]; string strActivityType = Session["ActivityType"].ToString(); Dal.Models.Activity activity = BLL.Activity.GetActivity(strActivityType, conn); if (activity == null) { Response.Redirect("~/View/Mutual/HomePage.aspx"); conn.Close(); return; } Dal.Models.Prize prize = BLL.Prize.GetPrize(Convert.ToInt32(hfPrizeID.Value), conn); List <Dal.Models.VoteResult> lstVoteResult = BLL.Vote.GetVoteResult(prize.PrizeID.Value, prize.MultipleVoteRound ?? 1, conn); DataTable dtExpert = Dal.DataTableExtensions.ToDataTable(lstVoteResult); string root = System.Web.HttpContext.Current.Request.PhysicalApplicationPath; string TempletFileName = root + "Content/Template/投票.xls"; //模板文件 string ReportFileName = Server.MapPath("~/Content/Temp/ExcelTemp/" + prize.PrizeName + "投票结果.xls"); //导出文件 try { NPOI.HSSF.UserModel.HSSFWorkbook wk = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet ws = wk.CreateSheet(prize.PrizeName + "投票结果"); NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICellStyle style = wk.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; row = ws.CreateRow(0); row.HeightInPoints = 35; SetNewCell(row, 0, "排序", style); SetNewCell(row, 1, "项目编码", style); SetNewCell(row, 2, "项目名称", style); SetNewCell(row, 3, "申报单位", style); SetNewCell(row, 4, "申报奖项", style); SetNewCell(row, 5, "奖项类别", style); SetNewCell(row, 6, "投票方式", style); if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303" || ((prize.VoteType == "2202" || prize.VoteType == "2203") && prize.MultipleVoteRound == 1)) { SetNewCell(row, 7, "推荐", style); SetNewCell(row, 8, "不推荐", style); SetNewCell(row, 9, "缓评", style); SetNewCell(row, 10, "转出", style); } else if (prize.VoteType == "2202" && prize.MultipleVoteRound == 2) { SetNewCell(row, 7, "一等奖", style); SetNewCell(row, 8, "二等奖", style); SetNewCell(row, 9, "三等奖", style); } else if (prize.VoteType == "2201" && (prize.PrizeTypeCode == "0301" || prize.PrizeTypeCode == "0304")) { SetNewCell(row, 7, "一等奖", style); SetNewCell(row, 8, "二等奖", style); SetNewCell(row, 9, "三等奖", style); SetNewCell(row, 10, "不推荐", style); SetNewCell(row, 11, "缓评", style); SetNewCell(row, 12, "转出", style); } else { if (prize.MultipleVoteRound == 2) { SetNewCell(row, 7, "一等奖、二等奖", style); SetNewCell(row, 8, "三等奖", style); } else { SetNewCell(row, 7, "一等奖", style); SetNewCell(row, 8, "二等奖", style); } } for (int i = 0; i < dtExpert.Rows.Count; i++) { row = ws.CreateRow(i + 1); SetNewCell(row, 0, dtExpert.Rows[i]["Ordinal"].ToString(), style); SetNewCell(row, 1, dtExpert.Rows[i]["DeclarationCode"].ToString(), style); SetNewCell(row, 2, dtExpert.Rows[i]["DeclarationName"].ToString(), style); SetNewCell(row, 3, dtExpert.Rows[i]["OrganizationName"].ToString(), style); SetNewCell(row, 4, dtExpert.Rows[i]["PrizeName"].ToString(), style); SetNewCell(row, 5, dtExpert.Rows[i]["PrizeType"].ToString(), style); SetNewCell(row, 6, dtExpert.Rows[i]["VoteTypeText"].ToString(), style); if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303" || ((prize.VoteType == "2202" || prize.VoteType == "2203") && prize.MultipleVoteRound == 1)) { SetNewCell(row, 7, dtExpert.Rows[i]["Recommended"].ToString(), style); SetNewCell(row, 8, dtExpert.Rows[i]["NotRecommended"].ToString(), style); SetNewCell(row, 9, dtExpert.Rows[i]["SlowRating"].ToString(), style); SetNewCell(row, 10, dtExpert.Rows[i]["TurnOut"].ToString(), style); } else if (prize.VoteType == "2202" && prize.MultipleVoteRound == 2) { SetNewCell(row, 7, dtExpert.Rows[i]["FirstPrize"].ToString(), style); SetNewCell(row, 8, dtExpert.Rows[i]["SecondPrize"].ToString(), style); SetNewCell(row, 9, dtExpert.Rows[i]["ThirdPrize"].ToString(), style); } else if (prize.VoteType == "2201" && (prize.PrizeTypeCode == "0301" || prize.PrizeTypeCode == "0304")) { SetNewCell(row, 7, dtExpert.Rows[i]["FirstPrize"].ToString(), style); SetNewCell(row, 8, dtExpert.Rows[i]["SecondPrize"].ToString(), style); SetNewCell(row, 9, dtExpert.Rows[i]["ThirdPrize"].ToString(), style); SetNewCell(row, 10, dtExpert.Rows[i]["NotRecommended"].ToString(), style); SetNewCell(row, 11, dtExpert.Rows[i]["SlowRating"].ToString(), style); SetNewCell(row, 12, dtExpert.Rows[i]["TurnOut"].ToString(), style); } else { SetNewCell(row, 7, dtExpert.Rows[i]["Recommended"].ToString(), style); SetNewCell(row, 8, dtExpert.Rows[i]["NotRecommended"].ToString(), style); } } ws.ForceFormulaRecalculation = true; //先删除上一个临时文件 if (File.Exists(ReportFileName)) { FileInfo fi = new FileInfo(ReportFileName); if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1) { fi.Attributes = FileAttributes.Normal; } File.Delete(ReportFileName); } //创建临时文件写入数据 using (FileStream filess = File.OpenWrite(ReportFileName)) { wk.Write(filess); } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); return; } System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(prize.PrizeName + "投票结果.xls")); Response.AddHeader("Content-Length", filet.Length.ToString()); Response.ContentType = "application/ms-excel"; Response.WriteFile(filet.FullName); Response.End(); conn.Close(); }
protected void btnExport_Click(object sender, EventArgs e) { int year = Common.St.ToInt32(selYear.Value); var m = 12; if (year == DateTime.Today.Year) { m = DateTime.Today.Month; } var list = DAL.WorkPlanRule.Get().Where(a => a.NeederId > 0 && a.Needer.Status == 1).GroupBy(a => a.NeederId).Select(a => { return(new { Name = a.First().Needer.RealName, M1 = m < 1 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-2-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-2-1") || b.RealEndTime < DateTime.Parse(year + "-1-1"))))).Count(), M2 = m < 2 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-3-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-3-1") || b.RealEndTime < DateTime.Parse(year + "-2-1"))))).Count(), M3 = m < 3 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-4-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-4-1") || b.RealEndTime < DateTime.Parse(year + "-3-1"))))).Count(), M4 = m < 4 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-5-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-5-1") || b.RealEndTime < DateTime.Parse(year + "-4-1"))))).Count(), M5 = m < 5 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-6-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-6-1") || b.RealEndTime < DateTime.Parse(year + "-5-1"))))).Count(), M6 = m < 6 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-7-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-7-1") || b.RealEndTime < DateTime.Parse(year + "-6-1"))))).Count(), M7 = m < 7 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-8-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-8-1") || b.RealEndTime < DateTime.Parse(year + "-7-1"))))).Count(), M8 = m < 8 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-9-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-9-1") || b.RealEndTime < DateTime.Parse(year + "-8-1"))))).Count(), M9 = m < 9 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-10-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-10-1") || b.RealEndTime < DateTime.Parse(year + "-9-1"))))).Count(), M10 = m < 10 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-11-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-11-1") || b.RealEndTime < DateTime.Parse(year + "-10-1"))))).Count(), M11 = m < 11 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse(year + "-12-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse(year + "-12-1") || b.RealEndTime < DateTime.Parse(year + "-11-1"))))).Count(), M12 = m < 12 ? 0 : a.Where(b => (b.State == 1 && b.RealStartTime < DateTime.Parse((year + 1) + "-1-1")) || (b.State == 2 && (!(b.RealStartTime >= DateTime.Parse((year + 1) + "-1-1") || b.RealEndTime < DateTime.Parse(year + "-12-1"))))).Count() }); }); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_4.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read)); NPOI.SS.UserModel.ISheet sheet = book.GetSheet("测试人员负责项目数统计"); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.WrapText = true; style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 内容 int i = 1; foreach (var o in list) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(i); NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(o.Name); NPOI.SS.UserModel.ICell cell1 = row.CreateCell(1); cell1.CellStyle = style; cell1.SetCellValue(o.M1); NPOI.SS.UserModel.ICell cell2 = row.CreateCell(2); cell2.CellStyle = style; cell2.SetCellValue(o.M2); NPOI.SS.UserModel.ICell cell3 = row.CreateCell(3); cell3.CellStyle = style; cell3.SetCellValue(o.M3); NPOI.SS.UserModel.ICell cell4 = row.CreateCell(4); cell4.CellStyle = style; cell4.SetCellValue(o.M4); NPOI.SS.UserModel.ICell cell5 = row.CreateCell(5); cell5.CellStyle = style; cell5.SetCellValue(o.M5); NPOI.SS.UserModel.ICell cell6 = row.CreateCell(6); cell6.CellStyle = style; cell6.SetCellValue(o.M6); NPOI.SS.UserModel.ICell cell7 = row.CreateCell(7); cell7.CellStyle = style; cell7.SetCellValue(o.M7); NPOI.SS.UserModel.ICell cell8 = row.CreateCell(8); cell8.CellStyle = style; cell8.SetCellValue(o.M8); NPOI.SS.UserModel.ICell cell9 = row.CreateCell(9); cell9.CellStyle = style; cell9.SetCellValue(o.M9); NPOI.SS.UserModel.ICell cell10 = row.CreateCell(10); cell10.CellStyle = style; cell10.SetCellValue(o.M10); NPOI.SS.UserModel.ICell cell11 = row.CreateCell(11); cell11.CellStyle = style; cell11.SetCellValue(o.M11); NPOI.SS.UserModel.ICell cell12 = row.CreateCell(12); cell12.CellStyle = style; cell12.SetCellValue(o.M12); i++; } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("测试人员负责项目数统计", System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }