public void TestCloneSheetMultipleTimes() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); cell.SetCellValue(new HSSFRichTextString("Clone_Test")); //Clone the sheet multiple times workbook.CloneSheet(0); workbook.CloneSheet(0); Assert.IsNotNull(workbook.GetSheet("Test Clone")); Assert.IsNotNull(workbook.GetSheet("Test Clone (2)")); Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2)); Assert.IsNotNull(workbook.GetSheet("Test Clone (3)")); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.CreateSheet("abc ( 123)"); workbook.CloneSheet(0); Assert.AreEqual("abc (124)", workbook.GetSheetName(1)); }
public void TestBug50298() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls"); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); ISheet sheet = wb.CloneSheet(0); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)"); wb.SetSheetName(wb.GetSheetIndex(sheet), "copy"); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy"); wb.SetSheetOrder("copy", 0); assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received"); wb.RemoveSheetAt(0); assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); // check that the overall workbook serializes with its correct size int expected = wb.Workbook.Size; int written = wb.Workbook.Serialize(0, new byte[expected * 2]); Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected); HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb); assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received"); }
public static void SaveSOExcel(List <So> soList, List <List <SoItemsContentAndState> > soitemsListList) { if (soList.Count() != soitemsListList.Count()) { MessageBox.Show("Internal Error. Please send the log file to the Author"); Logger.Error(soList.Count() + "," + soitemsListList.Count()); return; } FileStream file; try { file = new FileStream(@"SoTemplate.dll", FileMode.Open, FileAccess.Read); } catch (Exception) { MessageBox.Show("Please check the SoTemplate.dll."); return; } hssfworkbook = new HSSFWorkbook(file); WriteDsiInfo(); for (int i = 0; i < soList.Count(); i++) { ISheet sheet = hssfworkbook.CloneSheet(0); FillTheSoSheet(sheet, soList[i], soitemsListList[i]); hssfworkbook.SetSheetName(3 + i, "SO" + i.ToString()); } hssfworkbook.RemoveSheetAt(0); hssfworkbook.RemoveSheetAt(0); hssfworkbook.RemoveSheetAt(0); WriteToFile(); }
public void TestCloneSheet() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); Cell cell2 = row.CreateCell(1); cell.SetCellValue(new HSSFRichTextString("Clone_Test")); cell2.CellFormula = ("sin(1)"); NPOI.SS.UserModel.Sheet ClonedSheet = workbook.CloneSheet(0); Row ClonedRow = ClonedSheet.GetRow(0); //Check for a good Clone Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test"); //Check that the cells are not somehow linked cell.SetCellValue(new HSSFRichTextString("Difference Check")); cell2.CellFormula = ("cos(2)"); if ("Difference Check".Equals(ClonedRow.GetCell(0).RichStringCellValue.String)) { Assert.Fail("string cell not properly Cloned"); } if ("COS(2)".Equals(ClonedRow.GetCell(1).CellFormula)) { Assert.Fail("formula cell not properly Cloned"); } Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test"); Assert.AreEqual(ClonedRow.GetCell(1).CellFormula, "SIN(1)"); }
public void TestCloneComment() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5)); c.Column = (1); c.Row = (2); c.String = (new HSSFRichTextString("qwertyuio")); HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet; HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch; HSSFComment c2 = (HSSFComment)p2.Children[0]; Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize())); Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize())); Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize())); //everything except spRecord.shapeId must be the same Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize())); EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0); sp.ShapeId = (1025); Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize())); }
public void TestCloneWithProtect() { String passwordA = "secrect"; int expectedHashA = -6810; String passwordB = "admin"; int expectedHashB = -14556; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet hssfSheet = (HSSFSheet)workbook.CreateSheet(); Assert.IsFalse(hssfSheet.ObjectProtect); hssfSheet.ProtectSheet(passwordA); Assert.IsTrue(hssfSheet.ObjectProtect); Assert.AreEqual(expectedHashA, hssfSheet.Password); Assert.AreEqual(expectedHashA, hssfSheet.Sheet.ProtectionBlock.PasswordHash); // Clone the sheet, and make sure the password hash is preserved HSSFSheet sheet2 = (HSSFSheet)workbook.CloneSheet(0); Assert.IsTrue(hssfSheet.ObjectProtect); Assert.AreEqual(expectedHashA, sheet2.Sheet.ProtectionBlock.PasswordHash); // change the password on the first sheet hssfSheet.ProtectSheet(passwordB); Assert.IsTrue(hssfSheet.ObjectProtect); Assert.AreEqual(expectedHashB, hssfSheet.Sheet.ProtectionBlock.PasswordHash); Assert.AreEqual(expectedHashB, hssfSheet.Password); // but the cloned sheet's password should remain unchanged Assert.AreEqual(expectedHashA, sheet2.Password); }
public void TestCloneSheetWithDrawings() { HSSFWorkbook wb1 = HSSFTestDataSamples.OpenSampleWorkbook("45720.xls"); HSSFSheet sheet1 = (HSSFSheet)wb1.GetSheetAt(0); wb1.Workbook.FindDrawingGroup(); DrawingManager2 dm1 = wb1.Workbook.DrawingManager; wb1.CloneSheet(0); HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1); wb2.Workbook.FindDrawingGroup(); DrawingManager2 dm2 = wb2.Workbook.DrawingManager; //Check EscherDggRecord - a workbook-level registry of drawing objects Assert.AreEqual(dm1.GetDgg().MaxDrawingGroupId + 1, dm2.GetDgg().MaxDrawingGroupId); HSSFSheet sheet2 = (HSSFSheet)wb2.GetSheetAt(1); //Check that id of the drawing Group was updated EscherDgRecord dg1 = (EscherDgRecord)(sheet1.DrawingPatriarch as HSSFPatriarch).GetBoundAggregate().FindFirstWithId(EscherDgRecord.RECORD_ID); EscherDgRecord dg2 = (EscherDgRecord)(sheet2.DrawingPatriarch as HSSFPatriarch).GetBoundAggregate().FindFirstWithId(EscherDgRecord.RECORD_ID); int dg_id_1 = dg1.Options >> 4; int dg_id_2 = dg2.Options >> 4; Assert.AreEqual(dg_id_1 + 1, dg_id_2); //TODO: Check shapeId in the Cloned sheet }
/// <summary> /// 增加一個新的表格,並將新增的表格作為活動表 /// </summary> /// <param name="sheetName">表名稱</param> public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex) { int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName)); _currentSheet = _workbook.CloneSheet(i); sheetIndex = _workbook.GetSheetIndex(_currentSheet); _workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName); _defaultExcelCellStyle = new ExcelCellStyle(); }
public void TestSheetClone() { // First up, try a simple file HSSFWorkbook b = new HSSFWorkbook(); Assert.AreEqual(0, b.NumberOfSheets); b.CreateSheet("Sheet One"); b.CreateSheet("Sheet Two"); Assert.AreEqual(2, b.NumberOfSheets); b.CloneSheet(0); Assert.AreEqual(3, b.NumberOfSheets); // Now try a problem one with drawing records in it b = OpenSample("SheetWithDrawing.xls"); Assert.AreEqual(1, b.NumberOfSheets); b.CloneSheet(0); Assert.AreEqual(2, b.NumberOfSheets); }
public void TestCloneSheetBasic() { HSSFWorkbook b = new HSSFWorkbook(); ISheet s = b.CreateSheet("Test"); s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1)); ISheet clonedSheet = b.CloneSheet(0); Assert.AreEqual(1, clonedSheet.NumMergedRegions, "One merged area"); }
private void button2_Click(object sender, EventArgs e) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("My Sheet"); ICellStyle style1 = workbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; ICellStyle style2 = workbook.CreateCellStyle(); style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2; style2.FillPattern = FillPatternType.SOLID_FOREGROUND; ICell cell = sheet.CreateRow(0).CreateCell(0); cell.CellStyle = style1; cell.SetCellValue(0); cell = sheet.CreateRow(1).CreateCell(0); cell.CellStyle = style2; cell.SetCellValue(1); cell = sheet.CreateRow(2).CreateCell(0); cell.CellStyle = style1; cell.SetCellValue(2); cell = sheet.CreateRow(3).CreateCell(0); cell.CellStyle = style2; cell.SetCellValue(3); cell = sheet.CreateRow(4).CreateCell(0); cell.CellStyle = style1; cell.SetCellValue(4); string saveFile = FileDialogHelper.SaveExcel("test.xls"); if (!string.IsNullOrEmpty(saveFile)) { SaveToFile(workbook, saveFile); if (MessageUtil.ShowYesNoAndTips("保存成功,是否打开文件?") == System.Windows.Forms.DialogResult.Yes) { System.Diagnostics.Process.Start(saveFile); } } workbook.CloneSheet(0); }
public new void Test35084() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet("Sheet1"); IRow r = s.CreateRow(0); r.CreateCell(0).SetCellValue(1); r.CreateCell(1).CellFormula = ("A1*2"); NPOI.SS.UserModel.ISheet s1 = wb.CloneSheet(0); r = s1.GetRow(0); Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 1); // sanity Check Assert.IsNotNull(r.GetCell(1)); Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2"); }
public void TestClone() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(); String formula = "7"; HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting; HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula); HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting(); patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index); HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2"); HSSFConditionalFormattingRule[] cfRules = { rule1, rule2 }; short col = 1; CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, col, col) }; sheetCF.AddConditionalFormatting(regions, cfRules); try { wb.CloneSheet(0); } catch (Exception e) { if (e.Message.IndexOf("needs to define a clone method") > 0) { throw new AssertionException("Indentified bug 45682"); } throw e; } Assert.AreEqual(2, wb.NumberOfSheets); }
public void TestPageBreakClones() { HSSFWorkbook b = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet s = b.CreateSheet("Test"); s.SetRowBreak(3); s.SetColumnBreak((short)6); NPOI.SS.UserModel.Sheet clone = b.CloneSheet(0); Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken"); Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken"); s.RemoveRowBreak(3); Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken"); }
public void TestBug57231() { HSSFWorkbook wb = HSSFTestDataSamples .OpenSampleWorkbook("57231_MixedGasReport.xls"); HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet; HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet; int idx = wb.GetSheetIndex(newSheet); wb.SetSheetName(idx, "newName"); // Write the output to a file HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.IsNotNull(wbBack); Assert.IsNotNull(wbBack.GetSheet("master")); Assert.IsNotNull(wbBack.GetSheet("newName")); }
public void TestCloneSheet() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls"); ICell cell; IHyperlink link; NPOI.SS.UserModel.ISheet sheet = wb.CloneSheet(0); cell = sheet.GetRow(4).GetCell(0); link = cell.Hyperlink; Assert.IsNotNull(link); Assert.AreEqual("http://poi.apache.org/", link.Address); cell = sheet.GetRow(8).GetCell(0); link = cell.Hyperlink; Assert.IsNotNull(link); Assert.AreEqual("http://poi.apache.org/hssf/", link.Address); }
public static EmailExtend.FileAttachment SupervisorReport(string startTime, string endTime) { using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/supervisorsheet_citizenM.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); List <SupervisorSheetModel_citizenM> ModelList = DbFunction.SupervisorListReport_citizenM(startTime, endTime); var UserList = ModelList.Select(x => x.InspectBy).Distinct().ToList(); var datenow = Convert.ToDateTime(startTime); //得到 Excel 模板的名称 为 Room 的表格 var sheet0 = (HSSFSheet)hssfworkbook.GetSheetAt(0); sheet0.GetRow(6).GetCell(3).SetCellValue(ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime), 2) + "-" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(endTime), 2)); var tabIndex = 1; for (int i = 0; i < UserList.Count; i++) { var row = 9; sheet0 = (HSSFSheet)hssfworkbook.CloneSheet(0); string sheetName = UserList[i].ToString().Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' '); hssfworkbook.SetSheetName(tabIndex, sheetName); var NodeList = ModelList.Where(x => x.InspectBy == sheetName).OrderBy(x => Convert.ToInt32(x.NodeName)).ThenBy(x => Convert.ToInt32(x.NodeName)).ToList(); for (int j = 0; j < NodeList.Count; j++) { var cleaningType = ""; switch (NodeList[j].CleaningType) { case 0: cleaningType = "Checkout"; break; case 1: cleaningType = "Stayover"; break; case 2: cleaningType = "Mandatory Stayover"; break; } sheet0.CopyRow(8, row); HSSFRow fsRow = (HSSFRow)sheet0.GetRow(row); fsRow.GetCell(0).SetCellValue(NodeList[j].NodeName); fsRow.GetCell(1).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoingTime).ToString("dd/MM HH:mm")); fsRow.GetCell(2).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnDoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(3).SetCellValue(NodeList[j].StartStatusRoom); fsRow.GetCell(4).SetCellValue(NodeList[j].EndStatusRoom); fsRow.GetCell(5).SetCellValue(cleaningType); fsRow.GetCell(6).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoingTime).ToString("dd/MM HH:mm")); fsRow.GetCell(7).SetCellValue(string.IsNullOrEmpty(NodeList[j].RoomAssnTouchUpDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].RoomAssnTouchUpDoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(8).SetCellValue(NodeList[j].DoneBy); fsRow.GetCell(9).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoingTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoingTime).ToString("dd/MM HH:mm")); fsRow.GetCell(10).SetCellValue(string.IsNullOrEmpty(NodeList[j].DoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].DoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(11).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoTime).ToString("dd/MM HH:mm")); fsRow.GetCell(12).SetCellValue(string.IsNullOrEmpty(NodeList[j].ReDoneTime.ToString()) ? "" : Convert.ToDateTime(NodeList[j].ReDoneTime).ToString("dd/MM HH:mm")); fsRow.GetCell(13).SetCellValue(NodeList[j].InspectBy); fsRow.GetCell(14).SetCellValue(NodeList[j].InspectRemark); fsRow.GetCell(15).SetCellValue(NodeList[j].InspectTouchUpRemark); fsRow.GetCell(16).SetCellValue(NodeList[j].RoomNote); fsRow.GetCell(17).SetCellValue(NodeList[j].IsSkipped == 1 ? "√" : ""); row++; } sheet0.RemoveRowAt(8); tabIndex++; } if (UserList.Count > 0) { hssfworkbook.RemoveSheetAt(0); } var fileName = SystemConfig.HotelName + " Supervisor Report_" + ExcelExtend.DatetTimeFormate(datenow) + ".xls"; var ms = new MemoryStream(); hssfworkbook.Write(ms); return(new EmailExtend.FileAttachment() { FileContent = ms.ToArray(), FileName = fileName }); } }
protected void btnImport_Click(object sender, EventArgs e) { string strPrintId = this.strPrintId.Value; string strCompanyName = "郑州市环保局"; TMisMonitorTaskVo objTask = new TMisMonitorTaskLogic().Details(strPrintId); if (objTask.CLIENT_COMPANY_ID.Length > 0) { strCompanyName = new i3.BusinessLogic.Channels.Base.Company.TBaseCompanyInfoLogic().Details(objTask.CLIENT_COMPANY_ID).COMPANY_NAME; } TMisMonitorSubtaskVo objSubTask = new TMisMonitorSubtaskVo(); objSubTask.TASK_ID = strPrintId; DataTable dtSub = new TMisMonitorSubtaskLogic().SelectByTable(objSubTask); string strSampleIDs = ""; for (int i = 0; i < dtSub.Rows.Count; i++) { GetPoint_UnderTask(dtSub.Rows[i]["ID"].ToString(), ref strSampleIDs); } //获取基本信息 DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleIDs, "021", "0"); int iPageCount = dt.Rows.Count / 7; if (dt.Rows.Count % 7 != 0) { iPageCount += 1; } FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/QHDSamplingCode.xls"), FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); //sheet复制 for (int k = 1; k < iPageCount; k++) { hssfworkbook.CloneSheet(0); hssfworkbook.SetSheetName(k, "Sheet" + (k + 1).ToString()); } for (int m = 1; m <= iPageCount; m++) { ISheet sheet = hssfworkbook.GetSheet("Sheet" + m.ToString()); sheet.GetRow(2).GetCell(0).SetCellValue("委托单位: " + strCompanyName); sheet.GetRow(2).GetCell(4).SetCellValue("任务编号: " + objTask.TICKET_NUM); DataTable dtNew = new DataTable(); dtNew = dt.Copy(); dtNew.Clear(); for (int n = (m - 1) * 7; n < m * 7; n++) { if (n >= dt.Rows.Count) { break; } dtNew.ImportRow(dt.Rows[n]); } for (int i = 0; i < dtNew.Rows.Count; i++) { sheet.GetRow(i + 4).GetCell(0).SetCellValue((i + 1).ToString()); if (objTask.SAMPLE_SOURCE == "抽样") { sheet.GetRow(i + 4).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_NAME"].ToString()); } else { sheet.GetRow(i + 4).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_NAME"].ToString()); } sheet.GetRow(i + 4).GetCell(3).SetCellValue(dtNew.Rows[i]["MONITOR_TYPE_NAME"].ToString()); sheet.GetRow(i + 4).GetCell(4).SetCellValue(dtNew.Rows[i]["ITEM_NAME"].ToString()); } } using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("水质样品交接记录表.xls", Encoding.UTF8)); curContext.Response.BinaryWrite(stream.GetBuffer()); curContext.Response.End(); } }
public static EmailExtend.FileAttachment RoomScoreReport(string startTime, string endTime) { using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/cleanscore_citizenM.xls"), FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); var ModelList = DbFunction.RoomScoreReport_citizenM(startTime, endTime); var sheetUser = (HSSFSheet)hssfworkbook.GetSheet("pm"); var summarySheet = (HSSFSheet)hssfworkbook.GetSheet("Summary"); sheetUser.GetRow(0).GetCell(18).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy")); summarySheet.GetRow(0).GetCell(1).SetCellValue(Convert.ToDateTime(startTime).ToString("dd/MM/yyyy")); int row = 2; var tabIndex = 2; var userList = ModelList.Select(x => new { x.UserID, x.UserName }).Distinct().ToList(); userList.ForEach(user => { var dataRow = 3; int copyRow = 2; sheetUser = (HSSFSheet)hssfworkbook.CloneSheet(1); string sheetName = user.UserName.Replace('/', ' ').Replace('\\', ' ').Replace('?', ' ').Replace('*', ' ').Replace('[', ' ').Replace(']', ' '); hssfworkbook.SetSheetName(tabIndex, sheetName); var RoomList = ModelList.AsParallel().Where(x => x.UserID == user.UserID).OrderBy(x => x.NodeName).ToList(); HSSFRow summaryTimeRow = (HSSFRow)summarySheet.GetRow(0); summarySheet.CopyRow(copyRow, row + 1); HSSFRow sRow = (HSSFRow)summarySheet.GetRow(row + 1); sRow.GetCell(0).SetCellValue(user.UserName); sRow.GetCell(1).SetCellValue(RoomList.Count()); sRow.GetCell(2).SetCellValue(RoomList.Sum(x => x.Credit)); RoomList.ForEach(item => { var cleaningType = ""; switch (item.CleaningType) { case 0: cleaningType = "Checkout"; break; case 1: cleaningType = "Stayover"; break; case 2: cleaningType = "Mandatory Stayover"; break; } HSSFRow userRow = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow); userRow.GetCell(0).SetCellValue(item.NodeName); userRow.GetCell(1).SetCellValue(item.StartRoomStauts); userRow.GetCell(2).SetCellValue(item.EndRoomStauts); userRow.GetCell(3).SetCellValue(cleaningType); userRow.GetCell(4).SetCellValue(item.CreatorUserName); userRow.GetCell(5).SetCellValue(item.UserName); userRow.GetCell(6).SetCellValue(item.ToDoTime); userRow.GetCell(7).SetCellValue(item.DoingTime); userRow.GetCell(8).SetCellValue(item.PauseTime); userRow.GetCell(9).SetCellValue(item.PauseReason); userRow.GetCell(10).SetCellValue(item.ResumeTime); userRow.GetCell(11).SetCellValue(item.DoneTime); userRow.GetCell(12).SetCellValue(item.CleanTime); userRow.GetCell(13).SetCellValue(item.Credit); userRow.GetCell(14).SetCellValue(item.AdditionalTaskName); userRow.GetCell(15).SetCellValue(item.AdditionalTaskCredit); userRow.GetCell(16).SetCellValue(item.TouchUpStartTime); userRow.GetCell(17).SetCellValue(item.TouchUpDoneTime); userRow.GetCell(18).SetCellValue(item.TouchUpMinutes); userRow.GetCell(19).SetCellValue(item.RoomNote); dataRow++; }); HSSFRow sheetLast = (HSSFRow)sheetUser.CopyRow(copyRow, dataRow); sheetLast.GetCell(11).SetCellValue("Total"); sheetLast.GetCell(12).SetCellValue(RoomList.Sum(x => x.Credit)); sheetUser.RemoveRowAt(2); row++; tabIndex++; }); hssfworkbook.RemoveSheetAt(1); summarySheet.RemoveRowAt(2); var fileName = SystemConfig.HotelName + " Clean Score Report_" + ExcelExtend.DatetTimeFormate(Convert.ToDateTime(startTime)) + ".xls"; var ms = new MemoryStream(); hssfworkbook.Write(ms); return(new EmailExtend.FileAttachment() { FileContent = ms.ToArray(), FileName = fileName }); } }
/// <summary> /// 根据收货出账单号生成Execl /// </summary> /// <param name="bill"></param> /// <param name="list"></param> public static void ExeclCreate(ReceivingBill bill, List <BillModel> list) { Log.Info("LMS开始生成账单号为:{0}execl表格".FormatWith(bill.ReceivingBillID)); var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new { ShippingMethodName = g.Key, TotalFee = g.Sum(p => p.Freight + p.FuelCharge + p.Register + p.Surcharge + p.TariffPrepayFee + p.SpecialFee + p.RemoteAreaFee) }).ToList(); using ( var file = new FileStream( AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open, FileAccess.Read)) { IWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheetNew = hssfworkbook.CloneSheet(0); sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称:{0}".FormatWith(bill.CustomerName)); sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间:{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd"))); IRow sourceRow = sheetNew.GetRow(2); int startRow = 2; foreach (var t in groupShipping) { startRow++; sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true); IRow row = sheetNew.CreateRow(startRow); CopyRow(sourceRow, ref row); row.GetCell(0).SetCellValue(t.ShippingMethodName); row.GetCell(1).SetCellValue("总费用:{0}".FormatWith(t.TotalFee.ToString("F2"))); } startRow++; sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true); IRow srow = sheetNew.CreateRow(startRow); CopyRow(sourceRow, ref srow); srow.GetCell(0).SetCellValue("结算人:{0}".FormatWith(bill.ReceivingBillAuditor)); startRow = startRow + 3; int startR = startRow; foreach (var b in list) { IRow row = sheetNew.CreateRow(startRow); row.CreateCell(0).SetCellValue(b.WayBillNumber); row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? ""); row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm")); row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm")); row.CreateCell(4).SetCellValue(b.TrackingNumber ?? ""); row.CreateCell(5).SetCellValue(b.ChineseName); row.CreateCell(6).SetCellValue(b.InShippingMethodName); row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4")); row.CreateCell(8).SetCellValue(b.Weight.ToString("F4")); row.CreateCell(9).SetCellValue(b.CountNumber); row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2"))); row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2"))); row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2"))); row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2"))); row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2"))); row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2"))); row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2"))); startRow++; row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")"); } IRow totalRow = sheetNew.CreateRow(startRow); for (int i = 0; i < 18; i++) { totalRow.CreateCell(i).SetCellValue(""); } startR++; totalRow.Cells[9].SetCellValue("各项计费小计:"); totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")"); totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")"); totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")"); totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")"); totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")"); totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")"); totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")"); totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")"); hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls"))) { File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls")); } var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create); hssfworkbook.RemoveSheetAt(0); hssfworkbook.Write(savefile); file.Close(); } Log.Info("LMS完成生成账单号为:{0}execl表格".FormatWith(bill.ReceivingBillID)); }
public void DataTableToExcel() { int count = this.dt.Rows.Count; int sheetCount = this.GetSheetCount(count); DateTime now = DateTime.Now; FileStream file = new FileStream(this.templetFile, FileMode.Open, FileAccess.Read);//读入excel模板 HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("Sheet1"); for (int i = 1; i < sheetCount; i++) { workbook.CloneSheet(0); } this.FillData(workbook, sheetCount); try { FileStream fs = File.Create(this.outputFile); workbook.Write(fs); fs.Close(); } catch (Exception exception) { throw exception; } //Application o = new ApplicationClass //{ // Visible = false //}; //DateTime time2 = DateTime.Now; //Workbook workBook = o.Workbooks.Open(this.templetFile, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing); //Worksheet worksheet = (Worksheet)workBook.Sheets.get_Item(1); //for (int i = 1; i < sheetCount; i++) //{ // worksheet.Copy(this.missing, workBook.Worksheets[i]); //} //this.FillData(workBook, sheetCount); //worksheet.Activate(); //try //{ // workBook.SaveAs(this.outputFile, this.missing, this.missing, this.missing, this.missing, this.missing, XlSaveAsAccessMode.xlExclusive, this.missing, this.missing, this.missing, this.missing, this.missing); // workBook.Close(null, null, null); // o.Workbooks.Close(); // o.Application.Quit(); // o.Quit(); // Marshal.ReleaseComObject(worksheet); // Marshal.ReleaseComObject(workBook); // Marshal.ReleaseComObject(o); // worksheet = null; // workBook = null; // o = null; // GC.Collect(); //} //catch (Exception exception) //{ // throw exception; //} //finally //{ // Process[] processesByName = Process.GetProcessesByName("Excel"); // foreach (Process process in processesByName) // { // DateTime startTime = process.StartTime; // if ((startTime > now) && (startTime < time2)) // { // process.Kill(); // } // } //} }
private void tsbExcel_Click(object sender, EventArgs e) { HSSFWorkbook workbook = null; using (MemoryStream ms = new MemoryStream(Resource.template)) { workbook = new HSSFWorkbook(ms); // 工作簿 } Dictionary <string, HSSFCellStyle> styleDic = new Dictionary <string, HSSFCellStyle>(); Dictionary <string, IFont> fontDic = new Dictionary <string, IFont>(); //准备sheet var sheetIndex = 0; foreach (I3ReportData rd in reportDatas.Datas) { if (sheetIndex > 0) { workbook.CloneSheet(0); } sheetIndex++; } sheetIndex = 0; foreach (I3ReportData rd in reportDatas.Datas) { string sheetName = string.IsNullOrEmpty(rd.Name) ? ("sheet" + (sheetIndex + 1).ToString()) : rd.Name; //ISheet sheet = workbook.CreateSheet(sheetName); ISheet sheet = workbook.GetSheetAt(sheetIndex); workbook.SetSheetName(sheetIndex, sheetName); //设置列宽 var colIndex = -1; foreach (I3ReportCol col in rd.Cols) { colIndex++; if (col.Width == 0) { sheet.SetColumnHidden(colIndex, true); } else { //int width = (int)Math.Ceiling(col.Width * 34.612159); //向上取整,与excel中保持一致 //34.612159是从ebiao导入excel处获取的 //在列旁边按住鼠标不动,可以显示默认单位与像素的对应关系,10单位对应85像素,即1像素对应10/85单位 (好像与excel默认字体有关) //SetColumnWidth的单位是1/256,因此1像素对应10*256/85=30.117647 double dw = (double)col.Width * (double)10 * (double)256 / (double)85; int width = (int)Math.Ceiling(dw); sheet.SetColumnWidth(colIndex, width); } } //导出数据 int rowCount = 0; foreach (I3PrintArea area in rd.PrintAreas.Dic.Values) { exportAreaToSheet(area, workbook, sheet, ref rowCount, styleDic, fontDic); } //页面设置 //列宽会有微调导致在excel中左右分页 sheet.SetMargin(MarginType.RightMargin, rd.PageSetting.PaperRightMarginMM / (float)25.4); sheet.SetMargin(MarginType.TopMargin, rd.PageSetting.PaperTopMarginMM / (float)25.4); sheet.SetMargin(MarginType.LeftMargin, rd.PageSetting.PaperLeftMarginMM / (float)25.4); sheet.SetMargin(MarginType.BottomMargin, rd.PageSetting.PaperBottomMarginMM / (float)25.4); sheet.PrintSetup.Landscape = rd.PageSetting.PaperOrientation == PaperOrientation.横向; int paperType = rd.PageSetting.GetNPOIPaperType(); if (paperType == 0) //自定义,不清楚NPOI中怎样设置,用A4 { sheet.PrintSetup.PaperSize = 9; } else if (paperType == -1) //未打到对应关系 { sheet.PrintSetup.PaperSize = 9; } else { sheet.PrintSetup.PaperSize = (short)paperType; } sheet.IsPrintGridlines = true; sheet.FitToPage = false;//默认值是true //设置锁定 sheet.ProtectSheet("{D49C4D85-F46E-456F-9C71-DB7D880B5B04}"); sheet.IsPrintGridlines = false; //不设置这个,打印时会打印出空白单元格的网络线(即使没有网格线也会打印) sheetIndex++; } string tmpFileName = getTmpFileName(); using (FileStream file = new FileStream(tmpFileName, FileMode.Create)) { workbook.Write(file); file.Close(); } I3PCUtil.CreateAndWaitProcessByEvent(null, tmpFileName, "", IntPtr.Zero, 0, 0); }
protected void btnImport_Click(object sender, EventArgs e) { string strPrintId = this.strPrintId.Value; TMisMonitorSubtaskVo objSubTask = new TMisMonitorSubtaskVo(); objSubTask.TASK_ID = strPrintId; DataTable dtSub = new TMisMonitorSubtaskLogic().SelectByTable(objSubTask); string strSampleIDs = ""; for (int i = 0; i < dtSub.Rows.Count; i++) { GetPoint_UnderTask(dtSub.Rows[i]["ID"].ToString(), ref strSampleIDs); } //获取基本信息 DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleIDs, "021", "0"); int iPageCount = dt.Rows.Count / 17; if (dt.Rows.Count % 17 != 0) { iPageCount += 1; } FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/QHDSamplingCode.xls"), FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); //sheet复制 for (int k = 1; k < iPageCount; k++) { hssfworkbook.CloneSheet(0); hssfworkbook.SetSheetName(k, "Sheet" + (k + 1).ToString()); } for (int m = 1; m <= iPageCount; m++) { ISheet sheet = hssfworkbook.GetSheet("Sheet" + m.ToString()); sheet.GetRow(23).GetCell(0).SetCellValue("样品交接数量: " + dt.Rows.Count.ToString()); sheet.GetRow(2).GetCell(0).SetCellValue(string.Format(" 采 样 日 期: {0} 年 {1} 月 {2} 日", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString())); sheet.GetRow(3).GetCell(0).SetCellValue(string.Format(" 样品交接日期: {0} 年 {1} 月 {2} 日 {3} 点 {4} 分", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Hour.ToString(), DateTime.Now.Minute.ToString())); sheet.GetRow(1).GetCell(6).SetCellValue(string.Format("第 {0} 页 共 {1} 页", m.ToString(), iPageCount.ToString())); DataTable dtNew = new DataTable(); dtNew = dt.Copy(); dtNew.Clear(); for (int n = (m - 1) * 17; n < m * 17; n++) { if (n >= dt.Rows.Count) { break; } dtNew.ImportRow(dt.Rows[n]); } for (int i = 0; i < dtNew.Rows.Count; i++) { string strItmeNum = ""; TMisMonitorResultVo objResult = new TMisMonitorResultVo(); objResult.SAMPLE_ID = dtNew.Rows[i]["ID"].ToString(); DataTable dtResult = new TMisMonitorResultLogic().SelectByTable(objResult); for (int j = 0; j < dtResult.Rows.Count; j++) { TBaseItemInfoVo objItem = new TBaseItemInfoVo(); objItem.ID = dtResult.Rows[j]["ITEM_ID"].ToString(); objItem.IS_SAMPLEDEPT = "否"; DataTable dtItem = new TBaseItemInfoLogic().SelectByTable(objItem); if (dtItem.Rows.Count > 0 && dtItem.Rows[0]["ITEM_NUM"].ToString().Length > 0) { strItmeNum += (strItmeNum.Length > 0) ? "," + dtItem.Rows[0]["ITEM_NUM"].ToString() : dtItem.Rows[0]["ITEM_NUM"].ToString(); } } sheet.GetRow(i + 6).GetCell(0).SetCellValue((i + 1).ToString()); sheet.GetRow(i + 6).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_CODE"].ToString()); sheet.GetRow(i + 6).GetCell(6).SetCellValue(strItmeNum); } } using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("水质样品交接记录表.xls", Encoding.UTF8)); curContext.Response.BinaryWrite(stream.GetBuffer()); curContext.Response.End(); } }