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