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