public void TestCloneSheetMultipleTimes() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); CreationHelper factory = workbook.GetCreationHelper(); Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); cell.SetCellValue(factory.CreateRichTextString("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 TestPrintSetupLandscapeNew() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheetL = workbook.CreateSheet("LandscapeS"); Sheet sheetP = workbook.CreateSheet("LandscapeP"); // Check two aspects of the print Setup Assert.IsFalse(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(1, sheetL.PrintSetup.Copies); Assert.AreEqual(1, sheetP.PrintSetup.Copies); // Change one on each sheetL.PrintSetup.Landscape = true; sheetP.PrintSetup.Copies = (short)3; // Check taken Assert.IsTrue(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(1, sheetL.PrintSetup.Copies); Assert.AreEqual(3, sheetP.PrintSetup.Copies); // Save and re-load, and check still there workbook = GetTestDataProvider().WriteOutAndReadBack(workbook); sheetL = workbook.GetSheet("LandscapeS"); sheetP = workbook.GetSheet("LandscapeP"); Assert.IsTrue(sheetL.PrintSetup.Landscape); Assert.IsFalse(sheetP.PrintSetup.Landscape); Assert.AreEqual(1, sheetL.PrintSetup.Copies); Assert.AreEqual(3, sheetP.PrintSetup.Copies); }
public void TestRemoveRow() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet1 = workbook.CreateSheet(); Assert.AreEqual(0, sheet1.PhysicalNumberOfRows); Assert.AreEqual(0, sheet1.FirstRowNum); Assert.AreEqual(0, sheet1.LastRowNum); Row row0 = sheet1.CreateRow(0); Assert.AreEqual(1, sheet1.PhysicalNumberOfRows); Assert.AreEqual(0, sheet1.FirstRowNum); Assert.AreEqual(0, sheet1.LastRowNum); sheet1.RemoveRow(row0); Assert.AreEqual(0, sheet1.PhysicalNumberOfRows); Assert.AreEqual(0, sheet1.FirstRowNum); Assert.AreEqual(0, sheet1.LastRowNum); sheet1.CreateRow(1); Row row2 = sheet1.CreateRow(2); Assert.AreEqual(2, sheet1.PhysicalNumberOfRows); Assert.AreEqual(1, sheet1.FirstRowNum); Assert.AreEqual(2, sheet1.LastRowNum); Assert.IsNotNull(sheet1.GetRow(1)); Assert.IsNotNull(sheet1.GetRow(2)); sheet1.RemoveRow(row2); Assert.IsNotNull(sheet1.GetRow(1)); Assert.IsNull(sheet1.GetRow(2)); Assert.AreEqual(1, sheet1.PhysicalNumberOfRows); Assert.AreEqual(1, sheet1.FirstRowNum); Assert.AreEqual(1, sheet1.LastRowNum); Row row3 = sheet1.CreateRow(3); Sheet sheet2 = workbook.CreateSheet(); try { sheet2.RemoveRow(row3); Assert.Fail("Expected exception"); } catch (ArgumentException e) { Assert.AreEqual("Specified row does not belong to this sheet", e.Message); } }
public void TestCloneSheet() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); CreationHelper factory = workbook.GetCreationHelper(); Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); Cell cell2 = row.CreateCell(1); cell.SetCellValue(factory.CreateRichTextString("Clone_test")); cell2.CellFormula = "SIN(1)"; 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(factory.CreateRichTextString("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 Render(object data) { var json = JToken.FromObject(data); IsArray = json.Type == JTokenType.Array; for (int i = 0; i < Workbook.NumberOfSheets; i++) { ISheet sheet = Workbook.GetSheetAt(i); var sheetMeta = Sheets[i]; Workbook.SetSheetName(i, sheetMeta.SheetName); RenderSheet(sheetMeta, sheet, json); } //将模版元数据放到excel文件中,便于下次读取 var templateSheet = Workbook.CreateSheet("模版元数据"); templateSheet.ProtectSheet("author:zhulin"); templateSheet.TabColorIndex = HSSFColor.Yellow.Index; var row = templateSheet.CreateRow(0); var metastr = JsonConvert.SerializeObject(this); row.CreateCell(0).SetCellValue(metastr); var sheetIdx = Workbook.GetSheetIndex(templateSheet); Workbook.SetSheetHidden(sheetIdx, SheetState.VeryHidden); }
public void TestRemoveMerged() { Workbook wb = GetTestDataProvider().CreateWorkbook(); Sheet sheet = wb.CreateSheet(); CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1); sheet.AddMergedRegion(region); region = new CellRangeAddress(1, 2, 0, 1); sheet.AddMergedRegion(region); sheet.RemoveMergedRegion(0); region = sheet.GetMergedRegion(0); Assert.AreEqual(1, region.FirstRow, "Left over region should be starting at row 1"); sheet.RemoveMergedRegion(0); Assert.AreEqual(0, sheet.NumMergedRegions, "there should be no merged regions left!"); //an, Add, Remove, Get(0) would null pointer sheet.AddMergedRegion(region); Assert.AreEqual(1, sheet.NumMergedRegions, "there should now be one merged region!"); sheet.RemoveMergedRegion(0); Assert.AreEqual(0, sheet.NumMergedRegions, "there should now be zero merged regions!"); //add it again! region.LastRow = 4; sheet.AddMergedRegion(region); Assert.AreEqual(1, sheet.NumMergedRegions, "there should now be one merged region!"); //should exist now! Assert.IsTrue(1 <= sheet.NumMergedRegions, "there isn't more than one merged region in there"); region = sheet.GetMergedRegion(0); Assert.AreEqual(4, region.LastRow, "the merged row to doesnt match the one we put in "); }
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) { int i = 0; int j = 0; int count = 0; ISheet sheet = null; Fs = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); if (FileName.IndexOf(".xlsx") > 0) // 2007版本 { Workbook = new XSSFWorkbook(); } else if (FileName.IndexOf(".xls") > 0) // 2003版本 { Workbook = new HSSFWorkbook(); } try { if (Workbook != null) { sheet = Workbook.CreateSheet(); } else { return(-1); } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } Workbook.Write(Fs); //写入到excel return(count); } catch (Exception ex) { Log4Helper.Error(typeof(ExcelManager), ex.Message, ex); return(-1); } }
public void TestOutlineProperties() { Workbook wb = GetTestDataProvider().CreateWorkbook(); Sheet sheet = wb.CreateSheet(); //TODO defaults are different in HSSF and XSSF //Assert.IsTrue(sheet.RowSumsBelow); //Assert.IsTrue(sheet.RowSumsRight); sheet.RowSumsBelow = (false); sheet.RowSumsRight = (false); Assert.IsFalse(sheet.RowSumsBelow); Assert.IsFalse(sheet.RowSumsRight); sheet.RowSumsBelow = (true); sheet.RowSumsRight = (true); Assert.IsTrue(sheet.RowSumsBelow); Assert.IsTrue(sheet.RowSumsRight); wb = GetTestDataProvider().WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0); Assert.IsTrue(sheet.RowSumsBelow); Assert.IsTrue(sheet.RowSumsRight); }
/// <summary> /// Phương thức create sheet mới trong excel và export dữ liệu trong bảng vào sheet luôn /// </summary> /// <param name="source"></param> public void CreateSheet(DataTable source) { ISheet sheet = Workbook.CreateSheet(source.TableName); // Khởi tạo row header IRow rowHeader = sheet.CreateRow(0); rowHeader.Height = RowHeaderHeight; for (int i = 0; i < source.Columns.Count; i++) { sheet.SetColumnWidth(i, ColumnWidth); rowHeader.CreateCell(i).SetCellValue(source.Columns[i].ColumnName); rowHeader.GetCell(i).CellStyle = CellStyles[CELL_HEADER_CENTER]; } // Xuất dữ liệu int x = 1; foreach (DataRow currentRow in source.Rows) { IRow row = sheet.CreateRow(x); row.Height = RowHeight; for (int j = 0; j < source.Columns.Count; j++) { row.CreateCell(j).SetCellValue(currentRow[j].ToString()); row.GetCell(j).CellStyle = CellStyles[CELL_NOMAL_LEFT]; } x = x + 1; } }
private void CrearReporteDetalles(Dictionary <int, List <UnidadBackup> > detalles_backups) { Workbook.CreateSheet("Detalles Backups"); base.CrearSheetReporteGeneral("Detalles Backups", "Detalles Backups", new string[] { "Réplica", "Id_slot", "Base", "SubFlota", "Utilización_Neta", "Minutos Recuperados", "Tramos Recuperados" }, 0); Sheet sheet = Workbook.GetSheet("Detalles Backups"); int contadorRow = 0; foreach (int replica in detalles_backups.Keys) { foreach (UnidadBackup bu in detalles_backups[replica]) { int col = _primera_columna; Cell cell = sheet.CreateRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.NumeroEntero); cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(replica); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(bu.Id); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(bu.Estacion); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(bu.TramoBase.Ac_Owner + " " + bu.TramoBase.Numero_Vuelo); col++; EstadisticosUtilizacion estadisticos = bu.Estadisticos; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(estadisticos.porcentajeUtilizacionNeta); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.NumeroEntero); cell.SetCellType(CellType.STRING); cell.SetCellValue(estadisticos.minutosRecuperados); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.NumeroEntero); cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(estadisticos.tramosRecuperados); col++; contadorRow++; } } }
public void BaseTestGetSetMargin(double[] defaultMargins) { double marginLeft = defaultMargins[0]; double marginRight = defaultMargins[1]; double marginTop = defaultMargins[2]; double marginBottom = defaultMargins[3]; double marginHeader = defaultMargins[4]; double marginFooter = defaultMargins[5]; Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet = workbook.CreateSheet("Sheet 1"); Assert.AreEqual(marginLeft, sheet.GetMargin(MarginType.LeftMargin), 0.0); sheet.SetMargin(MarginType.LeftMargin, 10.0); //left margin is custom, all others are default Assert.AreEqual(10.0, sheet.GetMargin(MarginType.LeftMargin), 0.0); Assert.AreEqual(marginRight, sheet.GetMargin(MarginType.RightMargin), 0.0); Assert.AreEqual(marginTop, sheet.GetMargin(MarginType.TopMargin), 0.0); Assert.AreEqual(marginBottom, sheet.GetMargin(MarginType.BottomMargin), 0.0); sheet.SetMargin(MarginType.RightMargin, 11.0); Assert.AreEqual(11.0, sheet.GetMargin(MarginType.RightMargin), 0.0); sheet.SetMargin(MarginType.TopMargin, 12.0); Assert.AreEqual(12.0, sheet.GetMargin(MarginType.TopMargin), 0.0); sheet.SetMargin(MarginType.BottomMargin, 13.0); Assert.AreEqual(13.0, sheet.GetMargin(MarginType.BottomMargin), 0.0); //// incorrect margin constant //try { // sheet.SetMargin((short) 65, 15); // Assert.Fail("Expected exception"); //} catch (ArgumentException e){ // Assert.AreEqual("Unknown margin constant: 65", e.GetMessage()); //} }
/// <summary> /// Saves the specified objects to the specified stream. /// </summary> /// <typeparam name="T">The type of objects to save.</typeparam> /// <param name="stream">The stream to write the objects to.</param> /// <param name="objects">The objects to save.</param> /// <param name="sheetName">Name of the sheet.</param> /// <param name="overwrite"><c>true</c> to overwrite existing content; <c>false</c> to merge.</param> /// <param name="xlsx">if set to <c>true</c> saves in .xlsx format; otherwise, saves in .xls format.</param> public void Save<T>(Stream stream, IEnumerable<T> objects, string sheetName, bool overwrite = true, bool xlsx = true) { if (Workbook == null) Workbook = xlsx ? new XSSFWorkbook() : (IWorkbook)new HSSFWorkbook(); var sheet = Workbook.GetSheet(sheetName) ?? Workbook.CreateSheet(sheetName); Save(stream, sheet, objects, overwrite); }
public void TestRowBreaks() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet = workbook.CreateSheet(); //Sheet#RowBreaks returns an empty array if no row breaks are defined Assert.IsNotNull(sheet.RowBreaks); Assert.AreEqual(0, sheet.RowBreaks.Length); sheet.SetRowBreak(1); Assert.AreEqual(1, sheet.RowBreaks.Length); sheet.SetRowBreak(15); Assert.AreEqual(2, sheet.RowBreaks.Length); Assert.AreEqual(1, sheet.RowBreaks[0]); Assert.AreEqual(15, sheet.RowBreaks[1]); sheet.SetRowBreak(1); Assert.AreEqual(2, sheet.RowBreaks.Length); Assert.IsTrue(sheet.IsRowBroken(1)); Assert.IsTrue(sheet.IsRowBroken(15)); //now remove the Created breaks sheet.RemoveRowBreak(1); Assert.AreEqual(1, sheet.RowBreaks.Length); sheet.RemoveRowBreak(15); Assert.AreEqual(0, sheet.RowBreaks.Length); Assert.IsFalse(sheet.IsRowBroken(1)); Assert.IsFalse(sheet.IsRowBroken(15)); }
public void TestColumnBreaks() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet = workbook.CreateSheet(); Assert.IsNotNull(sheet.ColumnBreaks); Assert.AreEqual(0, sheet.ColumnBreaks.Length); Assert.IsFalse(sheet.IsColumnBroken(0)); sheet.SetColumnBreak(11); Assert.IsNotNull(sheet.ColumnBreaks); Assert.AreEqual(11, sheet.ColumnBreaks[0]); sheet.SetColumnBreak(12); Assert.AreEqual(2, sheet.ColumnBreaks.Length); Assert.IsTrue(sheet.IsColumnBroken(11)); Assert.IsTrue(sheet.IsColumnBroken(12)); sheet.RemoveColumnBreak((short)11); Assert.AreEqual(1, sheet.ColumnBreaks.Length); sheet.RemoveColumnBreak((short)15); //remove non-existing Assert.AreEqual(1, sheet.ColumnBreaks.Length); sheet.RemoveColumnBreak((short)12); Assert.AreEqual(0, sheet.ColumnBreaks.Length); Assert.IsFalse(sheet.IsColumnBroken(11)); Assert.IsFalse(sheet.IsColumnBroken(12)); }
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) { Fs = new FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); if (FilePath.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本 { Workbook = new XSSFWorkbook(); } else if (FilePath.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本 { Workbook = new HSSFWorkbook(); } try { ISheet sheet; if (Workbook != null) { sheet = Workbook.CreateSheet(sheetName); } else { return(-1); } int j; int count; if (isColumnWritten) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } int i; for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } Workbook.Write(Fs); //写入到excel return(count); } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return(-1); } }
public void TestGetSetColumnHidden() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet = workbook.CreateSheet("Sheet 1"); sheet.SetColumnHidden(2, true); Assert.IsTrue(sheet.IsColumnHidden(2)); }
/// <summary> /// Saves tracked objects to the specified stream. /// </summary> /// <typeparam name="T">The type of objects to save.</typeparam> /// <param name="stream">The stream to write the objects to.</param> /// <param name="sheetIndex">Index of the sheet.</param> /// <param name="overwrite"><c>true</c> to overwrite existing content; <c>false</c> to merge.</param> /// <param name="xlsx">if set to <c>true</c> saves in .xlsx format; otherwise, saves in .xls format.</param> public void Save<T>(Stream stream, int sheetIndex = 0, bool overwrite = true, bool xlsx = true) { if (Workbook == null) Workbook = xlsx ? new XSSFWorkbook() : (IWorkbook)new HSSFWorkbook(); var sheet = Workbook.GetSheetAt(sheetIndex) ?? Workbook.CreateSheet(); Save<T>(stream, sheet, overwrite); }
public void WriteDataToExport(IEnumerable <IEnumerable <Cell> > rowData, CellFormatter formatter, string sheetName) { if (currentSheet == null) { currentSheet = workBook.CreateSheet(sheetName); } formatter.WriteDataToSheet(currentSheet, rowData); }
public void TestFooter() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet = workbook.CreateSheet("Sheet 1"); Assert.IsNotNull(sheet.Footer); sheet.Footer.Center = ("test center footer"); Assert.AreEqual("test center footer", sheet.Footer.Center); }
/// <summary> /// Put objects in the sheet with specified name. /// </summary> /// <typeparam name="T">Target object type</typeparam> /// <param name="objects">The objects to save.</param> /// <param name="sheetName">The sheet name</param> /// <param name="overwrite"><c>true</c> to overwrite existing rows; otherwise append.</param> public void Put <T>(IEnumerable <T> objects, string sheetName, bool overwrite = true) { if (Workbook == null) { Workbook = new XSSFWorkbook(); } var sheet = Workbook.GetSheet(sheetName) ?? Workbook.CreateSheet(sheetName); Put(sheet, objects, overwrite); }
/// <summary> /// Put objects in the sheet with specified name. /// </summary> /// <typeparam name="T">Target object type</typeparam> /// <param name="objects">The objects to save.</param> /// <param name="sheetName">The sheet name</param> /// <param name="overwrite"><c>true</c> to overwrite existing rows; otherwise append.</param> public void Put <T>(IEnumerable <T> objects, string sheetName, bool overwrite = true, int rowOffset = 0, bool shiftDownExistingRows = false) { if (Workbook == null) { Workbook = new XSSFWorkbook(); } var sheet = Workbook.GetSheet(sheetName) ?? Workbook.CreateSheet(sheetName); Put(sheet, objects, overwrite, rowOffset, shiftDownExistingRows); }
public void TestGetFirstLastRowNum() { Workbook workbook = GetTestDataProvider().CreateWorkbook(); Sheet sheet = workbook.CreateSheet("Sheet 1"); sheet.CreateRow(9); sheet.CreateRow(0); sheet.CreateRow(1); Assert.AreEqual(0, sheet.FirstRowNum); Assert.AreEqual(9, sheet.LastRowNum); }
private void CrearReporteDetalles(Dictionary <int, List <SlotMantenimiento> > detalles_backups) { Workbook.CreateSheet("Detalles Mantto"); base.CrearSheetReporteGeneral("Detalles Mantto", "Detalles Mantto", new string[] { "Réplica", "Id_slot", "Estación", "SubFlota", "Matrícula", "Atraso" }, 0); Sheet sheet = Workbook.GetSheet("Detalles Mantto"); int contadorRow = 0; foreach (int replica in detalles_backups.Keys) { foreach (SlotMantenimiento sm in detalles_backups[replica]) { int col = _primera_columna; Cell cell = sheet.CreateRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.NumeroEntero); cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(replica); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(sm.TramoBase.Numero_Global.ToString()); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(sm.Estacion); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.Porcentajes); cell.SetCellType(CellType.STRING); cell.SetCellValue(sm.TramoBase.Ac_Owner + " " + sm.TramoBase.Numero_Vuelo); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.NumeroEntero); cell.SetCellType(CellType.STRING); cell.SetCellValue(sm.TramoBase.Numero_Ac); col++; cell = sheet.GetRow(_primera_fila + contadorRow).CreateCell(col); cell.CellStyle = GetEstilo(EstilosTexto.NumeroEntero); cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(sm.TiempoInicioManttoRst - sm.TiempoInicioManttoPrg); col++; contadorRow++; } } }
public void TestSheetProperties() { Workbook wb = GetTestDataProvider().CreateWorkbook(); Sheet sheet = wb.CreateSheet(); Assert.IsFalse(sheet.HorizontallyCenter); sheet.HorizontallyCenter = true; Assert.IsTrue(sheet.HorizontallyCenter); sheet.HorizontallyCenter = false; Assert.IsFalse(sheet.HorizontallyCenter); Assert.IsFalse(sheet.VerticallyCenter); sheet.VerticallyCenter = true; Assert.IsTrue(sheet.VerticallyCenter); sheet.VerticallyCenter = false; Assert.IsFalse(sheet.VerticallyCenter); Assert.IsFalse(sheet.IsPrintGridlines); sheet.IsPrintGridlines = true; Assert.IsTrue(sheet.IsPrintGridlines); Assert.IsFalse(sheet.DisplayFormulas); sheet.DisplayFormulas = true; Assert.IsTrue(sheet.DisplayFormulas); Assert.IsTrue(sheet.DisplayGridlines); sheet.DisplayGridlines = false; Assert.IsFalse(sheet.DisplayGridlines); //TODO: default "guts" is different in HSSF and XSSF //Assert.IsTrue(sheet.DisplayGuts); sheet.DisplayGuts = false; Assert.IsFalse(sheet.DisplayGuts); Assert.IsTrue(sheet.DisplayRowColHeadings); sheet.DisplayRowColHeadings = false; Assert.IsFalse(sheet.DisplayRowColHeadings); //TODO: default "autobreaks" is different in HSSF and XSSF //Assert.IsTrue(sheet.Autobreaks); sheet.Autobreaks = false; Assert.IsFalse(sheet.Autobreaks); Assert.IsFalse(sheet.ScenarioProtect); //TODO: default "fit-to-page" is different in HSSF and XSSF //Assert.IsFalse(sheet.FitToPage); sheet.FitToPage = (true); Assert.IsTrue(sheet.FitToPage); sheet.FitToPage = (false); Assert.IsFalse(sheet.FitToPage); }
public void DataTableToExcel(DataTable data, string sheetName) { Fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write); if (FilePath.IndexOf(".xlsx", StringComparison.Ordinal) > 0 || FilePath.IndexOf(".xlsm", StringComparison.Ordinal) > 0) // 2007版本 { Workbook = new XSSFWorkbook(); } else if (FilePath.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本 { Workbook = new HSSFWorkbook(); } if (Workbook == null) { return; } ISheet sheet = Workbook.CreateSheet(sheetName); IRow rowColumn = sheet.CreateRow(0); for (int j = 0; j < data.Columns.Count; j++) { rowColumn.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } for (int i = 0; i < data.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < data.Columns.Count; j++) { if (data.Rows[i][j] == null) { return; } if (data.Columns[j].DataType == typeof(string)) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } else { if (double.TryParse(data.Rows[i][j].ToString(), out double value)) { row.CreateCell(j).SetCellValue(value); } } } } Workbook.Write(Fs); }
/// <summary> /// Saves tracked objects to the specified stream. /// </summary> /// <param name="stream">The stream to save the objects to.</param> /// <param name="sheetName">Name of the sheet.</param> /// <param name="xlsx">if set to <c>true</c> saves in .xlsx format; otherwise, saves in .xls format.</param> public void Save(Stream stream, string sheetName, bool xlsx = true) { if (Workbook == null) { Workbook = xlsx ? (IWorkbook) new XSSFWorkbook() : (IWorkbook) new HSSFWorkbook(); } var sheet = Workbook.GetSheet(sheetName); if (sheet == null) { sheet = Workbook.CreateSheet(sheetName); } Save(stream, sheet); }
/// <summary> /// Saves tracked objects to the specified stream. /// </summary> /// <param name="stream">The stream to save the objects to.</param> /// <param name="sheetIndex">Index of the sheet.</param> /// <param name="xlsx">if set to <c>true</c> saves in .xlsx format; otherwise, saves in .xls format.</param> public void Save(Stream stream, int sheetIndex = 0, bool xlsx = true) { if (Workbook == null) { Workbook = xlsx ? (IWorkbook) new XSSFWorkbook() : (IWorkbook) new HSSFWorkbook(); } var sheet = Workbook.GetSheetAt(sheetIndex); if (sheet == null) { sheet = Workbook.CreateSheet(); } Save(stream, sheet); }
public IWorkbook DataToExcel(DataTable dataTable, string sheetName = null, IDictionary <int, string> columnFormatting = null) { try { ISheet sheet; if (sheetName != null) { sheet = Workbook.CreateSheet(sheetName); } else { sheet = Workbook.CreateSheet(); } var headerRow = sheet.CreateRow(0); foreach (DataColumn column in dataTable.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); } var rowIndex = 1; foreach (DataRow row in dataTable.Rows) { var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dataTable.Columns) { var cell = dataRow.CreateCell(column.Ordinal); string columnType = null; if (columnFormatting != null && columnFormatting.ContainsKey(column.Ordinal)) { columnType = columnFormatting[column.Ordinal]; } SetCellTypeAndValue(cell, column, row[column], columnType); } rowIndex++; } } catch (Exception e) { Debug.Fail($"A critical exception occurred while parsing data to excel: {e.Message}"); throw; } return(Workbook); }
public void Test35084() { Workbook wb = GetTestDataProvider().CreateWorkbook(); Sheet s = wb.CreateSheet("Sheet1"); Row r = s.CreateRow(0); r.CreateCell(0).SetCellValue(1); r.CreateCell(1).CellFormula = ("A1*2"); Sheet s1 = wb.CloneSheet(0); r = s1.GetRow(0); Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 0, "double"); // sanity check Assert.IsNotNull(r.GetCell(1)); Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2", "formula"); }
public void TestDefaultColumnStyle() { Workbook wb = GetTestDataProvider().CreateWorkbook(); CellStyle style = wb.CreateCellStyle(); Sheet sheet = wb.CreateSheet(); sheet.SetDefaultColumnStyle(0, style); Assert.IsNotNull(sheet.GetColumnStyle(0)); Assert.AreEqual(style.Index, sheet.GetColumnStyle(0).Index); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); CellStyle style2 = cell.CellStyle; Assert.IsNotNull(style2); Assert.AreEqual(style.Index, style2.Index, "style should match"); }