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);
}
}
