public void CreateFromReadOnlyWorkbook() { FileInfo input = XSSFTestDataSamples.GetSampleFile("sample.xlsx"); OPCPackage pkg = OPCPackage.Open(input, PackageAccess.READ); XSSFWorkbook xssf = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(xssf, 2); String sheetName = "Test SXSSF"; ISheet s = wb.CreateSheet(sheetName); for (int i = 0; i < 10; i++) { IRow r = s.CreateRow(i); r.CreateCell(0).SetCellValue(true); r.CreateCell(1).SetCellValue(2.4); r.CreateCell(2).SetCellValue("Test Row " + i); } Assert.AreEqual(10, s.LastRowNum); ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.Write(bos); wb.Dispose(); wb.Close(); xssf = new XSSFWorkbook(new ByteArrayInputStream(bos.ToByteArray())); s = xssf.GetSheet(sheetName); Assert.AreEqual(10, s.LastRowNum); Assert.AreEqual(true, s.GetRow(0).GetCell(0).BooleanCellValue); Assert.AreEqual("Test Row 9", s.GetRow(9).GetCell(2).StringCellValue); }
public void TestTemplateFile() { XSSFWorkbook workBook = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx"); SXSSFWorkbook streamingWorkBook = new SXSSFWorkbook(workBook, 10); ISheet sheet = streamingWorkBook.GetSheet("Sheet1"); for (int rowNum = 10; rowNum < 1000000; rowNum++) { IRow row = sheet.CreateRow(rowNum); for (int cellNum = 0; cellNum < 700; cellNum++) { ICell cell = row.CreateCell(cellNum); cell.SetCellValue("somEvalue"); } if (rowNum % 100 == 0) { Console.Write("."); if (rowNum % 10000 == 0) { Console.WriteLine(rowNum); } } } FileStream fos = new FileStream("C:\\temp\\streaming.xlsx", FileMode.Create, FileAccess.ReadWrite); streamingWorkBook.Write(fos); fos.Close(); streamingWorkBook.Close(); workBook.Close(); }
public void TestZipBombNotTriggeredOnUselessContent() { SXSSFWorkbook swb = new SXSSFWorkbook(null, 1, true, true); SXSSFSheet s = swb.CreateSheet() as SXSSFSheet; char[] useless = new char[32767]; Arrays.Fill(useless, ' '); for (int row = 0; row < 1; row++) { IRow r = s.CreateRow(row); for (int col = 0; col < 10; col++) { char[] prefix = HexDump.ToHex(row * 1000 + col).ToCharArray(); Arrays.Fill(useless, 0, 10, ' '); Array.Copy(prefix, 0, useless, 0, prefix.Length); String ul = new String(useless); r.CreateCell(col, CellType.String).SetCellValue(ul); ul = null; } } ByteArrayOutputStream bos = new ByteArrayOutputStream(); swb.Write(bos); swb.Dispose(); swb.Close(); }
private void WriteFile(string saveAsPath, SXSSFWorkbook wb) { //Passing SXSSFWorkbook because IWorkbook does not implement .Dispose which cleans ups temporary files. using (FileStream fs = new FileStream(saveAsPath, FileMode.Create, FileAccess.ReadWrite)) { wb.Write(fs); } wb.Dispose(); }
/// <summary> /// datatable导出 /// </summary> /// <param name="dataTable"></param> /// <param name="col"></param> public static void ToExcelByNpoi(DataTable dataTable, string col) { XSSFWorkbook work = new XSSFWorkbook(); IWorkbook workbook = new SXSSFWorkbook(work, 1000); //创建一个Sheet1 SXSSFSheet sheet1 = (SXSSFSheet)workbook.CreateSheet("Sheet1"); DataTable dt = dataTable; IRow rowhead = sheet1.CreateRow(0); string str = col; string[] strings = str.Split(','); if (!strings.Any(x => string.IsNullOrEmpty(x))) { for (int i = 0; i < strings.Length; i++) { rowhead.CreateCell(i, CellType.String).SetCellValue(strings[i]); } } else { for (int i = 0; i < dt.Columns.Count; i++) { rowhead.CreateCell(i, CellType.String).SetCellValue(dt.Columns[i].ColumnName); } } int index = 1; for (int i = 0; i < dt.Rows.Count; i++) { DataRow dtRow = dt.Rows[i]; IRow excelRow = sheet1.CreateRow(index++); for (int j = 0; j < dtRow.ItemArray.Length; j++) { excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString()); } } MemoryStream ms = new MemoryStream(); workbook.Write(ms); string title = "报表"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xlsx", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); work = null; ms.Close(); ms.Dispose(); }
public void testBug58175a() { IWorkbook wb = new SXSSFWorkbook(); try { ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(1); ICell cell = row.CreateCell(3); cell.SetCellValue("F4"); IDrawing drawing = sheet.CreateDrawingPatriarch(); ICreationHelper factory = wb.GetCreationHelper(); // When the comment box is visible, have it show in a 1x3 space IClientAnchor anchor = factory.CreateClientAnchor(); anchor.Col1 = (cell.ColumnIndex); anchor.Col2 = (cell.ColumnIndex + 1); anchor.Row1 = (row.RowNum); anchor.Row2 = (row.RowNum + 3); // Create the comment and set the text+author IComment comment = drawing.CreateCellComment(anchor); IRichTextString str = factory.CreateRichTextString("Hello, World!"); comment.String = (str); comment.Author = ("Apache POI"); /* fixed the problem as well * comment.setColumn(cell.ColumnIndex); * comment.setRow(cell.RowIndex); */ // Assign the comment to the cell cell.CellComment = (comment); FileStream out1 = new FileStream("C:\\temp\\58175.xlsx", FileMode.CreateNew, FileAccess.ReadWrite); try { wb.Write(out1); } finally { out1.Close(); } } finally { wb.Close(); } }
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements) { Document doc = commandData.Application.ActiveUIDocument.Document; // Variables to store user input List <int> listIds; string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // Variables to store log var schedSuccess = new List <string>(); var schedFail = new List <string>(); // Prompt window to collect user input using (BrowserCheckboxes customWindow = new BrowserCheckboxes(commandData)) { customWindow.ShowDialog(); listIds = customWindow.listIds; } // Check there are schedules selected if (listIds != null) { using (BrowserWindow browserWindow = new BrowserWindow()) { browserWindow.ShowDialog(); // Variables string fullPath = browserWindow.selectedPath; // CHeck that path is not empty and path is a folder if (fullPath == null || !Directory.Exists(fullPath)) { TaskDialog.Show("Warning", "No folder has been selected"); return(Result.Cancelled); } else { // Loop through each selected schedule foreach (int id in listIds) { // Extract data from schedule ViewSchedule sched = doc.GetElement(new ElementId(id)) as ViewSchedule; TableData tD = sched.GetTableData(); TableSectionData sectionData = tD.GetSectionData(SectionType.Body); int numbRows = sectionData.NumberOfRows; int numbCols = sectionData.NumberOfColumns; // Name of the file string excelPath = fullPath + @"\" + sched.Name + ".xlsx"; // Create excel file SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet(sched.Name); excelSheet.SetRandomAccessWindowSize(100); //Create a header row IRow row = excelSheet.CreateRow(0); // Define format for cells var fontStyle = workbook.CreateFont(); fontStyle.IsBold = true; fontStyle.FontHeightInPoints = 12; var titleStyle = workbook.CreateCellStyle(); titleStyle.SetFont(fontStyle); // Write to excel using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write)) { // Write content for (int i = 0; i < numbRows; i++) { row = excelSheet.CreateRow(i); for (int j = 0; j < numbCols; j++) { string content = sched.GetCellText(SectionType.Body, i, j); var cell = row.CreateCell(j); cell.SetCellValue(content); if (i == 0) { cell.CellStyle = titleStyle; } } } // Size columns excelSheet.TrackAllColumnsForAutoSizing(); for (int i = 0; i < numbCols; i++) { excelSheet.AutoSizeColumn(i); } excelSheet.UntrackAllColumnsForAutoSizing(); // Write to file try { workbook.Write(fs); // Log success export schedule name schedSuccess.Add(sched.Name); } catch { schedFail.Add(sched.Name); } } } TaskDialog.Show("Success", "The following schedules have been exported: " + string.Join("\n", schedSuccess.ToArray())); return(Result.Succeeded); } } } return(Result.Cancelled); }
public override IEnumerable <IFreeDocument> WriteData(IEnumerable <IFreeDocument> datas) { var xssfWb = new XSSFWorkbook(); var wb = new SXSSFWorkbook(xssfWb, 1000); // IWorkbook workbook = new XSSFWorkbook(); var sheet1 = xssfWb.CreateSheet("Sheet1"); var sw = File.Create(FileName); using (var dis = new DisposeHelper(() => { wb.Write(sw); sw.Close(); })) { var rowIndex = 0; PropertyNames = datas.GetKeys().ToDictionary(d => d, d => d); foreach (FreeDocument computeable in datas) { IDictionary <string, object> data = computeable.DictSerialize(); int cellIndex; if (rowIndex == 0) { var row1 = sheet1.CreateRow(rowIndex); cellIndex = 0; foreach (var o in PropertyNames) { row1.CreateCell(cellIndex).SetCellValue(o.Value); sheet1.AutoSizeColumn(cellIndex, true); cellIndex++; } rowIndex++; } cellIndex = 0; var row = sheet1.CreateRow(rowIndex); foreach (var value in PropertyNames.Select(name => data[name.Key])) { if (value is DateTime) { row.CreateCell(cellIndex).SetCellValue((value.ToString())); } else if (value is int || value is long) { row.CreateCell(cellIndex).SetCellValue(value.ToString()); } else if (value is double) { row.CreateCell(cellIndex).SetCellValue((double)value); } else if (value is string) { row.CreateCell(cellIndex).SetCellValue((string)value); } else { row.CreateCell(cellIndex).SetCellValue(value?.ToString() ?? ""); } cellIndex++; } rowIndex++; yield return(computeable); } } }
public MemoryStream GetExcelPackage() { using (var fs = new MemoryStream()) { var workbook = new SXSSFWorkbook(); workbook.RandomAccessWindowSize = 1000; var sheet = workbook.CreateSheet(SheetName) as SXSSFSheet; for (var r = 0; r < 50 + Data.Count; r++) { var newrow = sheet.CreateRow(r); for (var c = 0; c < 40; c++) { newrow.CreateCell(c); } } sheet.DefaultColumnWidth = 4; sheet.DisplayGridlines = false; //var rang = new CellRangeAddress(0, 1, 1, 10); //var merI = sheet.AddMergedRegion(rang); //var row = sheet._rows[0]; //var cell = row.Cells[1]; //var style = workbook.CreateCellStyle(); //var font = workbook.CreateFont(); //font.FontHeightInPoints = 16; //style.SetFont(font); //style.BorderTop = BorderStyle.Medium; //style.BorderLeft = BorderStyle.Medium; //style.BorderRight = BorderStyle.Medium; //cell.CellStyle = style; //cell.SetCellValue(123); //RegionUtil.SetBorderBottom(2, rang, sheet, workbook); //RegionUtil.SetBorderLeft(2, rang, sheet, workbook); //RegionUtil.SetBorderRight(2, rang, sheet, workbook); //RegionUtil.SetBorderTop(2, rang, sheet, workbook); SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", 12, false, BorderStyle.None, false, 700); SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", 10, true, BorderStyle.Medium, true); SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, 12, false, BorderStyle.None, false); SetCell(workbook, sheet, 1, 1, 1, 30, FromDepartment, 12, false, BorderStyle.None, true); //设置标题 var titleFont = workbook.CreateFont(); titleFont.FontHeightInPoints = 16; titleFont.Underline = FontUnderlineType.Single; SetCell(workbook, sheet, 16, 17, 0, 30, "adfdsf", titleFont, false, BorderStyle.None, true); //设置周期 var periodFont = workbook.CreateFont(); periodFont.FontHeightInPoints = 16; periodFont.Underline = FontUnderlineType.Single; SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), periodFont, false, BorderStyle.None, false); #region 代理商信息 SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), 12, true, BorderStyle.Thin, true); #endregion #region 代理商帐户信息 SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, 12, true, BorderStyle.Thin, true); #endregion #region 列表 SetCell(workbook, sheet, 32, 32, 1, 1, null, 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 2, 4, "コード", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); SetCell(workbook, sheet, 32, 32, 26, 29, "備考", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); var rowIndex = 33; var sn = 1; var total = 0m; foreach (var item in Data) { total += item.RateAmount; SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, 12, true, BorderStyle.Thin, true); rowIndex++; } SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), 12, true, BorderStyle.Thin, true); SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", 12, true, BorderStyle.Thin, true); #endregion workbook.Write(fs); return(fs); } }
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements) { Document doc = commandData.Application.ActiveUIDocument.Document; // Check if the open document is a Family document try { var check = doc.FamilyManager; TaskDialog.Show("Warning", "Family document opened, please open a project"); return(Result.Cancelled); } catch { // TODO: refactor Family document check } // Retrieve current date string currentDate = DateTime.Today.ToString("dd/MM/yyyy"); string[] columnNames = { "Priority", "Warning", "Element Ids", "Date Detected", "Date Solved", "Fixed by" }; string warningJSONPath = @"C:\ProgramData\Autodesk\Revit\Addins\BIMicon\WarningsReport\RevitWarningsClassified.json"; string warningsJsonString = Helpers.Helpers.WriteSafeReadAllLines(warningJSONPath); var warningsJObject = JObject.Parse(warningsJsonString); string critical = string.Join("", warningsJObject.Value <JArray>("Critical").ToObject <string[]>()); string high = string.Join("", warningsJObject.Value <JArray>("High").ToObject <string[]>()); string medium = string.Join("", warningsJObject.Value <JArray>("Medium").ToObject <string[]>()); string low = string.Join("", warningsJObject.Value <JArray>("Low").ToObject <string[]>()); IList <FailureMessage> docWarnings = doc.GetWarnings(); // Check if there is any warning in the document if (docWarnings.Count == 0) { TaskDialog.Show("Warning", "This project doesn't contain any warnings. Congratulations!"); return(Result.Succeeded); } // Store data to transfer to database List <string[]> dataTransfer = new List <string[]>(); foreach (FailureMessage failMessage in docWarnings) { string failDescription = failMessage.GetDescriptionText(); ICollection <ElementId> failWarningElementIds = failMessage.GetFailingElements(); string failElementIds = string.Join(", ", failWarningElementIds); string priorityCat = ""; if (critical.Contains(failDescription)) { priorityCat = "Critical"; } else if (high.Contains(failDescription)) { priorityCat = "High"; } else if (low.Contains(failDescription)) { priorityCat = "Low"; } else { priorityCat = "Medium"; } dataTransfer.Add(new string[] { priorityCat, failDescription, failElementIds, currentDate }); } // Path to output data string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); string excelPath = desktopPath + @"\Warnings Report.xlsx"; // Create excel file SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet("Sheet1"); excelSheet.SetRandomAccessWindowSize(100); //Create a header row IRow row = excelSheet.CreateRow(0); // Style for header var titleHeader = workbook.CreateFont(); titleHeader.FontHeightInPoints = 12; titleHeader.IsBold = true; ICellStyle boldStyle = workbook.CreateCellStyle(); boldStyle.SetFont(titleHeader); // Write to excel using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write)) { // Write header for (int i = 0; i < columnNames.Count(); i++) { var cell = row.CreateCell(i); cell.SetCellValue(columnNames[i]); cell.CellStyle = boldStyle; } // Write content for (int i = 0; i < dataTransfer.Count; i++) { int numberElements = dataTransfer[i].Count(); row = excelSheet.CreateRow(i + 1); for (int j = 0; j < numberElements; j++) { row.CreateCell(j).SetCellValue(dataTransfer[i][j]); } } // Size columns excelSheet.TrackAllColumnsForAutoSizing(); for (int i = 0; i < columnNames.Count(); i++) { if (i == 1) { excelSheet.SetColumnWidth(i, 3800); } // Autosize needs to be after column has some data excelSheet.AutoSizeColumn(i); } excelSheet.UntrackAllColumnsForAutoSizing(); // Write to file workbook.Write(fs); TaskDialog.Show("Success", "Warnings report created in: " + excelPath); } return(Result.Succeeded); }
public MemoryStream GetExcelPackage() { using (var fs = new MemoryStream()) { var workbook = new SXSSFWorkbook(); var sheet = workbook.CreateSheet(SheetName) as SXSSFSheet; workbook.RandomAccessWindowSize = 5000; sheet.DefaultColumnWidth = 4; sheet.DisplayGridlines = false; var rang = new CellRangeAddress(0, 1, 1, 10); var merI = sheet.AddMergedRegion(rang); var row = sheet.CreateRow(sheet.GetMergedRegion(merI - 1).LastRow); var cell = row.CreateCell(sheet.GetMergedRegion(merI - 1).LastColumn); cell.SetCellValue("test"); var style = workbook.CreateCellStyle(); var font = workbook.CreateFont(); font.FontHeightInPoints = 16; style.SetFont(font); cell.CellStyle = style; //SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", 12, false, BorderStyle.None, false, 700); //SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", 10, true, BorderStyle.Medium, true); //SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, 12, false, BorderStyle.None, false); //SetCell(workbook, sheet, 1, 1, 1, 30, FromDepartment, 12, false, BorderStyle.None, true); ////设置标题 //var titleFont = workbook.CreateFont(); //titleFont.FontHeightInPoints = 16; //titleFont.Underline = FontUnderlineType.Single; //SetCell(workbook, sheet, 16, 17, 0, 30, "adfdsf", titleFont, false, BorderStyle.None, true); ////设置周期 //var periodFont = workbook.CreateFont(); //periodFont.FontHeightInPoints = 16; //periodFont.Underline = FontUnderlineType.Single; //SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), periodFont, false, BorderStyle.None, true); //#region 代理商信息 //SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), 12, true, BorderStyle.Thin, true); ////SetBoard(sheet, 22, 27, 1, 13, BorderStyle.Medium); //#endregion //#region 代理商帐户信息 //SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, 12, true, BorderStyle.Thin, true); //#endregion //#region 列表 //SetCell(workbook, sheet, 32, 32, 1, 1, null, 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 2, 4, "コード", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //SetCell(workbook, sheet, 32, 32, 26, 29, "備考", 12, true, BorderStyle.Thin, true, null, HSSFColor.Rose.Index); //var rowIndex = 33; //var sn = 1; //var total = 0m; //foreach (var item in Data) //{ // total += item.RateAmount; // SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), 12, true, BorderStyle.Thin, true); // SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, 12, true, BorderStyle.Thin, true); // rowIndex++; //} //SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), 12, true, BorderStyle.Thin, true); //SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", 12, true, BorderStyle.Thin, true); //#endregion workbook.Write(fs); return(fs); } }