public void ExceedNumberFormatLimit()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
StylesTable styles = wb.GetStylesSource();
for (int i = 0; i < styles.MaxNumberOfDataFormats; i++)
{
wb.GetStylesSource().PutNumberFormat("\"test" + i + " \"0");
}
try
{
wb.GetStylesSource().PutNumberFormat("\"anotherformat \"0");
}
catch (InvalidOperationException e)
{
if (e.Message.StartsWith("The maximum number of Data Formats was exceeded."))
{
//expected
}
else
{
throw e;
}
}
}
finally
{
wb.Close();
}
}
public void TestPopulateExisting()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile);
Assert.IsNotNull(workbook.GetStylesSource());
StylesTable st = workbook.GetStylesSource();
Assert.AreEqual(11, st.XfsSize);
Assert.AreEqual(1, st.StyleXfsSize);
Assert.AreEqual(8, st.NumberFormatSize);
int nf1 = st.PutNumberFormat("YYYY-mm-dd");
int nf2 = st.PutNumberFormat("YYYY-mm-DD");
Assert.AreEqual(nf1, st.PutNumberFormat("YYYY-mm-dd"));
st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook)).GetStylesSource();
Assert.AreEqual(11, st.XfsSize);
Assert.AreEqual(1, st.StyleXfsSize);
Assert.AreEqual(10, st.NumberFormatSize);
Assert.AreEqual("YYYY-mm-dd", st.GetNumberFormatAt(nf1));
Assert.AreEqual(nf1, st.PutNumberFormat("YYYY-mm-dd"));
Assert.AreEqual(nf2, st.PutNumberFormat("YYYY-mm-DD"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(workbook));
}
public void TestLoadExisting()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile);
Assert.IsNotNull(workbook.GetStylesSource());
StylesTable st = workbook.GetStylesSource();
doTestExisting(st);
}
public void TestLoadSaveLoad()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile);
Assert.IsNotNull(workbook.GetStylesSource());
StylesTable st = workbook.GetStylesSource();
doTestExisting(st);
st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook)).GetStylesSource();
doTestExisting(st);
}
/**
* Creates a new XSSFRichTextString for you.
*/
public IRichTextString CreateRichTextString(string text)
{
XSSFRichTextString rt = new XSSFRichTextString(text);
rt.SetStylesTableReference(workbook.GetStylesSource());
return(rt);
}
public void decreaseUpperLimitBelowCurrentNumDataFormats()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
StylesTable styles = wb.GetStylesSource();
styles.PutNumberFormat(customDataFormat);
// Try decreasing the upper limit below the current number of formats
try
{
styles.MaxNumberOfDataFormats = (0);
Assert.Fail("Expected to raise InvalidOperationException");
}
catch (InvalidOperationException e)
{
if (e.Message.StartsWith("Cannot set the maximum number of data formats less than the current quantity."))
{
// expected
}
else
{
throw e;
}
}
}
finally
{
wb.Close();
}
}
public void addDataFormatsBeyondUpperLimit()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
StylesTable styles = wb.GetStylesSource();
styles.MaxNumberOfDataFormats = (0);
// Try adding a format beyond the upper limit
try
{
styles.PutNumberFormat("\"test \"0");
Assert.Fail("Expected to raise InvalidOperationException");
}
catch (InvalidOperationException e)
{
if (e.Message.StartsWith("The maximum number of Data Formats was exceeded."))
{
// expected
}
else
{
throw e;
}
}
}
finally
{
wb.Close();
}
}
public void LoadSave()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.AreEqual("dd/mm/yyyy", workbook.GetSheetAt(0).GetRow(1).GetCell(0).RichStringCellValue.String);
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
// Write out, and check
// Load up again, check all still there
XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
Assert.AreEqual("dd/mm/yyyy", wb2.GetSheetAt(0).GetRow(1).GetCell(0).RichStringCellValue.String);
Assert.AreEqual("yyyy/mm/dd", wb2.GetSheetAt(0).GetRow(2).GetCell(0).RichStringCellValue.String);
Assert.AreEqual("yyyy-mm-dd", wb2.GetSheetAt(0).GetRow(3).GetCell(0).RichStringCellValue.String);
Assert.AreEqual("yy/mm/dd", wb2.GetSheetAt(0).GetRow(4).GetCell(0).RichStringCellValue.String);
Assert.IsNotNull(wb2.GetSharedStringSource());
Assert.IsNotNull(wb2.GetStylesSource());
}
public void TestPopulateNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable st = wb.GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(1, st.XfsSize);
Assert.AreEqual(1, st.StyleXfsSize);
Assert.AreEqual(0, st.NumberFormatSize);
int nf1 = st.PutNumberFormat("yyyy-mm-dd");
int nf2 = st.PutNumberFormat("yyyy-mm-DD");
Assert.AreEqual(nf1, st.PutNumberFormat("yyyy-mm-dd"));
st.PutStyle(new XSSFCellStyle(st));
// Save and re-load
st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(2, st.XfsSize);
Assert.AreEqual(1, st.StyleXfsSize);
Assert.AreEqual(2, st.NumberFormatSize);
Assert.AreEqual("yyyy-mm-dd", st.GetNumberFormatAt(nf1));
Assert.AreEqual(nf1, st.PutNumberFormat("yyyy-mm-dd"));
Assert.AreEqual(nf2, st.PutNumberFormat("yyyy-mm-DD"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void GetCellStyleAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
try
{
short i = 0;
//get default style
ICellStyle cellStyleAt = workbook.GetCellStyleAt(i);
Assert.IsNotNull(cellStyleAt);
//get custom style
StylesTable styleSource = workbook.GetStylesSource();
XSSFCellStyle customStyle = new XSSFCellStyle(styleSource);
XSSFFont font = new XSSFFont();
font.FontName = ("Verdana");
customStyle.SetFont(font);
int x = styleSource.PutStyle(customStyle);
cellStyleAt = workbook.GetCellStyleAt((short)x);
Assert.IsNotNull(cellStyleAt);
}
finally
{
}
}
public void removeNumberFormat()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
String fmt = customDataFormat;
short fmtIdx = (short)wb.GetStylesSource().PutNumberFormat(fmt);
ICell cell = wb.CreateSheet("test").CreateRow(0).CreateCell(0);
cell.SetCellValue(5.25);
ICellStyle style = wb.CreateCellStyle();
style.DataFormat = fmtIdx;
cell.CellStyle = style;
Assert.AreEqual(fmt, cell.CellStyle.GetDataFormatString());
Assert.AreEqual(fmt, wb.GetStylesSource().GetNumberFormatAt(fmtIdx));
// remove the number format from the workbook
wb.GetStylesSource().RemoveNumberFormat(fmt);
// number format in CellStyles should be restored to default number format
short defaultFmtIdx = 0;
String defaultFmt = BuiltinFormats.GetBuiltinFormat(0);
Assert.AreEqual(defaultFmtIdx, style.DataFormat);
Assert.AreEqual(defaultFmt, style.GetDataFormatString());
// The custom number format should be entirely removed from the workbook
SortedDictionary <short, String> numberFormats = wb.GetStylesSource().GetNumberFormats() as SortedDictionary <short, String>;
assertNotContainsKey(numberFormats, fmtIdx);
assertNotContainsValue(numberFormats, fmt);
// The default style shouldn't be added back to the styles source because it's built-in
Assert.AreEqual(0, wb.GetStylesSource().NumDataFormats);
cell = null; style = null; numberFormats = null;
wb = XSSFTestDataSamples.WriteOutCloseAndReadBack(wb);
cell = wb.GetSheet("test").GetRow(0).GetCell(0);
style = cell.CellStyle;
// number format in CellStyles should be restored to default number format
Assert.AreEqual(defaultFmtIdx, style.DataFormat);
Assert.AreEqual(defaultFmt, style.GetDataFormatString());
// The custom number format should be entirely removed from the workbook
numberFormats = wb.GetStylesSource().GetNumberFormats() as SortedDictionary <short, String>;
assertNotContainsKey(numberFormats, fmtIdx);
assertNotContainsValue(numberFormats, fmt);
// The default style shouldn't be added back to the styles source because it's built-in
Assert.AreEqual(0, wb.GetStylesSource().NumDataFormats);
}
finally
{
wb.Close();
}
}
public void TestAddNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s = wb.CreateSheet() as XSSFSheet;
Assert.AreEqual(null, wb.GetTheme());
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(null, styles.GetTheme());
styles.EnsureThemesTable();
Assert.IsNotNull(styles.GetTheme());
Assert.IsNotNull(wb.GetTheme());
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
styles = wb.GetStylesSource();
Assert.IsNotNull(styles.GetTheme());
Assert.IsNotNull(wb.GetTheme());
}
/// <summary>
/// 构造方法
/// </summary>
/// <param name="s">文件</param>
public PowerExcel(Stream s)
{
try
{
_workbook = new XSSFWorkbook(s);
_sheet = _workbook.GetSheetAt(0);
_df = new XSSFDataFormat(_workbook.GetStylesSource());
}
catch (Exception) //检测模板异常
{
throw new Exception("模板不正确");
}
}
public void Styles()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
StylesTable ss = workbook.GetStylesSource();
Assert.IsNotNull(ss);
StylesTable st = ss;
// Has 8 number formats
Assert.AreEqual(8, st.NumberFormatSize);
// Has 2 fonts
Assert.AreEqual(2, st.GetFonts().Count);
// Has 2 Fills
Assert.AreEqual(2, st.GetFills().Count);
// Has 1 border
Assert.AreEqual(1, st.GetBorders().Count);
// Add two more styles
Assert.AreEqual(StylesTable.FIRST_CUSTOM_STYLE_ID + 8,
st.PutNumberFormat("testFORMAT"));
Assert.AreEqual(StylesTable.FIRST_CUSTOM_STYLE_ID + 8,
st.PutNumberFormat("testFORMAT"));
Assert.AreEqual(StylesTable.FIRST_CUSTOM_STYLE_ID + 9,
st.PutNumberFormat("testFORMAT2"));
Assert.AreEqual(10, st.NumberFormatSize);
// Save, load back in again, and check
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
ss = workbook.GetStylesSource();
Assert.IsNotNull(ss);
Assert.AreEqual(10, st.NumberFormatSize);
Assert.AreEqual(2, st.GetFonts().Count);
Assert.AreEqual(2, st.GetFills().Count);
Assert.AreEqual(1, st.GetBorders().Count);
}
public void TestBug52348()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("52348.xlsx");
StylesTable st = workbook.GetStylesSource();
Assert.AreEqual(0, st.StyleXfsSize);
XSSFCellStyle style = workbook.CreateCellStyle() as XSSFCellStyle; // no exception at this point
Assert.IsNull(style.GetStyleXf());
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(workbook));
}
public void Existing()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
// And check a few low level bits too
OPCPackage pkg = OPCPackage.Open(HSSFTestDataSamples.OpenSampleFileStream("Formatting.xlsx"));
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared, styles and themes
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(6, wbPart.Relationships.Size);
pkg.Close();
}
public void TestCreateSaveLoad()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable st = wb.GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(1, st.GetXfsSize());
Assert.AreEqual(1, st.GetStyleXfsSize());
Assert.AreEqual(0, st.GetNumberFormatSize());
st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(1, st.GetXfsSize());
Assert.AreEqual(1, st.GetStyleXfsSize());
Assert.AreEqual(0, st.GetNumberFormatSize());
}
public override void Test49928()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("49928.xlsx");
doTest49928Core(wb);
// an attempt to register an existing format returns its index
int poundFmtIdx = wb.GetSheetAt(0).GetRow(0).GetCell(0).CellStyle.DataFormat;
Assert.AreEqual(poundFmtIdx, wb.GetStylesSource().PutNumberFormat(poundFmt));
// now create a custom format with Pound (\u00a3)
IDataFormat dataFormat = wb.CreateDataFormat();
short customFmtIdx = dataFormat.GetFormat("\u00a3##.00[Yellow]");
Assert.IsTrue(customFmtIdx > BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
Assert.AreEqual("\u00a3##.00[Yellow]", dataFormat.GetFormat(customFmtIdx));
}
public void GetFontAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
StylesTable styleSource = workbook.GetStylesSource();
short i = 0;
//get default font
IFont fontAt = workbook.GetFontAt(i);
Assert.IsNotNull(fontAt);
//get customized font
XSSFFont customFont = new XSSFFont();
customFont.IsItalic = (true);
int x = styleSource.PutFont(customFont);
fontAt = workbook.GetFontAt((short)x);
Assert.IsNotNull(fontAt);
}
public void maxNumberOfDataFormats()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
StylesTable styles = wb.GetStylesSource();
// Check default limit
int n = styles.MaxNumberOfDataFormats;
// https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
Assert.IsTrue(200 <= n);
Assert.IsTrue(n <= 250);
// Check upper limit
n = int.MaxValue;
styles.MaxNumberOfDataFormats = (n);
Assert.AreEqual(n, styles.MaxNumberOfDataFormats);
// Check negative (illegal) limits
try
{
styles.MaxNumberOfDataFormats = (-1);
Assert.Fail("Expected to get an IllegalArgumentException(\"Maximum Number of Data Formats must be greater than or equal to 0\")");
}
catch (ArgumentException e)
{
if (e.Message.StartsWith("Maximum Number of Data Formats must be greater than or equal to 0"))
{
// expected
}
else
{
throw e;
}
}
}
finally
{
wb.Close();
}
}
/// <summary>
/// 构造方法 强行切换二开文件
/// </summary>
/// <param name="filePath">模板文件路径</param>
public PowerExcel(string filePath)
{
//如果存在二开文件，切换成二开模式
//todo: 这里还应该去获取是否是二开模式，暂时不知道怎么去获取，就先这样判断
var xFileName = filePath.Replace(Path.GetFileName(filePath), "x_" + Path.GetFileName(filePath));
if (File.Exists(xFileName))
{
filePath = xFileName;
}
if (File.Exists(filePath))
{
File.SetAttributes(filePath, FileAttributes.Normal);
}
var fs = new FileStream(filePath, FileMode.Open);
_workbook = new XSSFWorkbook(fs);
_sheet = _workbook.GetSheetAt(0);
_df = new XSSFDataFormat(_workbook.GetStylesSource());
}
/**
* Set a single paragraph of text on the shape. Note this will replace all existing paragraphs Created on the shape.
* @param str rich text string representing the paragraph text
*/
public void SetText(XSSFRichTextString str)
{
XSSFWorkbook wb = (XSSFWorkbook)GetDrawing().GetParent().GetParent();
str.SetStylesTableReference(wb.GetStylesSource());
CT_TextParagraph p = new CT_TextParagraph();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
rPr.sz = (/*setter*/ 1100);
r.t = (/*setter*/ str.String);
}
else
{
for (int i = 0; i < str.GetCTRst().SizeOfRArray(); i++)
{
CT_RElt lt = str.GetCTRst().GetRArray(i);
CT_RPrElt ltPr = lt.rPr;
if (ltPr == null)
{
ltPr = lt.AddNewRPr();
}
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
ApplyAttributes(ltPr, rPr);
r.t = (/*setter*/ lt.t);
}
}
ClearText();
ctShape.txBody.SetPArray(new CT_TextParagraph[] { p });
_paragraphs.Add(new XSSFTextParagraph(ctShape.txBody.GetPArray(0), ctShape));
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
private void SetupStyles()
{
var myBlueColour = new XSSFColor(new byte[] { 31, 73, 125 });
var styleTable = _workbook.GetStylesSource();
// fonts
var headerFont = _workbook.CreateFont() as XSSFFont;
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerFont.Color = HSSFColor.White.Index;
var generalFont = _workbook.CreateFont() as XSSFFont;
generalFont.SetColor(myBlueColour);
var boldFont = _workbook.CreateFont() as XSSFFont;
boldFont.SetColor(myBlueColour);
boldFont.Boldweight = (short)FontBoldWeight.Bold;
// header
_headerStyle = styleTable.CreateCellStyle();
_headerStyle.SetFont(headerFont);
_headerStyle.FillForegroundXSSFColor = myBlueColour;
_headerStyle.FillPattern = FillPattern.SolidForeground;
//general data
_generalStyle = styleTable.CreateCellStyle();
_generalStyle.SetFont(generalFont);
//general bold data
_generalBoldStyle = styleTable.CreateCellStyle();
_generalBoldStyle.SetFont(boldFont);
//general grid data
_generalGridStyle = styleTable.CreateCellStyle();
_generalGridStyle.SetFont(generalFont);
_generalGridStyle.BorderLeft = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.LEFT, myBlueColour);
_generalGridStyle.BorderTop = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.TOP, myBlueColour);
_generalGridStyle.BorderRight = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.RIGHT, myBlueColour);
_generalGridStyle.BorderBottom = BorderStyle.Thin;
_generalGridStyle.SetBorderColor(BorderSide.BOTTOM, myBlueColour);
_generalGridStyle.SetVerticalAlignment((short)VerticalAlignment.Top);
_generalGridStyle.WrapText = true;
//Date grid data
_dateGridStyle = styleTable.CreateCellStyle();
_dateGridStyle.SetFont(generalFont);
_dateGridStyle.BorderLeft = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.LEFT, myBlueColour);
_dateGridStyle.BorderTop = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.TOP, myBlueColour);
_dateGridStyle.BorderRight = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.RIGHT, myBlueColour);
_dateGridStyle.BorderBottom = BorderStyle.Thin;
_dateGridStyle.SetBorderColor(BorderSide.BOTTOM, myBlueColour);
_dateGridStyle.DataFormat = _workbook.GetCreationHelper()
.CreateDataFormat()
.GetFormat("DateTimeFormat".GetUiTranslation());
_dateGridStyle.Alignment = HorizontalAlignment.Center;
_dateGridStyle.SetVerticalAlignment((short)VerticalAlignment.Top);
}
public async Task <ExcelDataResult> ExportExcelDataToDBAsync(ImportDataRequestArgs requestArgs)
{
var tcs = new TaskCompletionSource <ExcelDataResult>();
try
{
var result = new ExcelDataResult();
var xssfwb = new XSSFWorkbook(requestArgs.DataStream);
var sheet = xssfwb.GetSheetAt(0);
var screeningNr = sheet.GetRow(0);
var headerText = sheet.GetRow(1);
var stylesTable = xssfwb.GetStylesSource();
/*var cell = sheet.GetRow(1).GetCell(4);
* var stylesTable = xssfwb.GetStylesSource();
* var name = stylesTable.GetCellStyleName(cell);
*/
if (screeningNr == null || headerText == null)
{
result.Errors.Add("Invalid Excel format.");
result.Success = false;
return(result);
}
var cropRow = sheet.GetRow(3);
var crop = string.Empty;
var syncCode = string.Empty;
var breedingStation = string.Empty;
if (cropRow == null)
{
result.Success = false;
result.Errors.Add("No Crop Record Found.");
return(result);
}
for (int i = 0; i < headerText.Cells.Count; i++)
{
if (headerText.GetCell(i).ToText().ToLower() == "crop" ||
headerText.GetCell(i).ToText().ToLower() == "cropcode")
{
crop = cropRow.GetCell(i)?.ToString();
}
if (headerText.GetCell(i).ToText().ToLower().Trim() == "brstation")
{
breedingStation = cropRow.GetCell(i)?.ToString();
}
if (!string.IsNullOrWhiteSpace(crop) && !string.IsNullOrWhiteSpace(breedingStation))
{
break;
}
}
if (string.IsNullOrWhiteSpace(crop))
{
result.Success = false;
result.Errors.Add("No Crop Record Found.");
return(result);
}
if (string.IsNullOrWhiteSpace(breedingStation))
{
result.Success = false;
result.Errors.Add("No breeding station found.");
return(result);
}
var dtRowTVP = new DataTable("TVP_Row");
var dtColumnsTVP = new DataTable("TVP_Column");
var dtCellTVP = new DataTable("TVP_Cell");
var dtListTVP = new DataTable("TVP_List");
//prepare list tvp which is only needed for importing data from phenome
dtListTVP.Columns.Add("RowID");
dtListTVP.Columns.Add("GID");
dtListTVP.Columns.Add("EntryCode");
//prepare columns
PrepareColumnTableTVP(dtColumnsTVP, headerText, stylesTable, screeningNr);
var coldata = dtColumnsTVP.AsEnumerable().Select(x => new
{
ColName = x.IsNull("TraitID") ? x["ColumnLabel"] : x["TraitID"]
}).AsEnumerable().Select(x => x.ColName).GroupBy(g => g).Select(x => new
{
ColVal = x.Key,
Count = x.Count()
}).Where(x => x.Count > 1);
if (coldata.Any())
{
var keylist = coldata.Select(x => x.ColVal.ToText()).ToList();
var key = keylist.Truncate();
result.Errors.Add($"Duplicate Column {key}");
}
result.Success = !result.Errors.Any();
if (!result.Success)
{
return(result);
}
//this validation is removed as per client request, and query is adjusted on returning multiple traitname for same determination ID.
//var columnvalidations = await ValidateColumnsForUniqueDeterminations(dtColumnsTVP, crop, requestArgs.Source);
//if (columnvalidations.Tables[0].Rows.Count > 0)
//{
// result.Success = false;
// foreach (DataRow _value in columnvalidations.Tables[0].Rows)
// {
// result.Errors.Add("Multiple determination applied for Trait " + _value["Traits"]);
// }
// return result;
//}
var lastRow = sheet.LastRowNum + 1; //this is because RowNum starts from 0 not from 1.
if (lastRow - 2 > ConfigurationManager.AppSettings["App:MaxNoOfRecords"].ToInt64()) //first two rows data are meta data and should be ignored while importing it so value 2 is subtracted.
{
result.Errors.Add("Cannot import excel file having rows greater than " + ConfigurationManager.AppSettings["App:MaxNoOfRecords"]);
result.Success = false;
return(result);
}
//Prepare rows and cell values
PrepareRowAndCellTVP(dtRowTVP, dtCellTVP, headerText, stylesTable, sheet, result);
var data = dtRowTVP.AsEnumerable().Select(x => x.Field <string>("MaterialKey"))
.GroupBy(g => g)
.Select(
x => new
{
MaterialKey = x.Key,
Count = x.Count()
});
if (data.Any(x => x.MaterialKey.ToText() == string.Empty))
{
result.Errors.Add("Material Key cannot be null or empty");
}
var material = data.FirstOrDefault();
if (material != null)
{
syncCode = material.MaterialKey?.Substring(0, 2);
}
if (string.IsNullOrWhiteSpace(syncCode))
{
result.Errors.Add("SyncCode cannot be null or empty");
}
var keys = data.Where(x => x.Count > 1);
if (keys.Any())
{
var keylist = keys.Select(x => x.MaterialKey).ToList();
var key = keylist.Truncate();
result.Errors.Add($"Duplicate Material key {key}");
}
result.Success = !result.Errors.Any();
if (result.Success)
{
await ImportDataAsync(crop, breedingStation, syncCode, string.Empty, requestArgs, dtColumnsTVP, dtRowTVP, dtCellTVP, dtListTVP);
tcs.SetResult(await GetDataAsync(requestArgs));
}
else
{
tcs.SetResult(result);
}
}
catch (Exception ex)
{
tcs.SetException(ex);
}
return(await tcs.Task);
}
public void SaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.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, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.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);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.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();
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}