private void WriteDemandExcel(IWorkbook workbook, string fullFileName, string[] nameCells, string[] valueCells) { ISheet sheet = workbook.CreateSheet("Sheet"); ICreationHelper creationHelper = workbook.GetCreationHelper(); var font = workbook.CreateFont(); font.IsBold = true; var style = workbook.CreateCellStyle(); style.SetFont(font); IRow namesRow = sheet.CreateRow(0); for (int i = 0; i < nameCells.Length; i++) { ICell cell = namesRow.CreateCell(i); cell.SetCellValue(creationHelper.CreateRichTextString(nameCells[i])); cell.CellStyle = style; } IRow valuesRow = sheet.CreateRow(1); for (int i = 0; i < valueCells.Length; i++) { ICell cell = valuesRow.CreateCell(i); cell.SetCellValue(creationHelper.CreateRichTextString(valueCells[i])); } using (FileStream fs = new FileStream(fullFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } }
public void TestQuickGuide() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ICreationHelper factory = wb.GetCreationHelper(); ISheet sheet = wb.CreateSheet(); ICell cell = sheet.CreateRow(3).CreateCell(5); cell.SetCellValue("F4"); IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = factory.CreateClientAnchor(); IComment comment = drawing.CreateCellComment(anchor); IRichTextString str = factory.CreateRichTextString("Hello, World!"); comment.String = (str); comment.Author = ("Apache POI"); //assign the comment to the cell cell.CellComment = (comment); wb = _testDataProvider.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0); cell = sheet.GetRow(3).GetCell(5); comment = cell.CellComment; Assert.IsNotNull(comment); Assert.AreEqual("Hello, World!", comment.String.String); Assert.AreEqual("Apache POI", comment.Author); Assert.AreEqual(3, comment.Row); Assert.AreEqual(5, comment.Column); }
public void TestCloneSheetMultipleTimes() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ICreationHelper factory = workbook.GetCreationHelper(); ISheet sheet = workbook.CreateSheet("Test Clone"); IRow row = sheet.CreateRow(0); ICell 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 TestNamedCell_2() { // Setup for this Testcase String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; IWorkbook wb = _testDataProvider.CreateWorkbook(); ICreationHelper factory = wb.GetCreationHelper(); ISheet sheet = wb.CreateSheet(sname); sheet.CreateRow(0).CreateCell(0).SetCellValue(factory.CreateRichTextString(cvalue)); // create named range for a single cell using cellreference IName namedCell = wb.CreateName(); namedCell.NameName = (cname); String reference = sname + "!A1"; namedCell.RefersToFormula = (reference); // retrieve the newly Created named range int namedCellIdx = wb.GetNameIndex(cname); IName aNamedCell = wb.GetNameAt(namedCellIdx); Assert.IsNotNull(aNamedCell); // retrieve the cell at the named range and Test its contents CellReference cref = new CellReference(aNamedCell.RefersToFormula); Assert.IsNotNull(cref); ISheet s = wb.GetSheet(cref.SheetName); IRow r = sheet.GetRow(cref.Row); ICell c = r.GetCell(cref.Col); String contents = c.RichStringCellValue.String; Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference"); }
public void GetCellComment() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); ICreationHelper factory = wb.GetCreationHelper(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(1); // cell does not have a comment Assert.IsNull(cell.CellComment); // add a cell comment IClientAnchor anchor = factory.CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + 1; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 3; IDrawing drawing = sheet.CreateDrawingPatriarch(); IComment comment = drawing.CreateCellComment(anchor); IRichTextString str = factory.CreateRichTextString("Hello, World!"); comment.String = str; comment.Author = "Apache POI"; cell.CellComment = comment; // ideally assertSame, but XSSFCell creates a new XSSFCellComment wrapping the same bean for every call to getCellComment. Assert.AreEqual(comment, cell.CellComment); wb.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(); }
void Serialize() { using (FileStream stream = new FileStream(@"Cust_Data.xlsx", FileMode.Create, FileAccess.Write)) { IWorkbook wb = new XSSFWorkbook(); ICreationHelper cH = wb.GetCreationHelper(); int k = 0; foreach (String subSchema in subSchemas) { ISheet sheet = wb.CreateSheet(subSchema); for (int i = 0; i < worksheets[k].Rows.Count; i++) { IRow row = sheet.CreateRow(i); int j = 0; foreach (Object cellVal in worksheets[k].Rows[i].ItemArray) { ICell cell = row.CreateCell(j); cell.SetCellValue(cH.CreateRichTextString(worksheets[k].Rows[i].ItemArray[j].ToString())); j++; } } k++; } wb.Write(stream); } }
public void TestCloneSheet() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ICreationHelper factory = workbook.GetCreationHelper(); ISheet sheet = workbook.CreateSheet("Test Clone"); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); ICell cell2 = row.CreateCell(1); cell.SetCellValue(factory.CreateRichTextString("Clone_test")); cell2.CellFormula = (/*setter*/ "SIN(1)"); ISheet clonedSheet = workbook.CloneSheet(0); IRow 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 = (/*setter*/ "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 AttemptToSave2CommentsWithSameCoordinates() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); ICreationHelper factory = wb.GetCreationHelper(); IDrawing patriarch = sh.CreateDrawingPatriarch(); patriarch.CreateCellComment(factory.CreateClientAnchor()); try { patriarch.CreateCellComment(factory.CreateClientAnchor()); _testDataProvider.WriteOutAndReadBack(wb); Assert.Fail("Expected InvalidOperationException(found multiple cell comments for cell $A$1"); } catch (InvalidOperationException e) { // HSSFWorkbooks fail when writing out workbook Assert.AreEqual(e.Message, "found multiple cell comments for cell A1"); } catch (ArgumentException e) { // XSSFWorkbooks fail when creating and setting the cell address of the comment Assert.AreEqual(e.Message, "Multiple cell comments in one cell are not allowed, cell: A1"); } finally { wb.Close(); } }
private IComment insertComment(IDrawing Drawing, ICell cell, String message) { ICreationHelper factory = cell.Sheet.Workbook.GetCreationHelper(); IClientAnchor anchor = factory.CreateClientAnchor(); anchor.Col1 = (/*setter*/ cell.ColumnIndex); anchor.Col2 = (/*setter*/ cell.ColumnIndex + 1); anchor.Row1 = (/*setter*/ cell.RowIndex); anchor.Row2 = (/*setter*/ cell.RowIndex + 1); anchor.Dx1 = (/*setter*/ 100); anchor.Dx2 = (/*setter*/ 100); anchor.Dy1 = (/*setter*/ 100); anchor.Dy2 = (/*setter*/ 100); IComment comment = Drawing.CreateCellComment(anchor); IRichTextString str = factory.CreateRichTextString(message); comment.String = (/*setter*/ str); comment.Author = (/*setter*/ "fanfy"); cell.CellComment = (/*setter*/ comment); return(comment); }
public void TestModifyComments() { IWorkbook wb = _testDataProvider.OpenSampleWorkbook("SimpleWithComments." + _testDataProvider.StandardFileNameExtension); ICreationHelper factory = wb.GetCreationHelper(); ISheet sheet = wb.GetSheetAt(0); ICell cell; IRow row; IComment comment; for (int rownum = 0; rownum < 3; rownum++) { row = sheet.GetRow(rownum); cell = row.GetCell(1); comment = cell.CellComment; comment.Author = ("Mofified[" + rownum + "] by Yegor"); comment.String = (factory.CreateRichTextString("Modified comment at row " + rownum)); } wb = _testDataProvider.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0); for (int rownum = 0; rownum < 3; rownum++) { row = sheet.GetRow(rownum); cell = row.GetCell(1); comment = cell.CellComment; Assert.AreEqual("Mofified[" + rownum + "] by Yegor", comment.Author); Assert.AreEqual("Modified comment at row " + rownum, comment.String.String); } }
private static void PostilAdd(Tk5ListMetaData metaInfos, ImportError importError, IWorkbook workBook, ISheet sheet) { IDrawing part = sheet.CreateDrawingPatriarch(); Dictionary <string, int> indexOfName = new Dictionary <string, int>(); int i = 0; foreach (var info in metaInfos.Table.TableList) { indexOfName.Add(info.DisplayName, i); i++; } foreach (var err in importError) { IRow row = sheet.GetRow(err.IndexOfRow); IComment comment = null; ICell cell = row.GetCell(indexOfName[err.ColumnName]); ICreationHelper factory = workBook.GetCreationHelper(); IClientAnchor anchor = null; anchor = factory.CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex + 2; anchor.Col2 = cell.ColumnIndex + 4; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 3; comment = part.CreateCellComment(anchor); comment.Author = "mitu"; comment.String = new HSSFRichTextString(err.ErrorMsg); cell.CellComment = comment; } }
public static MemoryStream CreateExcelFromDataTable(DataTable dt) { using (var stream = new MemoryStream()) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); ICreationHelper cH = wb.GetCreationHelper(); IRow header = sheet.CreateRow(0); for (int h = 0; h < dt.Columns.Count; h++) { ICell cell = header.CreateCell(h); cell.SetCellValue(cH.CreateRichTextString(dt.Columns[h].ColumnName)); } for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i].ItemArray[j].ToString())); } } for (int i = 0; i < dt.Columns.Count; i++) { sheet.AutoSizeColumn(i); } wb.Write(stream); return(stream); } }
/// <summary> /// 设置批注 /// </summary> /// <param name="cell"></param> /// <param name="errorMsg"></param> /// <param name="excelGlobalDTO"></param> public void SetCellComment(ICell cell, string errorMsg, ExcelGlobalDTO <TEntity> excelGlobalDTO) { ICreationHelper facktory = cell.Row.Sheet.Workbook.GetCreationHelper(); if (cell.CellComment == null) { //创建批注区域 IDrawing patr = cell.Row.Sheet.CreateDrawingPatriarch(); var anchor = facktory.CreateClientAnchor(); //设置批注区间大小 anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + 2; //设置列 anchor.Row1 = cell.RowIndex; anchor.Row2 = cell.RowIndex + 3; cell.CellComment = patr.CreateCellComment(anchor); } if (excelGlobalDTO.ExcelVersionEnum == ExcelVersionEnum.V2003) { cell.CellComment.String = new HSSFRichTextString(errorMsg);//2003批注方式 } else { cell.CellComment.String = new XSSFRichTextString(errorMsg);//2007批准方式 } cell.CellComment.Author = "yank"; }
public void TestToString() { IWorkbook wb = _testDataProvider.CreateWorkbook(); IRow r = wb.CreateSheet("Sheet1").CreateRow(0); ICreationHelper factory = wb.GetCreationHelper(); r.CreateCell(0).SetCellValue(true); r.CreateCell(1).SetCellValue(1.5); r.CreateCell(2).SetCellValue(factory.CreateRichTextString("Astring")); r.CreateCell(3).SetCellErrorValue((byte)ErrorConstants.ERROR_DIV_0); r.CreateCell(4).CellFormula = ("A1+B1"); Assert.AreEqual("TRUE", r.GetCell(0).ToString(), "Boolean"); Assert.AreEqual("1.5", r.GetCell(1).ToString(), "Numeric"); Assert.AreEqual("Astring", r.GetCell(2).ToString(), "String"); Assert.AreEqual("#DIV/0!", r.GetCell(3).ToString(), "Error"); Assert.AreEqual("A1+B1", r.GetCell(4).ToString(), "Formula"); //Write out the file, read it in, and then check cell values wb = _testDataProvider.WriteOutAndReadBack(wb); r = wb.GetSheetAt(0).GetRow(0); Assert.AreEqual("TRUE", r.GetCell(0).ToString(), "Boolean"); Assert.AreEqual("1.5", r.GetCell(1).ToString(), "Numeric"); Assert.AreEqual("Astring", r.GetCell(2).ToString(), "String"); Assert.AreEqual("#DIV/0!", r.GetCell(3).ToString(), "Error"); Assert.AreEqual("A1+B1", r.GetCell(4).ToString(), "Formula"); }
/// <summary> /// Создаем файл с путешественниками /// </summary> /// <param name="codeMO"></param> /// <param name="listTraveller"></param> public static async Task CreateFileWithTravellerAsync(string codeMO, List <Traveller> listTraveller) { string nameMO = await GetNameMOAsync(codeMO); PropertyInfo[] properties = new Traveller().ReturnType(); //выкидываем невалидные для имени файла символы nameMO = string.Join("_", nameMO.Split(Path.GetInvalidFileNameChars())); string path = $@"C:\Working\{nameMO}.xlsx"; using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write)) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); ICreationHelper cH = wb.GetCreationHelper(); IRow hederRow = sheet.CreateRow(0); //заполняем первую строку с названиями полей for (int i = 0; i < properties.Length; i++) { ICell cell = hederRow.CreateCell(i); cell.CellStyle.FillBackgroundColor = 1; cell.SetCellValue(properties[i].Name); } //заполняем строки с данными о прибывших for (int i = 0; i < listTraveller.Count; i++) { IRow row = sheet.CreateRow(i); for (int j = 0; j < properties.Length; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(listTraveller[i].GetType().GetProperty(properties[j].Name).GetValue(listTraveller[i], null).ToString()); } } wb.Write(stream); } }
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet) { var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2); sheet.AddMergedRegion(merge); var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var caminho = $"{diretorioAtual}/Recursos/logoVLI.png"; byte[] data = ArquivosUtil.RetornarArquivo(caminho); int pictureIndex = workbook.AddPicture(data, PictureType.JPEG); ICreationHelper helper = workbook.GetCreationHelper(); IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = helper.CreateClientAnchor(); anchor.Col1 = 1; anchor.Row1 = 1; IPicture picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/ return(sheet); }
public void TestBasicTypes() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); ICreationHelper CreateHelper = wb1.GetCreationHelper(); ICell cell; IHyperlink link; ISheet sheet = wb1.CreateSheet("Hyperlinks"); //URL cell = sheet.CreateRow(0).CreateCell((short)0); cell.SetCellValue("URL Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.Url); link.Address = ("http://poi.apache.org/"); cell.Hyperlink = (link); //link to a file in the current directory cell = sheet.CreateRow(1).CreateCell((short)0); cell.SetCellValue("File Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.File); link.Address = ("hyperinks-beta4-dump.txt"); cell.Hyperlink = (link); //e-mail link cell = sheet.CreateRow(2).CreateCell((short)0); cell.SetCellValue("Email Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.Email); //note, if subject Contains white spaces, make sure they are url-encoded link.Address = ("mailto:[email protected]?subject=Hyperlinks"); cell.Hyperlink = (link); //link to a place in this workbook //create a target sheet and cell ISheet sheet2 = wb1.CreateSheet("Target Sheet"); sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("Target Cell"); cell = sheet.CreateRow(3).CreateCell((short)0); cell.SetCellValue("Worksheet Link"); link = CreateHelper.CreateHyperlink(HyperlinkType.Document); link.Address = ("'Target Sheet'!A1"); cell.Hyperlink = (link); IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1); sheet = wb2.GetSheetAt(0); link = sheet.GetRow(0).GetCell(0).Hyperlink; Assert.AreEqual("http://poi.apache.org/", link.Address); link = sheet.GetRow(1).GetCell(0).Hyperlink; Assert.AreEqual("hyperinks-beta4-dump.txt", link.Address); link = sheet.GetRow(2).GetCell(0).Hyperlink; Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address); link = sheet.GetRow(3).GetCell(0).Hyperlink; Assert.AreEqual("'Target Sheet'!A1", link.Address); wb2.Close(); }
private void CreateHyperlink(ICreationHelper helper, ICell cell, HyperlinkType linkType, String ref1) { cell.SetCellValue(ref1); IHyperlink link = helper.CreateHyperlink(linkType); link.Address = (ref1); cell.Hyperlink = (link); }
public void TestSetValues() { IWorkbook book = _testDataProvider.CreateWorkbook(); ISheet sheet = book.CreateSheet("test"); IRow row = sheet.CreateRow(0); ICreationHelper factory = book.GetCreationHelper(); ICell cell = row.CreateCell(0); cell.SetCellValue(1.2); Assert.AreEqual(1.2, cell.NumericCellValue, 0.0001); Assert.AreEqual(CellType.Numeric, cell.CellType); AssertProhibitedValueAccess(cell, CellType.Boolean, CellType.String, CellType.Formula, CellType.Error); cell.SetCellValue(false); Assert.AreEqual(false, cell.BooleanCellValue); Assert.AreEqual(CellType.Boolean, cell.CellType); cell.SetCellValue(true); Assert.AreEqual(true, cell.BooleanCellValue); AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.String, CellType.Formula, CellType.Error); cell.SetCellValue(factory.CreateRichTextString("Foo")); Assert.AreEqual("Foo", cell.RichStringCellValue.String); Assert.AreEqual("Foo", cell.StringCellValue); Assert.AreEqual(CellType.String, cell.CellType); AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.Boolean, CellType.Formula, CellType.Error); cell.SetCellValue("345"); Assert.AreEqual("345", cell.RichStringCellValue.String); Assert.AreEqual("345", cell.StringCellValue); Assert.AreEqual(CellType.String, cell.CellType); AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.Boolean, CellType.Formula, CellType.Error); DateTime dt = DateTime.Now.AddMilliseconds(123456789); cell.SetCellValue(dt); Assert.IsTrue((dt.Ticks - cell.DateCellValue.Ticks) >= -20000); Assert.AreEqual(CellType.Numeric, cell.CellType); AssertProhibitedValueAccess(cell, CellType.Boolean, CellType.String, CellType.Formula, CellType.Error); cell.SetCellValue(dt); Assert.IsTrue((dt.Ticks - cell.DateCellValue.Ticks) >= -20000); Assert.AreEqual(CellType.Numeric, cell.CellType); AssertProhibitedValueAccess(cell, CellType.Boolean, CellType.String, CellType.Formula, CellType.Error); cell.SetCellErrorValue(FormulaError.NA.Code); Assert.AreEqual(FormulaError.NA.Code, cell.ErrorCellValue); Assert.AreEqual(CellType.Error, cell.CellType); AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.Boolean, CellType.Formula, CellType.String); }
private static void InsertCellForYear(ICreationHelper cH, ClientData clientDataAllYears, IRow rowClient, string year, int columnIndex) { if (clientDataAllYears.Montants.Any(o => o.Year == year)) { ICell cellClientyear = rowClient.CreateCell(columnIndex); var clientYearData = clientDataAllYears.Montants.First(o => o.Year == year); cellClientyear.SetCellValue(cH.CreateRichTextString(clientYearData.Montant)); } }
public void TestToString() { IWorkbook wb1 = _testDataProvider.CreateWorkbook(); IRow r = wb1.CreateSheet("Sheet1").CreateRow(0); ICreationHelper factory = wb1.GetCreationHelper(); r.CreateCell(0).SetCellValue(false); r.CreateCell(1).SetCellValue(true); r.CreateCell(2).SetCellValue(1.5); r.CreateCell(3).SetCellValue(factory.CreateRichTextString("Astring")); r.CreateCell(4).SetCellErrorValue(FormulaError.DIV0.Code); r.CreateCell(5).SetCellFormula("A1+B1"); r.CreateCell(6); // blank // create date-formatted cell DateTime c = new DateTime(2010, 01, 02, 00, 00, 00); r.CreateCell(7).SetCellValue(c); ICellStyle dateStyle = wb1.CreateCellStyle(); short formatId = wb1.GetCreationHelper().CreateDataFormat().GetFormat("dd-MMM-yyyy"); // m/d/yy h:mm any date format will do dateStyle.DataFormat = formatId; r.GetCell(7).CellStyle = dateStyle; Assert.AreEqual("FALSE", r.GetCell(0).ToString(), "Boolean"); Assert.AreEqual("TRUE", r.GetCell(1).ToString(), "Boolean"); Assert.AreEqual("1.5", r.GetCell(2).ToString(), "Numeric"); Assert.AreEqual("Astring", r.GetCell(3).ToString(), "String"); Assert.AreEqual("#DIV/0!", r.GetCell(4).ToString(), "Error"); Assert.AreEqual("A1+B1", r.GetCell(5).ToString(), "Formula"); Assert.AreEqual("", r.GetCell(6).ToString(), "Blank"); // toString on a date-formatted cell displays dates as dd-MMM-yyyy, which has locale problems with the month String dateCell1 = r.GetCell(7).ToString(); Assert.IsTrue(dateCell1.StartsWith("02-"), "Date (Day)"); Assert.IsTrue(dateCell1.EndsWith("-2010"), "Date (Year)"); //Write out the file, read it in, and then check cell values IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1); wb1.Close(); r = wb2.GetSheetAt(0).GetRow(0); Assert.AreEqual("FALSE", r.GetCell(0).ToString(), "Boolean"); Assert.AreEqual("TRUE", r.GetCell(1).ToString(), "Boolean"); Assert.AreEqual("1.5", r.GetCell(2).ToString(), "Numeric"); Assert.AreEqual("Astring", r.GetCell(3).ToString(), "String"); Assert.AreEqual("#DIV/0!", r.GetCell(4).ToString(), "Error"); Assert.AreEqual("A1+B1", r.GetCell(5).ToString(), "Formula"); Assert.AreEqual("", r.GetCell(6).ToString(), "Blank"); String dateCell2 = r.GetCell(7).ToString(); Assert.AreEqual(dateCell1, dateCell2, "Date"); wb2.Close(); }
public NPoiExcelService(byte[] content, int sheetNum) { using (Stream stream = new MemoryStream(content)) { workBook = new XSSFWorkbook(stream); sheet = workBook.GetSheetAt(sheetNum); } creationHelper = workBook.GetCreationHelper(); defaultCellStyle = CreateDefaultStyle(); }
/// <summary> /// 导出xls结尾的Excel数据,每个实体必须含有ExcelColNameAttr /// </summary> /// <param name="dataList"></param> /// <param name="outputStream"></param> public static void ListToExcelXlsx <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null) { XSSFWorkbook xssfWorkbook = new XSSFWorkbook(); ICellStyle dateCellStyle = xssfWorkbook.CreateCellStyle(); ICreationHelper creationHelper = xssfWorkbook.GetCreationHelper(); dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss"); foreach (List <T> dataList in dataListArrays) { if (dataList == null || dataList.Count == 0) { throw new Exception("数据列表为空,未能导出数据!"); } List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName); if (excelColInfoList.Count == 0) { throw new Exception("字段列表为空,不能导出数据!"); } XSSFSheet xssfSheet = (XSSFSheet)xssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName)); XSSFRow xssfRow = (XSSFRow)xssfSheet.CreateRow(0); ExcelCol excelCol; int width; int[] colWidths = new int[excelColInfoList.Count]; foreach (ExcelColInfo excelColInfo in excelColInfoList) { excelCol = excelColInfo.ExcelCol; width = SetCellValue(xssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName); if (width > colWidths[excelCol.ColIndex]) { colWidths[excelCol.ColIndex] = width; } } object temp; for (int i = 0, len = dataList.Count; i < len; i++) { temp = dataList[i]; xssfRow = (XSSFRow)xssfSheet.CreateRow(i + 1); foreach (ExcelColInfo excelColInfo in excelColInfoList) { excelCol = excelColInfo.ExcelCol; width = SetCellValue( (XSSFCell)xssfRow.CreateCell(excelCol.ColIndex), excelColInfo.PropertyInfo.GetValue(temp), dateCellStyle ); if (width > colWidths[excelCol.ColIndex]) { colWidths[excelCol.ColIndex] = width; } } } UpdateColWidth(xssfSheet, colWidths); } xssfWorkbook.Write(outputStream); }
internal static void FillRange( IRow[] rows, ICreationHelper creationHelper, DataColumnCollection columns, DataRow dataRow = null) { IterateRange( rows, columns, (cell, colName) => SetCellValue(cell, dataRow?[colName], creationHelper)); }
public void TestCopyCellFrom_CellCopyPolicy_mergeHyperlink() { //setUp_testCopyCellFrom_CellCopyPolicy(); IWorkbook wb = srcCell.Sheet.Workbook; ICreationHelper createHelper = wb.GetCreationHelper(); srcCell.SetCellValue("URL LINK"); IHyperlink link = createHelper.CreateHyperlink(HyperlinkType.Url); link.Address = ("http://poi.apache.org/"); destCell.Hyperlink = (link); // Set link cell style (optional) ICellStyle hlinkStyle = wb.CreateCellStyle(); IFont hlinkFont = wb.CreateFont(); hlinkFont.Underline = FontUnderlineType.Single; hlinkFont.Color = (IndexedColors.Blue.Index); hlinkStyle.SetFont(hlinkFont); destCell.CellStyle = (hlinkStyle); // Pre-condition assumptions. This test is broken if either of these Assert.Fail. Assert.AreSame(srcCell.Sheet, destCell.Sheet, "unit test assumes srcCell and destCell are on the same sheet"); Assert.IsNull(srcCell.Hyperlink); // Merge hyperlink - since srcCell doesn't have a hyperlink, destCell's hyperlink is not overwritten (cleared). CellCopyPolicy policy = new CellCopyPolicy.Builder().MergeHyperlink(true).CopyHyperlink(false).Build(); destCell.CopyCellFrom(srcCell, policy); Assert.IsNull(srcCell.Hyperlink); Assert.IsNotNull(destCell.Hyperlink); Assert.AreSame(link, destCell.Hyperlink); List <IHyperlink> links; links = srcCell.Sheet.GetHyperlinkList(); Assert.AreEqual(1, links.Count, "number of hyperlinks on sheet"); Assert.AreEqual(new CellReference(destCell).FormatAsString(), (links[(0)] as XSSFHyperlink).CellRef, "source hyperlink"); // Merge destCell's hyperlink to srcCell. Since destCell does have a hyperlink, this should copy destCell's hyperlink to srcCell. srcCell.CopyCellFrom(destCell, policy); Assert.IsNotNull(srcCell.Hyperlink); Assert.IsNotNull(destCell.Hyperlink); links = srcCell.Sheet.GetHyperlinkList(); Assert.AreEqual(2, links.Count, "number of hyperlinks on sheet"); Assert.AreEqual(new CellReference(destCell).FormatAsString(), (links[(0)] as XSSFHyperlink).CellRef, "dest hyperlink"); Assert.AreEqual(new CellReference(srcCell).FormatAsString(), (links[(1)] as XSSFHyperlink).CellRef, "source hyperlink"); wb.Close(); }
public static void CopyImage(HSSFSheet sheet, HSSFPictureData picData, int row, int col) { var pictureIdx = sheet.Workbook.AddPicture(picData.Data, (NPOI.SS.UserModel.PictureType)picData.Format); var drawing = sheet.CreateDrawingPatriarch(); ICreationHelper helper = sheet.Workbook.GetCreationHelper(); var anchor = helper.CreateClientAnchor(); anchor.Col1 = col; anchor.Row1 = row; var pict = drawing.CreatePicture(anchor, pictureIdx); pict.Resize(); }
public void Test49658() { // test if inserted EscherMetafileBlip will be read again IWorkbook wb = new HSSFWorkbook(); byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf"); int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF); byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png"); int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG); byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf"); int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; ICreationHelper ch = wb.GetCreationHelper(); IClientAnchor anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 2); anchor.Col2 = (/*setter*/ 5); anchor.Row1 = (/*setter*/ 1); anchor.Row2 = (/*setter*/ 6); patriarch.CreatePicture(anchor, indexEmf); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 2); anchor.Col2 = (/*setter*/ 5); anchor.Row1 = (/*setter*/ 10); anchor.Row2 = (/*setter*/ 16); patriarch.CreatePicture(anchor, indexPng); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 6); anchor.Col2 = (/*setter*/ 9); anchor.Row1 = (/*setter*/ 1); anchor.Row2 = (/*setter*/ 6); patriarch.CreatePicture(anchor, indexWmf); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut)); byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22]; Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22); pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut)); }
public void GetAddress() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh = wb.CreateSheet(); ICreationHelper factory = wb.GetCreationHelper(); IDrawing patriarch = sh.CreateDrawingPatriarch(); IComment comment = patriarch.CreateCellComment(factory.CreateClientAnchor()); Assert.AreEqual(CellAddress.A1, comment.Address); ICell C2 = sh.CreateRow(1).CreateCell(2); C2.CellComment = comment; Assert.AreEqual(new CellAddress("C2"), comment.Address); }
/// <summary> /// 设置批注 /// </summary> /// <param name="errMsg"></param> /// <param name="row"></param> /// <param name="cell"></param> /// <param name="facktory"></param> /// <param name="patr"></param> public static void SetComment(string errMsg, IRow row, ICell cell, ICreationHelper facktory, HSSFPatriarch patr) { var anchor = facktory.CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + 3; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 5; var comment = patr.CreateCellComment(anchor); comment.String = new HSSFRichTextString(errMsg); comment.Author = ("mysoft"); cell.CellComment = (comment); }
// Set the comment on a sheet // private static void setComment(ISheet sheet, ICell cell, IDrawing drawing, String commentText, ICreationHelper helper, IClientAnchor anchor) { //System.out.println("Setting col: " + cell.getColumnIndex() + " and row " + cell.getRowIndex()); anchor.Col1 = (cell.ColumnIndex); anchor.Col2 = (cell.ColumnIndex); anchor.Row1 = (cell.RowIndex); anchor.Row2 = (cell.RowIndex); // get comment, or create if it does not exist // NOTE - only occurs if getCellComment is called first IComment comment = cell.CellComment; //Comment comment = null; if (comment == null) { comment = drawing.CreateCellComment(anchor); } comment.Author = ("Test"); // attach the comment to the cell comment.String = (helper.CreateRichTextString(commentText)); cell.CellComment = (comment); }