static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet s1 = workbook.CreateSheet("Sheet1"); ICellStyle rowstyle = workbook.CreateCellStyle(); rowstyle.FillForegroundColor = IndexedColors.Red.Index; rowstyle.FillPattern = FillPattern.SolidForeground; ICellStyle c1Style = workbook.CreateCellStyle(); c1Style.FillForegroundColor = IndexedColors.Yellow.Index; c1Style.FillPattern = FillPattern.SolidForeground; IRow r1 = s1.CreateRow(1); IRow r2= s1.CreateRow(2); r1.RowStyle = rowstyle; r2.RowStyle = rowstyle; ICell c1 = r2.CreateCell(2); c1.CellStyle = c1Style; c1.SetCellValue("Test"); ICell c4 = r2.CreateCell(4); c4.CellStyle = c1Style; using(var fs=File.Create("test.xlsx")) { workbook.Write(fs); } }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); // Create a Worksheet ISheet ws = wb.CreateSheet("Sheet1"); // Aqua background ICellStyle style = wb.CreateCellStyle(); style.FillBackgroundColor = IndexedColors.Aqua.Index; style.FillPattern = FillPattern.BigSpots; IRow row = ws.CreateRow(0); ICell cell = row.CreateCell(1); cell.SetCellValue("X"); cell.CellStyle = style; // Orange "foreground", foreground being the fill foreground not the font color. style = wb.CreateCellStyle(); style.FillBackgroundColor = IndexedColors.Orange.Index; style.FillPattern = FillPattern.SolidForeground; cell = row.CreateCell(2); cell.SetCellValue("X"); cell.CellStyle = style; FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet1 = wb.CreateSheet("First Sheet"); ISheet sheet2 = wb.CreateSheet("Second Sheet"); // Note that sheet name is Excel must not exceed 31 characters // and must not contain any of the any of the following characters: // 0x0000 // 0x0003 // colon (:) // backslash (\) // asterisk (*) // question mark (?) // forward slash (/) // opening square bracket ([) // closing square bracket (]) // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} // for a safe way to create valid names, this utility replaces invalid characters with a space (' ') String safeName = WorkbookUtil.CreateSafeSheetName("[O'Brien's sales*?]"); ISheet sheet3 = wb.CreateSheet(safeName); FileStream sw = File.Create("newWorksheet.xls"); wb.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ////cell style for hyperlinks ////by default hyperlinks are blue and underlined ICellStyle hlink_style = workbook.CreateCellStyle(); IFont hlink_font = workbook.CreateFont(); hlink_font.Underline = (byte)FontUnderlineType.SINGLE; hlink_font.Color = HSSFColor.BLUE.index; hlink_style.SetFont(hlink_font); ICell cell; ISheet sheet = workbook.CreateSheet("Hyperlinks"); //URL cell = sheet.CreateRow(0).CreateCell(0); cell.SetCellValue("URL Link"); XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL); link.Address = ("http://poi.apache.org/"); cell.Hyperlink = (link); cell.CellStyle = (hlink_style); //link to a file in the current directory cell = sheet.CreateRow(1).CreateCell(0); cell.SetCellValue("File Link"); link = new XSSFHyperlink(HyperlinkType.FILE); link.Address = ("link1.xls"); cell.Hyperlink = (link); cell.CellStyle = (hlink_style); //e-mail link cell = sheet.CreateRow(2).CreateCell(0); cell.SetCellValue("Email Link"); link = new XSSFHyperlink(HyperlinkType.EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.Address = ("mailto:[email protected]?subject=Hyperlinks"); cell.Hyperlink = (link); cell.CellStyle = (hlink_style); //link to a place in this workbook //Create a target sheet and cell ISheet sheet2 = workbook.CreateSheet("Target ISheet"); sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell"); cell = sheet.CreateRow(3).CreateCell(0); cell.SetCellValue("Worksheet Link"); link = new XSSFHyperlink(HyperlinkType.DOCUMENT); link.Address = ("'Target ISheet'!A1"); cell.Hyperlink = (link); cell.CellStyle = (hlink_style); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); workbook.CreateSheet("Sheet A1"); workbook.CreateSheet("Sheet A2"); workbook.CreateSheet("Sheet A3"); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); wb.CreateSheet("new sheet"); wb.CreateSheet("second sheet"); ISheet cloneSheet = wb.CloneSheet(0); FileStream sw = File.Create("newWorksheet.xls"); wb.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet1 = wb.CreateSheet("Sheet1"); sheet1.SetMargin(MarginType.RightMargin, 0.5d); sheet1.SetMargin(MarginType.TopMargin, 0.6d); sheet1.SetMargin(MarginType.LeftMargin, 0.4d); sheet1.SetMargin(MarginType.BottomMargin, 0.3d); sheet1.PrintSetup.Copies = 3; sheet1.PrintSetup.NoColor = true; sheet1.PrintSetup.Landscape = true; sheet1.PrintSetup.PaperSize = (short)PaperSize.A4+1; sheet1.FitToPage = true; sheet1.PrintSetup.FitHeight = 2; sheet1.PrintSetup.FitWidth = 3; sheet1.IsPrintGridlines = true; sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample"); int x = 1; for (int i = 1; i <= 15; i++) { IRow row = sheet1.CreateRow(i); for (int j = 0; j < 15; j++) { row.CreateCell(j).SetCellValue(x++); } } ISheet sheet2 = wb.CreateSheet("Sheet2"); sheet2.PrintSetup.Copies = 1; sheet2.PrintSetup.Landscape = false; sheet2.PrintSetup.Notes = true; //sheet2.PrintSetup.EndNote = true; //sheet2.PrintSetup.CellError = DisplayCellErrorType.ErrorAsNA; sheet2.PrintSetup.PaperSize = (short)PaperSize.A5+1; x = 100; for (int i = 1; i <= 15; i++) { IRow row = sheet2.CreateRow(i); for (int j = 0; j < 15; j++) { row.CreateCell(j).SetCellValue(x++); } } FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
protected void btn_click(object sender, EventArgs e) { //FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read); //XSSFWorkbook temWorkBook = new XSSFWorkbook(fs); //ISheet nsheet = temWorkBook.GetSheet("Sheet1"); //IRow datarow = nsheet.GetRow(4); //datarow.GetCell(0).SetCellValue(77); //nsheet.ForceFormulaRecalculation = true; //using (var ms = new MemoryStream()) //{ // temWorkBook.Write(ms); // Response.Clear(); // Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx"); // Response.BinaryWrite(ms.ToArray()); // Response.End(); //} IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("New Sheet"); ISheet sheet2 = workbook.CreateSheet("Second Sheet"); ICell cell1 = sheet1.CreateRow(0).CreateCell(0); IFont fontBold = workbook.CreateFont(); fontBold.Boldweight = (short)FontBoldWeight.Bold; ICellStyle style1 = workbook.CreateCellStyle(); style1.SetFont(fontBold); cell1.CellStyle = style1; cell1.SetCellValue("sample value"); int x = 1; for (int i = 1; i <= 15; i++) { IRow row = sheet1.CreateRow(i); for(int j = 0;j < 15;j++) { row.CreateCell(j).SetCellValue(x++); } } using (var ms = new MemoryStream()) { workbook.Write(ms); Response.Clear(); Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx"); Response.BinaryWrite(ms.ToArray()); Response.End(); } }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); wb.CreateSheet("new sheet"); wb.CreateSheet("second sheet"); wb.CreateSheet("third sheet"); wb.SetSheetOrder("second sheet", 0); wb.SetSheetOrder("new sheet", 1); wb.SetSheetOrder("third sheet", 2); FileStream sw = File.Create("../../data/Reordered.xls"); wb.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); // Create a Worksheet ISheet ws = wb.CreateSheet("Sheet1"); // Create a new font and alter it IFont font = wb.CreateFont(); font.FontHeightInPoints = 24; font.FontName = "Courier New"; font.IsItalic = true; font.IsStrikeout = true; // Fonts are set into a style so create a new one to use. ICellStyle style = wb.CreateCellStyle(); style.SetFont(font); IRow row = ws.CreateRow(0); // Create a cell and put a value in it. ICell cell = row.CreateCell(1); cell.SetCellValue("Thisi s a test of fonts"); cell.CellStyle = style; FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
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(); }
public override void CreateWorkBook(string destinationPath) { try { var workbook = new XSSFWorkbook(); var sheet1 = (XSSFSheet)workbook.CreateSheet("Sheet1"); sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample xlsx Sheet"); //var x = 1; //for (var i = 1; i <= 10000; i++) //{ // var row = sheet1.CreateRow(i); // for (var j = 0; j < 50; j++) // { // row.CreateCell(j).SetCellValue("cell" + x++); // } //} var s = sheet1 as ISheet; CreateRows(20000, 40, ref s); using (var f = File.Create(destinationPath)) { workbook.Write(f); } } catch (Exception e) { throw new Exception("Cannot create Excell SpreadSheet (.xlsx)", e); } }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); // Setting support for Right To Left sheet1.IsRightToLeft = true; sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample"); int x = 1; for(int i = 1; i <= 15; i++) { IRow row = sheet1.CreateRow(i); for(int j = 0; j < 15; j++) { row.CreateCell(j).SetCellValue(x++); } } FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void TestBug48936() { IWorkbook w = new XSSFWorkbook(); ISheet s = w.CreateSheet(); int i = 0; List<String> lst = ReadStrings("48936-strings.txt"); foreach (String str in lst) { s.CreateRow(i++).CreateCell(0).SetCellValue(str); } try { w = XSSFTestDataSamples.WriteOutAndReadBack(w); } catch (POIXMLException) { Assert.Fail("Detected Bug #48936"); } s = w.GetSheetAt(0); i = 0; foreach (String str in lst) { String val = s.GetRow(i++).GetCell(0).StringCellValue; Assert.AreEqual(str, val); } }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("PictureSheet"); IDrawing patriarch = sheet1.CreateDrawingPatriarch(); //create the anchor XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7); anchor.AnchorType = 2; //load the picture and get the picture index in the workbook //first picture int imageId= LoadImage("../../image/HumpbackWhale.jpg", workbook); XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId); //Reset the image to the original size. //picture.Resize(); //Note: Resize will reset client anchor you set. picture.LineStyle = LineStyle.DashDotGel; //second picture int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook); XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15); XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2); picture.LineStyle = LineStyle.DashDotGel; FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void Create(Bilan bilan, string path) { var splitPath = path.Split('\\'); var pathFile = splitPath.Take(splitPath.Count() - 1).Aggregate((e, p) => e + "\\" + p) + "\\"; var fileNameOld = splitPath.Last(); var fileName = "synoptique_" + fileNameOld; using (var stream = new FileStream(pathFile + fileName, FileMode.Create, FileAccess.Write)) { var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet("synoptique"); var cadreArmoire = new CadreArmoire(sheet, workbook); cadreArmoire.Create(bilan.PageDeGarde.ChambrePMZ); var cadrePA = new CadrePA(sheet, workbook); cadrePA.Create(bilan.PageDeGarde.ChambrePointAboutement); var positionnementEtudeCreator = new PositionnementEtudeCreator(sheet, workbook); var hauteur = 9; var numeroCassette = 0; foreach (var positionnementEtude in bilan.PositionnementEtudes) { positionnementEtudeCreator.Create(positionnementEtude, ref hauteur, ref numeroCassette); } SetWidth(sheet); workbook.Write(stream); } }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); // Create a Worksheet ISheet ws = wb.CreateSheet("Sheet1"); ICellStyle style = wb.CreateCellStyle(); //Setting the line of the top border style.BorderTop = BorderStyle.Thick; style.TopBorderColor = 256; style.BorderLeft = BorderStyle.Thick; style.LeftBorderColor = 256; style.BorderRight = BorderStyle.Thick; style.RightBorderColor = 256; style.BorderBottom = BorderStyle.Thick; style.BottomBorderColor = 256; IRow row = ws.CreateRow(0); ICell cell = row.CreateCell(1); cell.CellStyle = style; FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
protected void Button1_Click(object sender, EventArgs e) { string filename = "test.xlsx"; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); XSSFWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample"); int x = 1; for (int i = 1; i <= 15; i++) { IRow row = sheet1.CreateRow(i); for (int j = 0; j < 15; j++) { row.CreateCell(j).SetCellValue(x++); } } using (var f = File.Create(@"c:\test.xlsx")) { workbook.Write(f); } Response.WriteFile(@"c:\test.xlsx"); //http://social.msdn.microsoft.com/Forums/en-US/3a7bdd79-f926-4a5e-bcb0-ef81b6c09dcf/responseoutputstreamwrite-writes-all-but-insetrs-a-char-every-64k?forum=ncl //workbook.Write(Response.OutputStream); cannot be used //root cause: Response.OutputStream will insert unnecessary byte into the response bytes. Response.Flush(); Response.End(); }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { tb = tb ?? throw new Exception($"无法把空{nameof(DataTable)}转成Excel"); using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index); for (int i = 0; i < tb.Rows.Count; ++i) { var row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { var cell = row.CreateCell(j); var data = tb.Rows[i][j]; cell.SetCellValue(ConvertHelper.GetString(data)); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test"); sheet.SetColumnWidth(4, 5000); sheet.SetColumnWidth(5, 5000); sheet.GroupColumn((short)4, (short)5); sheet.SetColumnGroupCollapsed(4, true); CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0); //logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing"); sheet = (XSSFSheet)wb.GetSheet("test"); for (int i = 4; i <= 5; i++) { Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i); } cols = sheet.GetCTWorksheet().GetColsArray(0); foreach (CT_Col col in cols.GetColArray()) { Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString()); } }
public static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet s1=wb.CreateSheet("Monthly Salary Report"); IRow headerRow = s1.CreateRow(0); headerRow.CreateCell(0).SetCellValue("First Name"); s1.SetColumnWidth(0, 20 * 256); headerRow.CreateCell(1).SetCellValue("Last Name"); s1.SetColumnWidth(1, 20 * 256); headerRow.CreateCell(2).SetCellValue("Salary"); headerRow.CreateCell(3).SetCellValue("Tax Rate"); headerRow.CreateCell(4).SetCellValue("Tax"); headerRow.CreateCell(5).SetCellValue("Delivery"); int row = 1; GenerateRow(s1, row++, "Bill", "Zhang", 5000, 9.0/100); GenerateRow(s1, row++, "Amy", "Huang", 8000, 11.0/100); GenerateRow(s1, row++, "Tomos", "Johnson", 6000, 9.0/100); GenerateRow(s1, row++, "Macro", "Jeep", 12000, 15.0/100); s1.ForceFormulaRecalculation = false; FileStream fs = File.Create("test.xlsx"); wb.Write(fs); fs.Close(); }
public void TestCreate() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data"); IList pictures = wb.GetAllPictures(); Assert.AreEqual(0, pictures.Count); int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG); Assert.AreEqual(1, pictures.Count); Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data)); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30); Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType); anchor.AnchorType = (int)AnchorType.DontMoveAndResize; Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType); XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx); Assert.IsTrue(anchor.Equals(shape.GetAnchor())); Assert.IsNotNull(shape.PictureData); Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data)); CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0]; // STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs); }
public void ExportExcel(IList<dynamic> items, string fileName) { IWorkbook workbook = new XSSFWorkbook(); ISheet worksheet = workbook.CreateSheet("Sheet1"); for (int rownum = 0; rownum < items.Count; rownum++) { IRow row = worksheet.CreateRow(rownum); var item = items[rownum]; var keys = item.Keys.ToArray(); for (int index = 0; index < keys.Length; index++) { var key = keys[index]; ICell cell = row.CreateCell(index); var value = item[key]; if (value != null) { cell.SetCellValue(value.ToString()); } } } using (FileStream sw = File.Create(fileName)) { workbook.Write(sw); sw.Close(); } }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("linechart"); // Create a row and put some cells in it. Rows are 0 based. IRow row; ICell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.CreateRow((short)rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.CreateCell((short)colIndex); cell.SetCellValue(colIndex * (rowIndex + 1)); } } IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15); CreateChart(drawing, sheet, anchor1, "title1","title2"); IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35); CreateChart(drawing, sheet, anchor2, "s1", "s2"); using (FileStream fs =File.Create("test.xlsx")) { wb.Write(fs); } }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); int x = 1; for (int i = 0; i < 15; i++) { IRow row = sheet1.CreateRow(i); for (int j = 0; j < 15; j++) { ICell cell = row.CreateCell(j); if (x % 2 == 0) { //fill background with blue ICellStyle style1 = workbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; cell.CellStyle = style1; } else { //fill background with yellow ICellStyle style1 = workbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; cell.CellStyle = style1; } x++; } } FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void TestXSSFSetArrayFormula_multiCell() { ICellRange<ICell> cells; String formula2 = "456"; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6"); cells = sheet.SetArrayFormula(formula2, range); Assert.AreEqual(3, cells.Size); // sheet.SetArrayFormula Creates rows and cells for the designated range /* * From the spec: * For a multi-cell formula, the c elements for all cells except the top-left * cell in that range shall not have an f element; */ // Check that each cell exists and that the formula text is Set correctly on the first cell XSSFCell firstCell = (XSSFCell)cells.TopLeftCell; ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6"); ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5"); ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6"); Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell)); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); //increase the width of Column A sheet.SetColumnWidth(0, 5000); //create the format instance IDataFormat format = workbook.CreateDataFormat(); // Create a row and put some cells in it. Rows are 0 based. ICell cell = sheet.CreateRow(0).CreateCell(0); //number format with 2 digits after the decimal point - "1.20" SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00")); //RMB currency format with comma - "¥20,000" ICell cell2 = sheet.CreateRow(1).CreateCell(0); SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0")); //scentific number format - "3.15E+00" ICell cell3 = sheet.CreateRow(2).CreateCell(0); SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00")); //percent format, 2 digits after the decimal point - "99.33%" ICell cell4 = sheet.CreateRow(3).CreateCell(0); SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%")); //phone number format - "021-65881234" ICell cell5 = sheet.CreateRow(4).CreateCell(0); SetValueAndFormat(workbook, cell5, 02165881234,format.GetFormat("000-00000000")); //Chinese capitalized character number - 壹贰叁 元 ICell cell6 = sheet.CreateRow(5).CreateCell(0); SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元")); //Chinese date string ICell cell7 = sheet.CreateRow(6).CreateCell(0); SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日")); cell7.SetCellValue(new DateTime(2004, 5, 6)); //Chinese date string ICell cell8 = sheet.CreateRow(7).CreateCell(0); SetValueAndFormat(workbook,cell8,new DateTime(2005, 11, 6),format.GetFormat("yyyy年m月d日")); //formula value with datetime style ICell cell9 = sheet.CreateRow(8).CreateCell(0); cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")"; ICellStyle cellStyle9 = workbook.CreateCellStyle(); cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); cell9.CellStyle = cellStyle9; //display current time ICell cell10 = sheet.CreateRow(9).CreateCell(0); SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@")); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void TestRepeatingRowsAndColums() { // First Test that Setting RR&C for same sheet more than once only Creates a // single Print_Titles built-in record XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet("First Sheet"); wb.SetRepeatingRowsAndColumns(0, -1, -1, -1, -1); // Set repeating rows and columns twice for the first sheet for (int i = 0; i < 2; i++) { wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3); //sheet.CreateFreezePane(0, 3); } Assert.AreEqual(1, wb.NumberOfNames); IName nr1 = wb.GetNameAt(0); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName); Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula); //remove the columns part wb.SetRepeatingRowsAndColumns(0, -1, -1, 0, 3); Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula); //revert wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3); //remove the rows part wb.SetRepeatingRowsAndColumns(0, 0, 0, -1, -1); Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula); //revert wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3); // Save and re-open IWorkbook nwb = XSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.AreEqual(1, nwb.NumberOfNames); nr1 = nwb.GetNameAt(0); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName); Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula); // check that Setting RR&C on a second sheet causes a new Print_Titles built-in // name to be Created nwb.CreateSheet("SecondSheet"); nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0); Assert.AreEqual(2, nwb.NumberOfNames); IName nr2 = nwb.GetNameAt(1); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr2.NameName); Assert.AreEqual("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.RefersToFormula); nwb.SetRepeatingRowsAndColumns(1, -1, -1, -1, -1); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); //font style1: underlined, italic, red color, fontsize=20 IFont font1 = workbook.CreateFont(); font1.Color = IndexedColors.Red.Index; font1.IsItalic = true; font1.Underline = FontUnderlineType.Double; font1.FontHeightInPoints = 20; //bind font with style 1 ICellStyle style1 = workbook.CreateCellStyle(); style1.SetFont(font1); //font style2: strikeout line, green color, fontsize=15, fontname='宋体' IFont font2 = workbook.CreateFont(); font2.Color = IndexedColors.OliveGreen.Index; font2.IsStrikeout = true; font2.FontHeightInPoints = 15; font2.FontName = "宋体"; //bind font with style 2 ICellStyle style2 = workbook.CreateCellStyle(); style2.SetFont(font2); //apply font styles ICell cell1 = sheet1.CreateRow(1).CreateCell(1); cell1.SetCellValue("Hello World!"); cell1.CellStyle = style1; ICell cell2 = sheet1.CreateRow(3).CreateCell(1); cell2.SetCellValue("早上好!"); cell2.CellStyle = style2; ////cell with rich text ICell cell3 = sheet1.CreateRow(5).CreateCell(1); XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM"); //apply font to "Microsoft Office" IFont font4 = workbook.CreateFont(); font4.FontHeightInPoints = 12; richtext.ApplyFont(0, 16, font4); //apply font to "TM" IFont font3 = workbook.CreateFont(); font3.TypeOffset = FontSuperScript.Super; font3.IsItalic = true; font3.Color = IndexedColors.Blue.Index; font3.FontHeightInPoints = 8; richtext.ApplyFont(16, 18, font3); cell3.SetCellValue(richtext); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> public byte[] DataTableToExcel(DataTable tb, bool show_header = true) { tb = tb ?? throw new ArgumentNullException($"无法把空{nameof(DataTable)}转成Excel"); using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsNotEmpty(tb.TableName) ? tb.TableName : "sheet"); var columns = tb.Columns.AsEnumerable_ <DataColumn>(); var row_index = 0; IRow NewRow() => sheet.CreateRow(row_index++); if (show_header) { //头部 var header_style = this.GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Black.Index, NPOI.HSSF.Util.HSSFColor.White.Index); var header = NewRow(); var cell_index = 0; foreach (var col in columns) { var cell = header.CreateCell(cell_index++, CellType.String); var data = col.ColumnName; cell.SetCellValue(data); cell.CellStyle = header_style; } } var style = this.GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index); foreach (var tb_row in tb.Rows.AsEnumerable_ <DataRow>()) { var row = NewRow(); var cell_index = 0; foreach (var tb_col in columns) { var cell = row.CreateCell(cell_index++, CellType.String); var data = tb_row[tb_col.ColumnName]; cell.SetCellValue(ConvertHelper.GetString(data)); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
/// <summary> /// 读取DataTable的数据,并把数据写入excel /// </summary> /// <param name="filePath">文件路径</param> /// <param name="fileName">文件名</param> /// <param name="sheetName">excel里第一个sheet的名称</param> /// <param name="DataTable1">要写入的DataTable</param> /// <param name="freezePaneRow">冻结拆分窗口的行数</param> /// <returns></returns> public string createExcel(string filePath, string fileName, string sheetName, DataTable DataTable1, int freezePaneRow) { string result = null; try { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet(sheetName); //设置居中 ICellStyle styleCell = xssfworkbook.CreateCellStyle(); styleCell.VerticalAlignment = VerticalAlignment.Center; styleCell.Alignment = HorizontalAlignment.Center; for (int i = 0; i < DataTable1.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i); for (int j = 0; j < DataTable1.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(DataTable1.Rows[i][j].ToString()); sheet.GetRow(i).GetCell(j).CellStyle = styleCell; } } //自适应列宽 for (int i = 0; i < DataTable1.Columns.Count; i++) { sheet.AutoSizeColumn(i); } //冻结拆分窗口 sheet.CreateFreezePane(1, freezePaneRow); MemoryStream stream = new MemoryStream(); xssfworkbook.Write(stream); var buf = stream.ToArray(); stream.Close(); stream.Dispose(); if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } using (FileStream fs = new FileStream(filePath + fileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); fs.Close(); fs.Dispose(); } } catch (Exception e) { result = e.Message; } return result; }
public void TestMultipleDrawings() { XSSFWorkbook wb = new XSSFWorkbook(); for (int i = 0; i < 3; i++) { XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); } OPCPackage pkg = wb.Package; Assert.AreEqual(3, pkg.GetPartsByContentType(XSSFRelation.DRAWINGS.ContentType).Count); }
public static byte[] Output(DataTable dataTable, string[] tableTitle) { NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet"); IRow Title = null; IRow rows = null; for (int i = 1; i <= dataTable.Rows.Count; i++) { //创建表头 if (i - 1 == 0) { Title = sheet.CreateRow(0); for (int k = 1; k < tableTitle.Length + 1; k++) { Title.CreateCell(0).SetCellValue("序号"); Title.CreateCell(k).SetCellValue(tableTitle[k - 1]); } continue; } else { rows = sheet.CreateRow(i - 1); for (int j = 1; j <= dataTable.Columns.Count; j++) { rows.CreateCell(0).SetCellValue(i - 1); rows.CreateCell(j).SetCellValue(dataTable.Rows[i - 1][j - 1].ToString()); } } } byte[] buffer = new byte[1024 * 5]; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); buffer = ms.GetBuffer(); ms.Close(); } return(buffer); }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { if (tb == null) { return(null); } using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index); NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICell cell = null; for (int i = 0; i < tb.Rows.Count; ++i) { row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { cell = row.CreateCell(j); cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j])); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
public void CreateExcelFile() { string FileName = @"d:\5.5.5.101_5555_Log_18.txt"; string[] filelist = File.ReadAllLines(FileName, Encoding.Default); List <string> StringLists = new List <string>(); int gg = 0; for (int linenum = filelist.Length - 1; linenum >= 0; linenum--) { if (filelist[linenum].IndexOf("ANR") > -1) { int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length; int last = filelist[linenum].LastIndexOf(" ("); string str2 = filelist[linenum].Substring(first, last - first); StringLists.Add(str2); } } ////建立Excel 2007檔案 IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); //合併區 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行 //背景色(藍色) ICellStyle cellStyle0 = workbook.CreateCellStyle(); cellStyle0.FillPattern = FillPattern.SolidForeground; cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index; cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //背景色(綠色) ICellStyle cellStyle1 = workbook.CreateCellStyle(); cellStyle1.FillPattern = FillPattern.SolidForeground; cellStyle1.FillForegroundColor = IndexedColors.Lime.Index; cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //背景色(粉色) ICellStyle cellStyle2 = workbook.CreateCellStyle(); cellStyle2.FillPattern = FillPattern.SolidForeground; cellStyle2.FillForegroundColor = IndexedColors.Tan.Index; cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //背景色(灰色) ICellStyle cellStyle3 = workbook.CreateCellStyle(); cellStyle3.FillPattern = FillPattern.SolidForeground; cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index; //背景色(白色) ICellStyle cellStyle4 = workbook.CreateCellStyle(); cellStyle4.FillPattern = FillPattern.SolidForeground; cellStyle4.FillForegroundColor = IndexedColors.White.Index; //Summary儲存格格式 ICellStyle summaryStyle = workbook.CreateCellStyle(); IFont summaryFont = workbook.CreateFont(); summaryFont.FontHeightInPoints = 18; summaryStyle.SetFont(summaryFont); summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; summaryStyle.FillPattern = FillPattern.SolidForeground; summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index; //A列 sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name"); sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name"); sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time"); sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time"); sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time"); sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No."); sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device"); sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester"); for (int A = 0; A < 8; A++) { sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0; } //E列 sheet.GetRow(0).CreateCell(4).SetCellValue("Date"); sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)"); sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES"); sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash"); sheet.GetRow(4).CreateCell(4).SetCellValue("Result"); sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW"); sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash"); for (int E = 0; E < 7; E++) { sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0; } sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4; //F列 sheet.GetRow(0).CreateCell(5).SetCellValue("-----"); sheet.GetRow(1).CreateCell(5).SetCellValue("-----"); sheet.GetRow(2).CreateCell(5).SetCellValue("-----"); sheet.GetRow(3).CreateCell(5).SetCellValue("-----"); sheet.GetRow(4).CreateCell(5).SetCellValue(""); sheet.GetRow(5).CreateCell(5).SetCellValue("-----"); sheet.GetRow(6).CreateCell(5).SetCellValue("-----"); for (int F = 0; F < 7; F++) { sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2; } sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4; //Summary sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary"); sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle; //Error List sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List"); sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3; //Total sheet.GetRow(10).CreateCell(6).SetCellValue("Total"); sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3; //搜尋相同字串並記次 Dictionary <string, int> dict = new Dictionary <string, int>(); foreach (string myStringList in StringLists) { if (dict.ContainsKey(myStringList)) { //如果Dictionary中存在这个关键词元素,则把这个Dictionary的key+1 dict[myStringList]++; } else { //如果Dictionary中不存在这个关键词元素,则把它添加进Dictionary dict.Add(myStringList, 1); } } int rowcnt = dict.Count; while (rowcnt != 0) { foreach (KeyValuePair <string, int> item in dict) { Console.WriteLine(item.Key); Console.WriteLine(item.Value); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行 sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key); sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value); rowcnt--; } } for (int c = 0; c <= 25; c++) { sheet.AutoSizeColumn(c); } FileStream file = new FileStream(@"d:\npoi.xlsx", FileMode.Create);//產生檔案 workbook.Write(file); file.Close(); }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFTextParagraph para = shape.AddNewTextParagraph(); para.AddNewTextRun().Text = ("Line 1"); List <XSSFTextRun> Runs = para.TextRuns; Assert.AreEqual(1, Runs.Count); XSSFTextRun run = Runs[0]; Assert.AreEqual("Line 1", run.Text); //Assert.IsNotNull(run.ParentParagraph); //Assert.IsNotNull(run.XmlObject); Assert.IsNotNull(run.GetRPr()); Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor); Color color = Color.FromArgb(0, 255, 255); run.FontColor = (/*setter*/ color); Assert.AreEqual(color, run.FontColor); Assert.AreEqual(11.0, run.FontSize, 0.01); run.FontSize = (/*setter*/ 12.32); Assert.AreEqual(12.32, run.FontSize, 0.01); run.FontSize = (/*setter*/ -1.0); Assert.AreEqual(11.0, run.FontSize, 0.01); run.FontSize = (/*setter*/ -1.0); Assert.AreEqual(11.0, run.FontSize, 0.01); try { run.FontSize = (/*setter*/ 0.9); Assert.Fail("Should fail"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.Contains("0.9")); } Assert.AreEqual(11.0, run.FontSize, 0.01); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/ 12.31); Assert.AreEqual(12.31, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/ 0.0); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/ 0.0); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); Assert.AreEqual("Calibri", run.FontFamily); run.SetFontFamily("Arial", (byte)1, (byte)1, false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", unchecked ((byte)-1), (byte)1, false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", (byte)1, unchecked ((byte)-1), false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", (byte)1, (byte)1, true); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily(null, (byte)1, (byte)1, false); Assert.AreEqual("Calibri", run.FontFamily); run.SetFontFamily(null, (byte)1, (byte)1, false); Assert.AreEqual("Calibri", run.FontFamily); run.SetFont("Arial"); Assert.AreEqual("Arial", run.FontFamily); Assert.AreEqual((byte)0, run.PitchAndFamily); run.SetFont(null); Assert.AreEqual((byte)0, run.PitchAndFamily); Assert.IsFalse(run.IsStrikethrough); run.IsStrikethrough = (/*setter*/ true); Assert.IsTrue(run.IsStrikethrough); run.IsStrikethrough = (/*setter*/ false); Assert.IsFalse(run.IsStrikethrough); Assert.IsFalse(run.IsSuperscript); run.IsSuperscript = (/*setter*/ true); Assert.IsTrue(run.IsSuperscript); run.IsSuperscript = (/*setter*/ false); Assert.IsFalse(run.IsSuperscript); Assert.IsFalse(run.IsSubscript); run.IsSubscript = (/*setter*/ true); Assert.IsTrue(run.IsSubscript); run.IsSubscript = (/*setter*/ false); Assert.IsFalse(run.IsSubscript); Assert.AreEqual(TextCap.NONE, run.TextCap); Assert.IsFalse(run.IsBold); run.IsBold = (/*setter*/ true); Assert.IsTrue(run.IsBold); run.IsBold = (/*setter*/ false); Assert.IsFalse(run.IsBold); Assert.IsFalse(run.IsItalic); run.IsItalic = (/*setter*/ true); Assert.IsTrue(run.IsItalic); run.IsItalic = (/*setter*/ false); Assert.IsFalse(run.IsItalic); Assert.IsFalse(run.IsUnderline); run.IsUnderline = (/*setter*/ true); Assert.IsTrue(run.IsUnderline); run.IsUnderline = (/*setter*/ false); Assert.IsFalse(run.IsUnderline); Assert.IsNotNull(run.ToString()); } finally { wb.Close(); } }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/ "Arial"); rt.ApplyFont(font); shape.SetText(/*setter*/ rt); Assert.IsNotNull(shape.GetCTShape()); Assert.IsNotNull(shape.GetEnumerator()); Assert.IsNotNull(XSSFSimpleShape.GetPrototype()); foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber))) { shape.TextParagraphs[(0)].SetBullet(nr); Assert.IsNotNull(shape.Text); } shape.TextParagraphs[(0)].SetBullet(false); Assert.IsNotNull(shape.Text); shape.SetText("testtext"); Assert.AreEqual("testtext", shape.Text); shape.SetText(new XSSFRichTextString()); Assert.AreEqual("null", shape.Text); shape.AddNewTextParagraph(); shape.AddNewTextParagraph("test-other-text"); shape.AddNewTextParagraph(new XSSFRichTextString("rtstring")); shape.AddNewTextParagraph(new XSSFRichTextString()); Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP); Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP); Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.Bottom; Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.Top; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.STACKED); Assert.AreEqual(TextDirection.STACKED, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ 12.32); Assert.AreEqual(12.32, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ 12.31); Assert.AreEqual(12.31, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ 13.31); Assert.AreEqual(13.31, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.RightInset, 0.01); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ 23.31); Assert.AreEqual(23.31, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.TopInset, 0.01); Assert.IsTrue(shape.WordWrap); shape.WordWrap = (/*setter*/ false); Assert.IsFalse(shape.WordWrap); shape.WordWrap = (/*setter*/ true); Assert.IsTrue(shape.WordWrap); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE); Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.NONE); Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit); Assert.AreEqual(5, shape.ShapeType); shape.ShapeType = (/*setter*/ 23); Assert.AreEqual(23, shape.ShapeType); // TODO: should this be supported? // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); Assert.IsNotNull(shape.GetShapeProperties()); } finally { wb.Close(); } }
/// <summary> /// 输出数据表格 /// </summary> /// <param name="workbook">工作文档</param> /// <param name="normalStyle">普通样式(用于表格内容)</param> /// <param name="boldStyle">粗体样式(用于表格头部)</param> /// <param name="table">表格数据</param> public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table) { //创建Sheet页 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(table.TableName); //行号 int rowIndex = 0; //是否需要输出表头 bool isNeedCreateHeader = true; //输出数据到Excel foreach (DataRow rowData in table.Rows) { //忽略空数据行 if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count) { continue; } //列号 int colIndex = 0; //Excel行 NPOI.SS.UserModel.IRow row = null; //是否需要输入表头 if (isNeedCreateHeader) { isNeedCreateHeader = false; //创建行 row = sheet.CreateRow(rowIndex); //输出列名到Excel colIndex = 0; foreach (DataColumn kvp in table.Columns) { //列名 //创建列 NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex); //设置样式 cell.CellStyle = boldStyle; //设置数据 cell.SetCellValue(kvp.ColumnName); colIndex++; } rowIndex++; } //创建行 row = sheet.CreateRow(rowIndex); //输出列值到Excel colIndex = 0; foreach (object val in rowData.ItemArray) { //列值 //创建列 NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex); //设置样式 cell.CellStyle = normalStyle; //设置数据 //判断是否为空 if (val != null) { //不为空 //判断是否为RTF内容 if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name)) { //RTF内容 cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val); } else { //文本内容 cell.SetCellValue(val.ToString()); } } else { //为空 cell.SetCellValue(string.Empty); } colIndex++; } rowIndex++; } //Excel列宽自动适应 if (table.Rows.Count >= 1 && sheet.GetRow(0) != null) { for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++) { sheet.AutoSizeColumn(k); } } }
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter) { var response = new DataResponse <string>(); try { #region Sales Default Export NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport"); NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper(); string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" }; var headerRow = sheet.CreateRow(0); //create header for (int i = 0; i < headers.Length; i++) { sheet.DefaultColumnWidth = 20; XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFColor colorToFill = new XSSFColor(Color.Aqua); style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua); headerRow.RowStyle = style; var cell = headerRow.CreateCell(i); cell.SetCellValue(headers[i]); } string[] allowedRoles = { "RDSLS" }; string[] superRoles = { "RDSLSALL" }; bool hasSuperRight = HasRight(superRoles); bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" }); if (HasRight(allowedRoles) || hasSuperRight) { var repository = new RepositorySales(); var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath); List <GroupedSales> salesList = dataResponse.Model.List.ToList(); int recordCount = dataResponse.Model.Pager.TotalCount; //fill content var rowIndex = 0; for (int i = 0; i < recordCount; i++) { rowIndex++; var row = sheet.CreateRow(rowIndex); var objSalesItem = salesList[i]; if (objSalesItem == null) { continue; } var font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; var cell1 = row.CreateCell(0); cell1.CellStyle = workbook.CreateCellStyle(); cell1.CellStyle.SetFont(font); cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName); var cell2 = row.CreateCell(1); cell2.CellStyle = workbook.CreateCellStyle(); cell2.CellStyle.SetFont(font); cell2.SetCellValue(objSalesItem.Count); var cell3 = row.CreateCell(2); cell3.CellStyle = workbook.CreateCellStyle(); cell3.CellStyle.SetFont(font); cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn)); } string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports"); string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory)); if (!Directory.Exists(fileUri)) { Directory.CreateDirectory(fileUri); } string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now), extension = "xlsx"; string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension)); int count = 1; isExist: if (File.Exists(filePath)) { fileName = string.Format("{0}{1}{2}", fileName, count, extension); filePath = Path.Combine(fileUri, fileName); count++; goto isExist; } using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension)); } return(Ok <DataResponse>(response)); #endregion } catch (Exception ex) { ex.Log(); } response.Message = "Internal Server Error"; return(Ok <DataResponse>(response)); }
public static System.IO.MemoryStream createExcelReport(DataTable dt) { using (System.IO.MemoryStream mem = new System.IO.MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.Center; headerLabelCellStyle.BorderBottom = BorderStyle.Thin; headerLabelCellStyle.BorderRight = BorderStyle.Thin; headerLabelCellStyle.BorderTop = BorderStyle.Thin; headerLabelCellStyle.BorderLeft = BorderStyle.Thin; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.Bold; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.Right; rightAlignedCellStyle.BorderBottom = BorderStyle.Thin; rightAlignedCellStyle.BorderRight = BorderStyle.Thin; rightAlignedCellStyle.BorderTop = BorderStyle.Thin; rightAlignedCellStyle.BorderLeft = BorderStyle.Thin; #endregion #region Currency Cell Style var CellStyleRight = workbook.CreateCellStyle(); CellStyleRight.Alignment = HorizontalAlignment.Right; CellStyleRight.BorderBottom = BorderStyle.Thin; CellStyleRight.BorderRight = BorderStyle.Thin; CellStyleRight.BorderTop = BorderStyle.Thin; CellStyleRight.BorderLeft = BorderStyle.Thin; #endregion #region Detail Currency Subtotal Style var CellStyleNormal = workbook.CreateCellStyle(); CellStyleNormal.BorderBottom = BorderStyle.Thin; CellStyleNormal.BorderRight = BorderStyle.Thin; CellStyleNormal.BorderTop = BorderStyle.Thin; CellStyleNormal.BorderLeft = BorderStyle.Thin; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.Normal; CellStyleNormal.SetFont(detailCurrencySubtotalFont); #endregion #endregion var sheet = workbook.CreateSheet(dt.TableName); var rowIndex = 0; var row = sheet.CreateRow(rowIndex); List <String> columns = new List <string>(); for (int i = 0; i < dt.Columns.Count; i++) { columns.Add(dt.Columns[i].ColumnName); var cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); cell.CellStyle = headerLabelCellStyle; } rowIndex++; //// Add data rows foreach (System.Data.DataRow item in dt.Rows) { row = sheet.CreateRow(rowIndex); for (int ii = 0; ii < dt.Columns.Count; ii++) { var cell = row.CreateCell(ii); cell.SetCellValue(dt.Columns[ii].ColumnName); if (dt.Columns[ii].DataType == typeof(System.Decimal)) { cell.CellStyle = CellStyleRight; } else { cell.CellStyle = CellStyleNormal; } cell.SetCellValue(item[dt.Columns[ii].ColumnName].ToString()); } rowIndex++; } for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); } workbook.Write(mem); return(mem); } }
public IHttpActionResult ExportAllToExcel(FilterSales filter) { var response = new DataResponse <string>(); try { if (filter.GroupBy <= 0) { #region Sales Default Export NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport"); NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper(); string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray(); string[] headers, columns = null; headers = columns = columnname; columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray(); columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray(); //byte[] rgb = new byte[3] { 22, 183, 223 }; //XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb)); var headerRow = sheet.CreateRow(0); //headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua); //create header for (int i = 0; i < headers.Length; i++) { sheet.DefaultColumnWidth = 20; XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFColor colorToFill = new XSSFColor(Color.Aqua); style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua); headerRow.RowStyle = style; var cell = headerRow.CreateCell(i); cell.SetCellValue(headers[i]); } string[] allowedRoles = { "RDSLS" }; string[] superRoles = { "RDSLSALL" }; bool hasSuperRight = HasRight(superRoles); bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" }); if (HasRight(allowedRoles) || hasSuperRight) { var repository = new RepositorySales(); var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath); List <EntitySales> salesList = dataResponse.Model.List.ToList(); int recordCount = dataResponse.Model.Pager.TotalCount; //fill content var rowIndex = 0; for (int i = 0; i < recordCount; i++) { rowIndex++; var row = sheet.CreateRow(rowIndex); for (int j = 0; j < columns.Length; j++) { var font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; var cell = row.CreateCell(j); cell.CellStyle = workbook.CreateCellStyle(); cell.CellStyle.SetFont(font); string value = null; var objSalesItem = salesList[i]; var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]); if (objStaticItem != null) { var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase)); if (property != null) { value = Convert.ToString(property.GetValue(salesList[i], null)); } } else { var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]); if (objDynamicItem != null) { value = objDynamicItem.Value; } } cell.SetCellValue(value); } bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1; if (isFinanceDataCount) { for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++) { rowIndex++; var financeRow = sheet.CreateRow(rowIndex); for (int j = 0; j < columns.Length; j++) { var cell = financeRow.CreateCell(j); string value = null; var objSalesItem = salesList[i].FinanceDataList.ToList()[k]; var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]); if (objStaticItem != null) { value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null)); } else { var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]); if (objDynamicItem != null) { value = objDynamicItem.Value; } } cell.SetCellValue(value); } } } } string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports"); string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory)); if (!Directory.Exists(fileUri)) { Directory.CreateDirectory(fileUri); } string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now), extension = "xlsx"; string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension)); int count = 1; isExist: if (File.Exists(filePath)) { fileName = string.Format("{0}{1}{2}", fileName, count, extension); filePath = Path.Combine(fileUri, fileName); count++; goto isExist; } using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension)); } return(Ok <DataResponse>(response)); #endregion } #region Sales Grouped Report return(ExportGroupedSalesToExcel(filter)); #endregion } catch (Exception ex) { ex.Log(); } response.Message = "Internal Server Error"; return(Ok <DataResponse>(response)); }
public void TestNew() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); //multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); Assert.AreSame(dr1, dr2); List <POIXMLDocumentPart> rels = sheet.GetRelations(); Assert.AreEqual(1, rels.Count); Assert.IsTrue(rels[0] is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rels[0]; String drawingId = drawing.GetPackageRelationship().Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); //XSSFClientAnchor anchor = new XSSFClientAnchor(); XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2)); c1.LineWidth = 2.5; c1.LineStyle = SS.UserModel.LineStyle.DashDotSys; XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5)); Assert.IsNotNull(c2); XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); c3.SetText(new XSSFRichTextString("Test String")); c3.SetFillColor(128, 128, 128); XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); rt.ApplyFont(0, 5, wb.CreateFont()); rt.ApplyFont(5, 6, wb.CreateFont()); c4.SetText(rt); c4.IsNoFill = (true); Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray()); List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[(0)] is XSSFConnector); Assert.IsTrue(shapes[(1)] is XSSFShapeGroup); Assert.IsTrue(shapes[(2)] is XSSFSimpleShape); Assert.IsTrue(shapes[(3)] is XSSFSimpleShape); // Save and re-load it wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; // Check dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing; CT_Drawing ctDrawing = dr1.GetCTDrawing(); // Connector, shapes and text boxes are all two cell anchors Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray()); Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray()); Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray()); shapes = dr1.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[0] is XSSFConnector); Assert.IsTrue(shapes[1] is XSSFShapeGroup); Assert.IsTrue(shapes[2] is XSSFSimpleShape); Assert.IsTrue(shapes[3] is XSSFSimpleShape); // // Ensure it got the right namespaces //String xml = ctDrawing.ToString(); //Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\"")); //Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\"")); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb)); }
public void TestBug55752() { IWorkbook wb = new XSSFWorkbook(); try { ISheet sheet = wb.CreateSheet("test"); for (int i = 0; i < 4; i++) { IRow row = sheet.CreateRow(i); for (int j = 0; j < 2; j++) { ICell cell = row.CreateCell(j); cell.CellStyle = (wb.CreateCellStyle()); } } // set content IRow row1 = sheet.GetRow(0); row1.GetCell(0).SetCellValue("AAA"); IRow row2 = sheet.GetRow(1); row2.GetCell(0).SetCellValue("BBB"); IRow row3 = sheet.GetRow(2); row3.GetCell(0).SetCellValue("CCC"); IRow row4 = sheet.GetRow(3); row4.GetCell(0).SetCellValue("DDD"); // merge cells CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1); sheet.AddMergedRegion(range1); CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1); sheet.AddMergedRegion(range2); CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1); sheet.AddMergedRegion(range3); Assert.AreEqual(0, range3.FirstColumn); Assert.AreEqual(1, range3.LastColumn); Assert.AreEqual(2, range3.LastRow); CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1); sheet.AddMergedRegion(range4); // set border RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb); row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin; row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin; ICell cell0 = CellUtil.GetCell(row3, 0); CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); ICell cell1 = CellUtil.GetCell(row3, 1); CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb); // write to file Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite); try { wb.Write(stream); } finally { stream.Close(); } } finally { wb.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], 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], rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } }
public override void SetUp() { wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; IRow row1 = sheet.CreateRow(0); // Create a cell and Put a value in it. ICell cell = row1.CreateCell(0); cell.SetCellValue("Names"); ICell cell2 = row1.CreateCell(1); cell2.SetCellValue("#"); ICell cell7 = row1.CreateCell(2); cell7.SetCellValue("Data"); ICell cell10 = row1.CreateCell(3); cell10.SetCellValue("Value"); IRow row2 = sheet.CreateRow(1); ICell cell3 = row2.CreateCell(0); cell3.SetCellValue("Jan"); ICell cell4 = row2.CreateCell(1); cell4.SetCellValue(10); ICell cell8 = row2.CreateCell(2); cell8.SetCellValue("Apa"); ICell cell11 = row1.CreateCell(3); cell11.SetCellValue(11.11); IRow row3 = sheet.CreateRow(2); ICell cell5 = row3.CreateCell(0); cell5.SetCellValue("Ben"); ICell cell6 = row3.CreateCell(1); cell6.SetCellValue(9); ICell cell9 = row3.CreateCell(2); cell9.SetCellValue("Bepa"); ICell cell12 = row1.CreateCell(3); cell12.SetCellValue(12.12); XSSFName namedRange = sheet.Workbook.CreateName() as XSSFName; namedRange.RefersToFormula = (/*setter*/ sheet.SheetName + "!" + "A1:C2"); pivotTable = sheet.CreatePivotTable(namedRange, new CellReference("H5")); XSSFSheet offsetSheet = wb.CreateSheet() as XSSFSheet; IRow tableRow_1 = offsetSheet.CreateRow(1); offsetOuterCell = tableRow_1.CreateCell(1); offsetOuterCell.SetCellValue(-1); ICell tableCell_1_1 = tableRow_1.CreateCell(2); tableCell_1_1.SetCellValue("Row #"); ICell tableCell_1_2 = tableRow_1.CreateCell(3); tableCell_1_2.SetCellValue("Exponent"); ICell tableCell_1_3 = tableRow_1.CreateCell(4); tableCell_1_3.SetCellValue("10^Exponent"); IRow tableRow_2 = offsetSheet.CreateRow(2); ICell tableCell_2_1 = tableRow_2.CreateCell(2); tableCell_2_1.SetCellValue(0); ICell tableCell_2_2 = tableRow_2.CreateCell(3); tableCell_2_2.SetCellValue(0); ICell tableCell_2_3 = tableRow_2.CreateCell(4); tableCell_2_3.SetCellValue(1); IRow tableRow_3 = offsetSheet.CreateRow(3); ICell tableCell_3_1 = tableRow_3.CreateCell(2); tableCell_3_1.SetCellValue(1); ICell tableCell_3_2 = tableRow_3.CreateCell(3); tableCell_3_2.SetCellValue(1); ICell tableCell_3_3 = tableRow_3.CreateCell(4); tableCell_3_3.SetCellValue(10); IRow tableRow_4 = offsetSheet.CreateRow(4); ICell tableCell_4_1 = tableRow_4.CreateCell(2); tableCell_4_1.SetCellValue(2); ICell tableCell_4_2 = tableRow_4.CreateCell(3); tableCell_4_2.SetCellValue(2); ICell tableCell_4_3 = tableRow_4.CreateCell(4); tableCell_4_3.SetCellValue(100); namedRange = sheet.Workbook.CreateName() as XSSFName; namedRange.RefersToFormula = (/*setter*/ "C2:E4"); namedRange.SheetIndex = (/*setter*/ sheet.Workbook.GetSheetIndex(sheet)); offsetPivotTable = offsetSheet.CreatePivotTable(namedRange, new CellReference("C6")); }
public void SaveLoadNew() { XSSFWorkbook wb1 = new XSSFWorkbook(); //check that the default date system is Set to 1900 CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr; Assert.IsNotNull(pr); Assert.IsTrue(pr.IsSetDate1904()); Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system"); ISheet sheet1 = wb1.CreateSheet("sheet1"); ISheet sheet2 = wb1.CreateSheet("sheet2"); wb1.CreateSheet("sheet3"); IRichTextString rts = wb1.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, wb1.GetSheetAt(0).FirstRowNum); Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum); Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum); Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum); Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum); Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum); FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx"); Stream out1 = File.OpenWrite(file.FullName); wb1.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); wb1.Close(); // Load back the XSSFWorkbook XSSFWorkbook wb2 = new XSSFWorkbook(pkg); Assert.AreEqual(3, wb2.NumberOfSheets); Assert.IsNotNull(wb2.GetSheetAt(0)); Assert.IsNotNull(wb2.GetSheetAt(1)); Assert.IsNotNull(wb2.GetSheetAt(2)); Assert.IsNotNull(wb2.GetSharedStringSource()); Assert.IsNotNull(wb2.GetStylesSource()); Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum); Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum); Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum); Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum); Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum); Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum); sheet1 = wb2.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(); Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files."); }
public void XSSFTextParagraph_() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/ "Arial"); rt.ApplyFont(font); shape.SetText(rt); List <XSSFTextParagraph> paras = shape.TextParagraphs; Assert.AreEqual(1, paras.Count); XSSFTextParagraph text = paras[(0)]; Assert.AreEqual("Test String", text.Text); Assert.IsFalse(text.IsBullet); Assert.IsNotNull(text.GetXmlObject()); Assert.AreEqual(shape.GetCTShape(), text.ParentShape); Assert.IsNotNull(text.GetEnumerator()); Assert.IsNotNull(text.AddLineBreak()); Assert.IsNotNull(text.TextRuns); Assert.AreEqual(2, text.TextRuns.Count); text.AddNewTextRun(); Assert.AreEqual(3, text.TextRuns.Count); Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextAlign = TextAlign.None; Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextAlign = (/*setter*/ TextAlign.CENTER); Assert.AreEqual(TextAlign.CENTER, text.TextAlign); text.TextAlign = (/*setter*/ TextAlign.RIGHT); Assert.AreEqual(TextAlign.RIGHT, text.TextAlign); text.TextAlign = TextAlign.None; Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextFontAlign = (/*setter*/ TextFontAlign.BASELINE); Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); text.TextFontAlign = (/*setter*/ TextFontAlign.BOTTOM); Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign); text.TextFontAlign = TextFontAlign.None; Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); text.TextFontAlign = TextFontAlign.None; Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); Assert.IsNull(text.BulletFont); text.BulletFont = (/*setter*/ "Arial"); Assert.AreEqual("Arial", text.BulletFont); Assert.IsNull(text.BulletCharacter); text.BulletCharacter = (/*setter*/ "."); Assert.AreEqual(".", text.BulletCharacter); //Assert.IsNull(text.BulletFontColor); Assert.AreEqual(Color.Empty, text.BulletFontColor); text.BulletFontColor = (/*setter*/ color); Assert.AreEqual(color, text.BulletFontColor); Assert.AreEqual(100.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ 1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ 1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ -9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ -9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ 1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ -9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); Assert.AreEqual(0.0, text.Indent, 0.01); text.Indent = (/*setter*/ 2.0); Assert.AreEqual(2.0, text.Indent, 0.01); text.Indent = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.Indent, 0.01); text.Indent = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.Indent, 0.01); Assert.AreEqual(0.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/ 3.0); Assert.AreEqual(3.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.LeftMargin, 0.01); Assert.AreEqual(0.0, text.RightMargin, 0.01); text.RightMargin = (/*setter*/ 4.5); Assert.AreEqual(4.5, text.RightMargin, 0.01); text.RightMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.RightMargin, 0.01); text.RightMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.RightMargin, 0.01); Assert.AreEqual(0.0, text.DefaultTabSize, 0.01); Assert.AreEqual(0.0, text.GetTabStop(0), 0.01); text.AddTabStop(3.14); Assert.AreEqual(3.14, text.GetTabStop(0), 0.01); Assert.AreEqual(100.0, text.LineSpacing, 0.01); text.LineSpacing = (/*setter*/ 3.15); Assert.AreEqual(3.15, text.LineSpacing, 0.01); text.LineSpacing = (/*setter*/ -2.13); Assert.AreEqual(-2.13, text.LineSpacing, 0.01); Assert.AreEqual(0.0, text.SpaceBefore, 0.01); text.SpaceBefore = (/*setter*/ 3.17); Assert.AreEqual(3.17, text.SpaceBefore, 0.01); text.SpaceBefore = (/*setter*/ -4.7); Assert.AreEqual(-4.7, text.SpaceBefore, 0.01); Assert.AreEqual(0.0, text.SpaceAfter, 0.01); text.SpaceAfter = (/*setter*/ 6.17); Assert.AreEqual(6.17, text.SpaceAfter, 0.01); text.SpaceAfter = (/*setter*/ -8.17); Assert.AreEqual(-8.17, text.SpaceAfter, 0.01); Assert.AreEqual(0, text.Level); text.Level = (/*setter*/ 1); Assert.AreEqual(1, text.Level); text.Level = (/*setter*/ 4); Assert.AreEqual(4, text.Level); Assert.IsTrue(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.IsBullet = (false); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.IsBullet = (true); Assert.IsTrue(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); Assert.AreEqual(0, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN); Assert.IsTrue(text.IsBullet); Assert.IsTrue(text.IsBulletAutoNumber); //Assert.AreEqual(0, text.BulletAutoNumberStart); //This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1; Assert.AreEqual(1, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10); Assert.IsTrue(text.IsBullet); Assert.IsTrue(text.IsBulletAutoNumber); Assert.AreEqual(10, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme); Assert.IsNotNull(text.ToString()); new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape()); } finally { wb.Close(); } }
public void Bug58245_XSSFSheetIterator() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet(); // ===================================================================== // Case 1: Existing code uses XSSFSheet for-each loop // ===================================================================== // Original code (no longer valid) /* * for (XSSFSheet sh : wb) { * sh.createRow(0); * } */ // Option A: foreach (XSSFSheet sh in wb) { sh.CreateRow(0); } // Option B (preferred for new code): foreach (ISheet sh in wb) { sh.CreateRow(0); } // ===================================================================== // Case 2: Existing code creates an iterator variable // ===================================================================== // Original code (no longer valid) /* * Iterator<XSSFSheet> it = wb.iterator(); * XSSFSheet sh = it.next(); * sh.createRow(0); */ // Option A: { IEnumerator <XSSFSheet> it = wb.GetEnumerator() as IEnumerator <XSSFSheet>; XSSFSheet sh = it.Current; sh.CreateRow(0); } // Option B: { //IEnumerator<XSSFSheet> it = wb.XssfSheetIterator(); //XSSFSheet sh = it.Current; //sh.CreateRow(0); } // Option C (preferred for new code): { IEnumerator <ISheet> it = wb.GetEnumerator() as IEnumerator <ISheet>; ISheet sh = it.Current; sh.CreateRow(0); } wb.Close(); }