//UTILS================================================================================================
private XSSFWorkbook OpenXLSXFile(string fileName)
{
retry:
try
{
string newFileName = Path.GetFullPath(Path.Combine(Directory.GetCurrentDirectory(), filePath + fileName));
FileStream file = new FileStream(newFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
XSSFWorkbook workbook = new XSSFWorkbook(file);
file.Close();
//WORKAROUND: Replace values for unsupported formulas @"Utils" worksheet with the exact formula stored as string
TempUtils(workbook.GetSheet("Utils"));
workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
return(workbook);
}
catch (IOException)
{
MessageBox.Show(new Form()
{
TopMost = true
}, "Please close the test case file: " + fileName + " and click on OK.", "File Access Conflict", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
goto retry;
}
}
/// <summary>
/// Создаем файл с путешественниками
/// </summary>
/// <param name="codeMO"></param>
/// <param name="listTraveller"></param>
public static async Task CreateFileWithTravellerAsync(string codeMO, List <Traveller> listTraveller)
{
string nameMO = await GetNameMOAsync(codeMO);
PropertyInfo[] properties = new Traveller().ReturnType();
//выкидываем невалидные для имени файла символы
nameMO = string.Join("_", nameMO.Split(Path.GetInvalidFileNameChars()));
string path = $@"C:\Working\{nameMO}.xlsx";
using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICreationHelper cH = wb.GetCreationHelper();
IRow hederRow = sheet.CreateRow(0);
//заполняем первую строку с названиями полей
for (int i = 0; i < properties.Length; i++)
{
ICell cell = hederRow.CreateCell(i);
cell.CellStyle.FillBackgroundColor = 1;
cell.SetCellValue(properties[i].Name);
}
//заполняем строки с данными о прибывших
for (int i = 0; i < listTraveller.Count; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < properties.Length; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(listTraveller[i].GetType().GetProperty(properties[j].Name).GetValue(listTraveller[i], null).ToString());
}
}
wb.Write(stream);
}
}
public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
public void TestSharedFormulas_EvaluateInCell()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("49872.xlsx");
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = wb.GetSheetAt(0);
double result = 3.0;
// B3 is a master shared formula, C3 and D3 don't have the formula written in their f element.
// Instead, the attribute si for a particular cell is used to figure what the formula expression
// should be based on the cell's relative location to the master formula, e.g.
// B3: <f t="shared" ref="B3:D3" si="0">B1+B2</f>
// C3 and D3: <f t="shared" si="0"/>
// Get B3 and Evaluate it in the cell
ICell b3 = sheet.GetRow(2).GetCell(1);
Assert.AreEqual(result, Evaluator.EvaluateInCell(b3).NumericCellValue);
//at this point the master formula is gone, but we are still able to Evaluate dependent cells
ICell c3 = sheet.GetRow(2).GetCell(2);
Assert.AreEqual(result, Evaluator.EvaluateInCell(c3).NumericCellValue);
ICell d3 = sheet.GetRow(2).GetCell(3);
Assert.AreEqual(result, Evaluator.EvaluateInCell(d3).NumericCellValue);
}
public void TestEvaluateColumnGreaterThan255()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("50096.xlsx");
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
/**
* The first row simply Contains the numbers 1 - 300.
* The second row simply refers to the cell value above in the first row by a simple formula.
*/
for (int i = 245; i < 265; i++)
{
ICell cell_noformula = wb.GetSheetAt(0).GetRow(0).GetCell(i);
ICell cell_formula = wb.GetSheetAt(0).GetRow(1).GetCell(i);
CellReference ref_noformula = new CellReference(cell_noformula.RowIndex, cell_noformula.ColumnIndex);
CellReference ref_formula = new CellReference(cell_noformula.RowIndex, cell_noformula.ColumnIndex);
String fmla = cell_formula.CellFormula;
// assure that the formula refers to the cell above.
// the check below is 'deep' and involves conversion of the shared formula:
// in the sample file a shared formula in GN1 is spanned in the range GN2:IY2,
Assert.AreEqual(ref_noformula.FormatAsString(), fmla);
CellValue cv_noformula = Evaluator.Evaluate(cell_noformula);
CellValue cv_formula = Evaluator.Evaluate(cell_formula);
Assert.AreEqual(cv_noformula.NumberValue, cv_formula.NumberValue, "Wrong Evaluation result in " + ref_formula.FormatAsString());
}
}
public static MemoryStream CreateExcelFromDataTable(DataTable dt)
{
using (var stream = new MemoryStream())
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICreationHelper cH = wb.GetCreationHelper();
IRow header = sheet.CreateRow(0);
for (int h = 0; h < dt.Columns.Count; h++)
{
ICell cell = header.CreateCell(h);
cell.SetCellValue(cH.CreateRichTextString(dt.Columns[h].ColumnName));
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i].ItemArray[j].ToString()));
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
wb.Write(stream);
return(stream);
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
//add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
ICreationHelper helper = wb.GetCreationHelper();
// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();
// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
FileStream sw = File.Create("../../data/image.xlsx");
wb.Write(sw);
sw.Close();
}
void Serialize()
{
using (FileStream stream = new FileStream(@"Cust_Data.xlsx", FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ICreationHelper cH = wb.GetCreationHelper();
int k = 0;
foreach (String subSchema in subSchemas)
{
ISheet sheet = wb.CreateSheet(subSchema);
for (int i = 0; i < worksheets[k].Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
int j = 0;
foreach (Object cellVal in worksheets[k].Rows[i].ItemArray)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(worksheets[k].Rows[i].ItemArray[j].ToString()));
j++;
}
}
k++;
}
wb.Write(stream);
}
}
private void exportXLSX_Click(object sender, RoutedEventArgs e)
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
// XSSFWorkbook : *.xlsx >= Excel2007
// HSSFWorkbook : *.xls < Excel2007
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet Name");
// 所有索引都从0开始
// 合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex); //创建行
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content");
sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
rowIndex++;
// 插入图片
byte[] data = File.ReadAllBytes(@"image.jpg");
int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor;
anchor.Col1 = 10;
anchor.Row1 = 0;
XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict.Resize();
// 新建sheet
var sheet2 = workbook.CreateSheet("Sheet2");
// 更改样式
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
//保存
workbook.Write(fs);
}
txtStatus.Text = "writing xlsx successful!";
}
/// <summary>
/// 导出xls结尾的Excel数据，每个实体必须含有ExcelColNameAttr
/// </summary>
/// <param name="dataList"></param>
/// <param name="outputStream"></param>
public static void ListToExcelXlsx <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null)
{
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
ICellStyle dateCellStyle = xssfWorkbook.CreateCellStyle();
ICreationHelper creationHelper = xssfWorkbook.GetCreationHelper();
dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss");
foreach (List <T> dataList in dataListArrays)
{
if (dataList == null || dataList.Count == 0)
{
throw new Exception("数据列表为空，未能导出数据！");
}
List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName);
if (excelColInfoList.Count == 0)
{
throw new Exception("字段列表为空，不能导出数据！");
}
XSSFSheet xssfSheet = (XSSFSheet)xssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName));
XSSFRow xssfRow = (XSSFRow)xssfSheet.CreateRow(0);
ExcelCol excelCol;
int width;
int[] colWidths = new int[excelColInfoList.Count];
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(xssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
object temp;
for (int i = 0, len = dataList.Count; i < len; i++)
{
temp = dataList[i];
xssfRow = (XSSFRow)xssfSheet.CreateRow(i + 1);
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(
(XSSFCell)xssfRow.CreateCell(excelCol.ColIndex),
excelColInfo.PropertyInfo.GetValue(temp),
dateCellStyle
);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
}
UpdateColWidth(xssfSheet, colWidths);
}
xssfWorkbook.Write(outputStream);
}
private void CreateReportSheet(XSSFWorkbook workbook, string sectionName, IEnumerable <WorkItemDetail> workItems, WorkItemsReportViewModel prReport, bool includeTags = false)
{
var excelSheet = workbook.CreateSheet(sectionName);
var creationHelper = workbook.GetCreationHelper();
var hlinkstyle = workbook.CreateCellStyle();
var hlinkfont = workbook.CreateFont();
hlinkfont.Underline = FontUnderlineType.Single;
hlinkfont.Color = HSSFColor.Blue.Index;
hlinkstyle.SetFont(hlinkfont);
SetHeader(excelSheet, includeTags);
int rowIdx = 1, cellIdx;
foreach (var reportEntry in workItems)
{
cellIdx = 0;
var row = excelSheet.CreateRow(rowIdx);
var idCell = row.CreateCell(cellIdx++, CellType.String);
idCell.SetCellValue(reportEntry.WorkItemId);
var link = creationHelper.CreateHyperlink(HyperlinkType.Url);
link.Address = $"https://dynamicscrm.visualstudio.com/{reportEntry.WorkItemProject}/_workitems/edit/{reportEntry.WorkItemId}";
idCell.Hyperlink = link;
row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.WorkItemTitle);
row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.WorkItemType);
if (includeTags)
{
row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.Tags);
}
row.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(reportEntry.EstimatedToComplete);
row.CreateCell(cellIdx, CellType.Numeric).SetCellValue(reportEntry.TimeSpent);
rowIdx++;
}
var summaryRow = excelSheet.CreateRow(rowIdx);
cellIdx = 0;
summaryRow.CreateCell(cellIdx++, CellType.String).SetCellValue("Total: ");
summaryRow.CreateCell(cellIdx++, CellType.String).SetCellValue($"{prReport.GetTotalBugs(workItems)} bugs / {prReport.GetTotalTasks(workItems)} tasks");
summaryRow.CreateCell(cellIdx++, CellType.String);
if (includeTags)
{
_ = summaryRow.CreateCell(cellIdx++, CellType.String);
}
summaryRow.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(prReport.GetTotalEstimated(workItems));
summaryRow.CreateCell(cellIdx, CellType.Numeric).SetCellValue(prReport.GetTotalTimeSpent(workItems));
// AutosizeCells(excelSheet, summaryRow.Cells.Count);
}
public void TestMultisheetFormulaEval()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;
// sheet1 A1
XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 A1
cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 B1
cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet3 A1
cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet1 A2 formulae
cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");
// sheet1 A3 formulae
cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
Assert.AreEqual(3.0, cell.NumericCellValue);
cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
Assert.AreEqual(4.0, cell.NumericCellValue);
}
finally
{
wb.Close();
}
}
private void gv_CellParsing(object sender, DataGridViewCellParsingEventArgs e)
{
var gvRow = gv.Rows[e.RowIndex];
var gvCell = gv.Rows[e.RowIndex].Cells[e.ColumnIndex];
var value = e.Value.ToString();
var vitem = gvRow.Tag as VItem;
var vcell = gvCell.Tag as ICell;
var oldVal = GetCellValue(vcell);
// if using a formula
if (value.StartsWith("="))
{
vcell.SetCellType(CellType.Formula);
vcell.SetCellFormula(value.Substring(1, value.Length - 1));
}
else
{
// only consider number/string here
double v;
if (double.TryParse(value, out v))
{
vcell.SetCellType(CellType.Numeric);
vcell.SetCellValue(v);
}
else
{
vcell.SetCellValue(value.ToString());
}
}
var eval = workbook.GetCreationHelper().CreateFormulaEvaluator();
for (int i = 0; i < vitem.VCells.Count; i++)
{
// re-evaluate the row
eval.EvaluateFormulaCell(vitem.VCells[i]);
}
if (string.IsNullOrWhiteSpace(oldVal))
{
oldVal = "空值";
}
var node = treeView1.SelectedNode;
while (node.Parent != null)
{
node = node.Parent;
}
listBox1.Items.Add(new MyListBoxItem(string.Format("{0}!{1}从【{2}】改变到【{3}】\r\n", vitem.SheetName, GetCellPosition(vcell), oldVal, GetCellValue(vcell)), node));
gvCell.Style.BackColor = Color.SkyBlue;
//RefreshRow(gvRow);
ChangedCells.Add(vcell);
}
public void EvaluateInCellReturnsSameDataType()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet().CreateRow(0).CreateCell(0);
XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
XSSFCell cell = wb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;
XSSFCell same = evaluator.EvaluateInCell(cell) as XSSFCell;
//assertSame(cell, same);
Assert.AreSame(cell, same);
wb.Close();
}
public void TestOffsetWithEmpty23Arguments()
{
IWorkbook workbook = new XSSFWorkbook();
ICell cell = workbook.CreateSheet().CreateRow(0).CreateCell(0);
cell.SetCellFormula("OFFSET(B1,,)");
String value = "EXPECTED_VALUE";
ICell valueCell = cell.Row.CreateCell(1);
valueCell.SetCellValue(value);
workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
Assert.AreEqual(CellType.String, cell.CachedFormulaResultType);
Assert.AreEqual(value, cell.StringCellValue);
}
internal static void FillSingleCells(DataSet dataSet, XSSFWorkbook workBook)
{
var creationHelper = workBook.GetCreationHelper();
foreach (ISheet sheet in workBook)
{
foreach (IRow row in sheet)
{
foreach (ICell cell in row.Cells)
{
FillCell(dataSet, cell, creationHelper);
}
}
}
}
public void TestBug56655()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
setCellFormula(sheet, 0, 0, "#VALUE!");
setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
Assert.AreEqual(CellType.Error, getCell(sheet, 0, 0).CachedFormulaResultType);
Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 0).ErrorCellValue);
Assert.AreEqual(CellType.Error, getCell(sheet, 0, 1).CachedFormulaResultType);
Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 1).ErrorCellValue);
wb.Close();
}
public void SaveFileInSpecifiedPathXLSX(DataTable dt, string[] columns
, string fullPath)
{
using (FileStream stream = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
string[] headers = columns;
int columnsCount = headers.Count();
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Details");
ICreationHelper cH = wb.GetCreationHelper();
#region Cellstyle with bold and background color
XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
XSSFColor color = new XSSFColor(System.Drawing.Color.Lime);
((XSSFCellStyle)cellStyle).SetFillBackgroundColor(color);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFillForegroundColor(color);
XSSFFont yourFont = (XSSFFont)wb.CreateFont();
yourFont.FontHeightInPoints = (short)10.5;
yourFont.FontName = "Arial";
yourFont.IsBold = true;
yourFont.IsItalic = false;
cellStyle.SetFont(yourFont);
#endregion
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < columns.Length; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(columns[i]);
cell.CellStyle = cellStyle;
}
for (int i = 1; i <= dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < columnsCount; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i - 1].ItemArray[j].ToString()));
}
}
wb.Write(stream);
}
}
private void Export(string fileName, string exportDir)
{
XSSFWorkbook xssfWorkbook;
IFormulaEvaluator formulaEvaluator;
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
xssfWorkbook = new XSSFWorkbook(file);
formulaEvaluator = xssfWorkbook.GetCreationHelper().CreateFormulaEvaluator();
Dictionary<string, IFormulaEvaluator> evaluators = new Dictionary<string, IFormulaEvaluator>
{
{Path.GetFileName(fileName),formulaEvaluator }
};
foreach (string linkedFileName in xssfWorkbook.ExternalLinksTable.Select(p => $"{ExcelPath}/{p.LinkedFileName}"))
{
XSSFWorkbook linkedWorkBook;
using (FileStream linkedFile = new FileStream(linkedFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
linkedWorkBook = new XSSFWorkbook(linkedFile);
}
evaluators.Add(Path.GetFileName(linkedFileName), linkedWorkBook.GetCreationHelper().CreateFormulaEvaluator());
}
formulaEvaluator.SetupReferencedWorkbooks(evaluators);
}
string protoName = Path.GetFileNameWithoutExtension(fileName);
Log.Info($"{protoName}导表开始");
string exportPath = Path.Combine(exportDir, $"{protoName}.txt");
using (FileStream txt = new FileStream(exportPath, FileMode.Create))
using (StreamWriter sw = new StreamWriter(txt))
{
for (int i = 0; i < xssfWorkbook.NumberOfSheets; ++i)
{
ISheet sheet = xssfWorkbook.GetSheetAt(i);
if (sheet.SheetName.StartsWith("~"))
{
continue;
}
ExportSheet(sheet, sw, formulaEvaluator);
}
}
Log.Info($"{protoName}导表完成");
}
private static void SaveMoviesToXLSX(IEnumerable <IMDbResponse> movies)
{
List <string> colNames = new List <string>();
foreach (PropertyInfo prop in typeof(IMDbResponse).GetProperties())
{
colNames.Add(prop.Name);
}
using (FileStream stream = new FileStream("truefilms.xlsx", FileMode.Create))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("TrueFilms");
ICreationHelper cH = wb.GetCreationHelper();
IRow topRow = sheet.CreateRow(0);
int ic = 0;
foreach (var col in colNames)
{
ICell cell = topRow.CreateCell(ic);
cell.SetCellValue(col);
ic++;
}
int im = 1;
var properties = typeof(IMDbResponse).GetProperties();
foreach (var movie in movies)
{
IRow row = sheet.CreateRow(im);
for (int j = 0; j < colNames.Count; j++)
{
ICell cell = row.CreateCell(j);
var prop = properties.SingleOrDefault(p => string.Equals(p.Name, colNames[j]));
cell.SetCellValue(prop == null ? "" : prop.GetValue(movie)?.ToString());
}
im++;
}
wb.Write(stream);
}
}
static void CreateExportData(List <VendeurData> vendeursData, string fileFullName)
{
// https://stackoverflow.com/questions/19838743/trying-to-create-a-new-xlsx-file-using-npoi-and-write-to-it
using (FileStream stream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet(SHEET_NAME);
ICreationHelper cH = wb.GetCreationHelper();
int rowIndex = FIRST_ROW;
foreach (var vendeur in vendeursData)
{
var vendeurCourant = vendeur.VendeurName;
IRow rowVendeur = sheet.CreateRow(rowIndex);
ICell cellVendeur = rowVendeur.CreateCell(0);
cellVendeur.SetCellValue(cH.CreateRichTextString(vendeurCourant));
//merged region https://scottstoecker.wordpress.com/2011/05/18/merging-excel-cells-with-npoi/
NPOI.SS.Util.CellRangeAddress craVendeur = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 4);
sheet.AddMergedRegion(craVendeur);
rowIndex++;
foreach (var clientDataAllYears in vendeur.Clients)
{
IRow rowClient = sheet.CreateRow(rowIndex);
ICell cellClientName = rowClient.CreateCell(1);
cellClientName.SetCellValue(cH.CreateRichTextString(clientDataAllYears.Name));
NPOI.SS.Util.CellRangeAddress craClient = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 1, 3);
sheet.AddMergedRegion(craClient);
InsertCellForYear(cH, clientDataAllYears, rowClient, "2016", 4);
InsertCellForYear(cH, clientDataAllYears, rowClient, "2017", 5);
InsertCellForYear(cH, clientDataAllYears, rowClient, "2018", 6);
rowIndex++;
}
rowIndex += 2;
}
wb.Write(stream);
}
}
public void TestBug56655a()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
setCellFormula(sheet, 0, 0, "B1*C1");
sheet.GetRow(0).CreateCell(1).SetCellValue("A");
setCellFormula(sheet, 1, 0, "B1*C1");
sheet.GetRow(1).CreateCell(1).SetCellValue("A");
setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
Assert.AreEqual(CellType.Error, getCell(sheet, 0, 0).CachedFormulaResultType);
Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 0).ErrorCellValue);
Assert.AreEqual(CellType.Error, getCell(sheet, 1, 0).CachedFormulaResultType);
Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 1, 0).ErrorCellValue);
Assert.AreEqual(CellType.Error, getCell(sheet, 0, 3).CachedFormulaResultType);
Assert.AreEqual(FormulaError.VALUE.Code, getCell(sheet, 0, 3).ErrorCellValue);
wb.Close();
}
internal static void FillNamedArea(DataTable dataTable, IName areaName, XSSFWorkbook workBook)
{
if (dataTable == null)
{
return;
}
var creationHelper = workBook.GetCreationHelper();
var(area, lastIndex) = GetAreaByName(workBook, areaName);
var rowCount = dataTable.Rows.Count;
if (rowCount == 0)
{
FillRange(area, creationHelper, dataTable.Columns);
return;
}
if (rowCount == 1)
{
FillRange(area, creationHelper, dataTable.Columns, dataTable.Rows[0]);
return;
}
var(nextArea, nextIndex) = (area, lastIndex);
for (int i = 0; i < rowCount; i++)
{
if (i < rowCount - 1)
{
(nextArea, nextIndex) = CopyArea(area, lastIndex + 1);
}
FillRange(area, creationHelper, dataTable.Columns, dataTable.Rows[i]);
(area, lastIndex) = (nextArea, nextIndex);
}
}
public void TestInvalidURLs()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] invalidURLs =
{
"http:\\apache.org",
"www.apache .org",
"c:\\temp",
"\\poi"
};
foreach (String s in invalidURLs)
{
try
{
CreateHelper.CreateHyperlink(HyperlinkType.Url).Address = (s);
Assert.Fail("expected ArgumentException: " + s);
}
catch (ArgumentException)
{
}
}
}
public void SaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
pkg.Close();
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
public void TestLoadSave()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx");
ICreationHelper CreateHelper = workbook.GetCreationHelper();
Assert.AreEqual(3, workbook.NumberOfSheets);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
// Check hyperlinks
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Write out, and check
// Load up again, check all links still there
XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
sheet = (XSSFSheet)wb2.GetSheetAt(0);
// Check hyperlinks again
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Add one more, and re-check
IRow r17 = sheet.CreateRow(17);
ICell r17c = r17.CreateCell(2);
IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url);
hyperlink.Address = ("http://poi.apache.org/spreadsheet/");
hyperlink.Label = "POI SS Link";
r17c.Hyperlink = (hyperlink);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
// Save and re-load once more
XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2);
Assert.AreEqual(3, wb3.NumberOfSheets);
Assert.IsNotNull(wb3.GetSheetAt(0));
Assert.IsNotNull(wb3.GetSheetAt(1));
Assert.IsNotNull(wb3.GetSheetAt(2));
sheet = (XSSFSheet)wb3.GetSheetAt(0);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
}
public ActionResult RelatorioEntradasFiltro(DateTime txt_data_inicio, DateTime txt_data_fim, int cbx_tipo)
{
//cbx_tipo = 1, significa gerar PDF
//cbx_tipo = 2, significa gerar XLSX
if (cbx_tipo == 1)
{
//Criando o PDF
var relatorioPDF = new ViewAsPdf
{
ViewName = "RelatorioEntradasPDF",
IsGrayScale = false,
FileName = "RelatorioEntradasPDF.pdf",
Model = EntradaESaidaRelatorioDao.GetEntradasRelatorioFiltro(txt_data_inicio, txt_data_fim)
};
return(relatorioPDF);
}
else
{
List <EntradaGraficos> entradas = EntradaESaidaRelatorioDao.GetEntradasRelatorioFiltro(txt_data_inicio, txt_data_fim);
//Criando o XLSX
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Relatório Entradas");
int rowNumer = 0;
//---- HEADER
IRow row = sheet.CreateRow(rowNumer);
ICell cellData;
ICell cellTotal;
ICellStyle styleData = workbook.CreateCellStyle();
styleData.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;
styleData.Alignment = HorizontalAlignment.Center;
ICellStyle styleTotal = workbook.CreateCellStyle();
styleTotal.Alignment = HorizontalAlignment.Right;
ICreationHelper createHelper = workbook.GetCreationHelper();
styleTotal.DataFormat = (createHelper.CreateDataFormat().GetFormat("R$ #.##0;-R$ #.##0"));
cellData = row.CreateCell(0);
cellData.SetCellValue("Data");
cellData.CellStyle = styleData;
cellTotal = row.CreateCell(1);
cellTotal.SetCellValue("Total");
cellTotal.CellStyle = styleTotal;
if (entradas.Count > 0)
{
foreach (var item in entradas)
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
cellData = row.CreateCell(0);
cellData.SetCellValue(item.Data.ToString("dd/MM/yyyy"));
cellData.CellStyle = styleData;
cellTotal = row.CreateCell(1);
cellTotal.SetCellValue((double)item.total);
cellTotal.CellStyle = styleTotal;
}
}
else
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
cellData = row.CreateCell(0);
cellData.SetCellValue("SEM DADOS NESSE PERIODO");
cellData.CellStyle = styleData;
cellTotal = row.CreateCell(1);
cellTotal.SetCellValue(0.00);
cellTotal.CellStyle = styleTotal;
}
//Tamanho das colunas
sheet.SetColumnWidth(0, 40 * 256);
sheet.SetColumnWidth(1, 20 * 256);
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
return(File(stream.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
string.Format("RelatorioEntradas_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")))); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
public static void ExportToExcelNopi(string FilePath, string SqlQuery)
{
DataTable dt = new DataTable();
OracleConnection conn = WebTools.GetIpmsConnection();
OracleCommand cmd = new OracleCommand(SqlQuery, conn);
// create data adapter
OracleDataAdapter da = new OracleDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dt);
// close objects
conn.Close();
da.Dispose();
cmd.Dispose();
using (FileStream stream = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICreationHelper cH = wb.GetCreationHelper();
// create header
IRow header_row = sheet.CreateRow(0); // first row = 0
// style for heading
IFont boldFont = wb.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle header_style = wb.CreateCellStyle();
header_style.SetFont(boldFont);
//boldStyle.BorderBottom = BorderStyle.Medium;
header_style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
header_style.FillPattern = FillPattern.SolidForeground;
header_style.Alignment = HorizontalAlignment.Center;
header_style.VerticalAlignment = VerticalAlignment.Center;
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = header_row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Columns[j].ColumnName));
cell.CellStyle = header_style;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
DataRow drow = dt.Rows[i];
string str_value = drow.ItemArray[j].ToString();
ICell cell = row.CreateCell(j);
if (dt.Columns[j].DataType == typeof(DateTime) && drow.ItemArray[j] != DBNull.Value)
{
cell.SetCellValue(DateTime.Now);
IDataFormat dataFormatCustom = wb.CreateDataFormat();
DateTime?date_field = drow.Field <DateTime?>(j);
//cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyyMMdd HH:mm:ss");
//DateTime.ParseExact(str_value, "dd/M/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture)
cell.SetCellValue(date_field.Value);
ICellStyle date_style = wb.CreateCellStyle();
date_style.DataFormat = dataFormatCustom.GetFormat("dd-mmm-yyyy;@");
cell.CellStyle = date_style;
}
{
cell.SetCellValue(cH.CreateRichTextString(str_value.ToString()));
}
}
}
wb.Write(stream);
}
}
public AlertMessage ExportTagihToExcel(JDatatableViewModel model)
{
AlertMessage alert = new AlertMessage();
model.Length = -1;
JDatatableResponse response = GetDatatableByQuery(model);
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Plant");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("KaCab NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("KaCab Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("ASM NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("ASM Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Collector Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AR Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AR Collector Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SPV AR Collector NIK");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SPV AR Collector Name");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <HierTagihViewModel> listData = response.Data as List <HierTagihViewModel>;
if (listData != null)
{
foreach (var item in listData)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
// RayonCode
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.RayonCode);
cell.CellStyle = cellStyleText;
// Plant
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.PlantCode);
cell.CellStyle = cellStyleNumber;
// NSM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.NSMNik);
cell.CellStyle = cellStyleNumber;
// NSM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.NSMFullname);
cell.CellStyle = cellStyleText;
// ASM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.ASMNik);
cell.CellStyle = cellStyleNumber;
// ASM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.ASMFullname);
cell.CellStyle = cellStyleText;
// FSS NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSSNik);
cell.CellStyle = cellStyleNumber;
// FSS Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSSFullname);
cell.CellStyle = cellStyleText;
// SLM NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLMNik);
cell.CellStyle = cellStyleNumber;
// SLM Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLMFullname);
cell.CellStyle = cellStyleText;
// Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.CollectorNik ?? 0);
cell.CellStyle = cellStyleNumber;
// Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.CollectorFullname);
cell.CellStyle = cellStyleText;
// AR Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FakturisNik ?? 0);
cell.CellStyle = cellStyleNumber;
// AR Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FakturisFullname);
cell.CellStyle = cellStyleText;
// SPV AR Collector NIK
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SPVFakturisNik ?? 0);
cell.CellStyle = cellStyleNumber;
// SPV AR Collector Name
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SPVFakturisFullname);
cell.CellStyle = cellStyleText;
rowIndex++;
}
}
#endregion
#region auto size column
for (int i = 0; i < 16; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
