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