public string Read(string inFile) { var stream = File.OpenRead(inFile); var book = new XSSFWorkbook(stream); stream.Close(); var sb = new StringBuilder(); var sheet = book.GetSheetAt(0); int lastRowNum = sheet.LastRowNum; for (int r = 0; r <= lastRowNum; r++) { var datarow = sheet.GetRow(r); { foreach (var cell in datarow.Cells) { switch (cell.CellType) { case CellType.Numeric: sb.Append(cell.NumericCellValue.ToString() + "\t"); break; case CellType.String: sb.Append(cell.StringCellValue.Replace("\n", "") + "\t"); break; default: throw new Exception("?"); } } sb.Append("\r\n"); } } return sb.ToString(); }
/// <summary> /// Gets the field names from a workbook and stores them in a list. /// </summary> /// <param name="fieldNames">list where field names will be stored</param> /// <param name="workbook">workbook from which field names will be extracted</param> public static void GetFieldNames(ImportData data, XSSFWorkbook workbook) { ISheet sheet = workbook.GetSheetAt(0); bool reading = true; int rindex = sheet.FirstRowNum; while (reading) { IRow row = sheet.GetRow(rindex); if (row != null) { ICell cell = row.GetCell(0); if (cell != null) { string s = CellValueAsString(cell); if (s != "" && s[0] == '[') { for (int i = 0; i < row.LastCellNum; i++) { s = CellValueAsString(row.GetCell(i)).TrimEnd(']').TrimStart('['); ; data.fieldNames.Add(s); } // don't read more than one row of field names reading = false; } } } rindex++; if (rindex > sheet.LastRowNum) { reading = false; } } }
public static bool UpdateExcelData(string excelFilePath, string excelName) { XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open)); var sheet = excelBook.GetSheetAt(0); var headerRow = sheet.GetRow(0); //total columns int cellCount = headerRow.LastCellNum; //total rows int rowCount = sheet.LastRowNum; switch (excelName) { case Constant.Water: UpdateWaterData(sheet, cellCount, rowCount); break; case Constant.Air: UpdateAirData(sheet, cellCount, rowCount); break; } try { FileStream writefile = new FileStream("d:\\" + excelName + ".xlsx", FileMode.Create, FileAccess.Write); excelBook.Write(writefile); writefile.Close(); return true; } catch { return false; } }
public ActionResult ExportExcel() { var data = this.rptRepository.All(); string path = Server.MapPath("~/Templates/客戶資訊.xlsx"); FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read); IWorkbook wb = new XSSFWorkbook(stream); stream.Close(); ISheet sheet = wb.GetSheetAt(0); ICellStyle cs = sheet.GetRow(1).Cells[0].CellStyle; int Index = 1; foreach (var item in data) { IRow row = sheet.CreateRow(Index); CreateCell(item.客戶名稱, row, 0, cs); CreateCell(item.聯絡人數量.ToString(), row, 1, cs); CreateCell(item.客戶帳戶數量.ToString(), row, 2, cs); Index++; } string serverPath = @"D:/repot.xlsx"; using (FileStream file = new FileStream(serverPath, FileMode.Create)) { wb.Write(file); file.Close(); } return File(serverPath, "application/excel","Report.xlsx"); }
/// <summary> /// use me for 2003 and 2007 /// </summary> /// <param name="fileName"></param> /// <returns></returns> public DataTable ReadExcelAsTable(string fileName) { try { using (FileStream fs = new FileStream(fileName, FileMode.Open)) { //chk xls SS.UserModel.ISheet sheet; if (fileName.EndsWith("xlsx")) { XSSF.UserModel.XSSFWorkbook wb = new XSSF.UserModel.XSSFWorkbook(fs); //for 2007 sheet = wb.GetSheetAt(0); //read first sheet } else { SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook(fs); //for 2003 sheet = wb.GetSheetAt(0); //read first sheet } return(ReadSheet(sheet)); } } catch (Exception ex) { throw ex; } }
public Dictionary<DateTime, List<SaleParsed>> ImportFromSheet(string path, DateTime dateTime, IEnumerable<int> sheetNumbers, IEnumerable<GoodType> goodTypes, string producer) { this.goodTypes = goodTypes; this.producer = producer; var result = new Dictionary<DateTime, List<SaleParsed>>(); using (var fs = File.OpenRead(path)) { var workbook = new XSSFWorkbook(fs); foreach (var sNum in sheetNumbers) { ISheet sheet = workbook.GetSheetAt(sNum); var sales = ParseSales(sheet, dateTime); foreach (var s in sales) { if (result.ContainsKey(s.Key)) { result[s.Key].AddRange(s.Value); } else { result.Add(s.Key, s.Value); } } } } return result; }
public void TestBug48936() { IWorkbook w = new XSSFWorkbook(); ISheet s = w.CreateSheet(); int i = 0; List<String> lst = ReadStrings("48936-strings.txt"); foreach (String str in lst) { s.CreateRow(i++).CreateCell(0).SetCellValue(str); } try { w = XSSFTestDataSamples.WriteOutAndReadBack(w); } catch (POIXMLException) { Assert.Fail("Detected Bug #48936"); } s = w.GetSheetAt(0); i = 0; foreach (String str in lst) { String val = s.GetRow(i++).GetCell(0).StringCellValue; Assert.AreEqual(str, val); } }
public static int[][][] ReadExcelPattern(string filepath) { using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); int sheetnumber = workbook.NumberOfSheets; int [][][] sheetelements = new int[sheetnumber][][]; for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){ ISheet _isheet = workbook.GetSheetAt(sheetindex); int lastrownum = _isheet.LastRowNum; int [][] rowelements = new int[lastrownum+1][]; for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){ IRow row = _isheet.GetRow(rowindex); if(row == null)continue; int lastcellnum = row.LastCellNum; int[] cellelements = new int[lastcellnum+1]; for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){ ICell cell = row.GetCell(cellindex); if(cell != null){ cellelements[cellindex] = Convert.ToInt32(cell.ToString()); Debug.Log(cellelements[cellindex]); } } rowelements[rowindex] = cellelements; } sheetelements[sheetindex] = rowelements; } return sheetelements; } }
public void TestLoadStyles() { XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("styles.xlsx")); ISheet sheet = workbook.GetSheetAt(0); IRow row = sheet.GetRow(0); ICell cell = row.GetCell((short)0); ICellStyle style = cell.CellStyle; // assertNotNull(style); }
/// <summary> /// Get the parsable rows from a workbook /// </summary> /// <param name="rows">a reference to an object that will contain parsable rows</param> /// <param name="workbook">the workbook to extract rows from</param> public static void GetParsableRows(ref List<ParsableRow> rows, XSSFWorkbook workbook) { ISheet sheet = workbook.GetSheetAt(0); bool reading = true; int rindex = sheet.FirstRowNum; while (reading) { IRow row = sheet.GetRow(rindex); // if there is a row, and it's not empty if (row != null && row.Cells.Count > 0) { // check to see if the row is parsable by looking at the first value ICell firstCell = row.GetCell(0); if (firstCell != null) { string firstCellVal = CellValueAsString(firstCell); // treat the row as parsable if it is: // - not a title // - not metadata // - not a variable if (firstCellVal[0] != '[' && firstCellVal[0] != '#' && firstCellVal[0] != '$') { // cells are stored in parsable row objects ParsableRow pr = new ParsableRow(); // copy the line number to the parsable row object pr.linenumber = rindex; // make an array which is as long as the last cell number - note that NPOI will show cells **up to** the last cell that // contains data, so if a row in the sheet only has a single value in the fourth column, that row will return null for all // indices up to 2, then a value for index 3. pr.cells = new string[row.LastCellNum]; // for each cell in the row, convert it to a string and copy it to the parsable row object for (int i = 0; i < row.LastCellNum; i++) { pr.cells[i] = CellValueAsString(row.GetCell(i)); } // add the parsable row object we just set up to the list of rows that was passed to the method rows.Add(pr); } } } rindex++; // check to make sure we're not out of bounds if (rindex > sheet.LastRowNum) { reading = false; } } }
private void button1_Click(object sender, EventArgs e) { // LoadTemplate(); //FileStream file = new FileStream(@"template/Astro2_Sys WHCK Status - 0712-SA2.xlsx", FileMode.Open, FileAccess.ReadWrite); FileStream file = new FileStream(@"template/Astro2_Sys WHCK Status - 0712-SA2.xlsx", FileMode.Open, FileAccess.ReadWrite); IWorkbook wb = new XSSFWorkbook(file); MessageBox.Show(wb.NumberOfSheets+"", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); if (wb.NumberOfSheets >= 2) { ISheet sheet1 = wb.GetSheetAt(1); sheet1.GetRow(7).GetCell(6).SetCellValue("Failed"); //Write the stream data of workbook to the root directory //FileStream sw = new FileStream(@"test.xls", FileMode.Create); FileStream sw = new FileStream(@"test.xls", FileMode.OpenOrCreate); wb.Write(file); sw.Close(); } //create cell on rows, since rows do already exist,it's not necessary to create rows again. // sheet1.GetRow(8).GetCell(7).SetCellValue("Failed"); /* sheet1.GetRow(2).GetCell(1).SetCellValue(300); sheet1.GetRow(3).GetCell(1).SetCellValue(500050); sheet1.GetRow(4).GetCell(1).SetCellValue(8000); sheet1.GetRow(5).GetCell(1).SetCellValue(110); sheet1.GetRow(6).GetCell(1).SetCellValue(100); sheet1.GetRow(7).GetCell(1).SetCellValue(200); sheet1.GetRow(8).GetCell(1).SetCellValue(210); sheet1.GetRow(9).GetCell(1).SetCellValue(2300); sheet1.GetRow(10).GetCell(1).SetCellValue(240); sheet1.GetRow(11).GetCell(1).SetCellValue(180123); sheet1.GetRow(12).GetCell(1).SetCellValue(150); * */ //Force excel to recalculate all the formula while open //sheet1.ForceFormulaRecalculation = true; //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls")); //Response.BinaryWrite(ms.ToArray()); // WriteToFile(); // wb = null; //ms.Close(); //ms.Dispose(); }
public static void CreateLoadScriptableObject() { string filepath = AssetDatabase.GetAssetPath(Selection.activeObject); string createpath = AssetDatabase.GenerateUniqueAssetPath("Assets/PartA/Resources/Pattern/" + Selection.activeObject.name +".asset"); ExcelData exceldata = ScriptableObject.CreateInstance<ExcelData>(); //List<_Row> _sheet_list = new List<_Row>(); using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); int sheetnumber = workbook.NumberOfSheets; int [][][] sheetelements = new int[sheetnumber][][]; exceldata.data = new int[sheetnumber][][]; for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){ ISheet _isheet = workbook.GetSheetAt(sheetindex); int lastrownum = _isheet.LastRowNum; int [][] rowelements = new int[lastrownum + 1][]; _Row localrow = new _Row(); for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){ IRow row = _isheet.GetRow(rowindex); if(row == null)continue; int lastcellnum = row.LastCellNum; int[] cellelements = new int[lastcellnum + 1]; _Cell localcell = new _Cell(); for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){ ICell cell = row.GetCell(cellindex); if(cell != null){ //cellelements[cellindex] = Convert.ToInt32(cell.ToString()); localcell.cell[cellindex] = Convert.ToInt32(cell.ToString()); } } localrow.row.Add(localcell); rowelements[rowindex] = cellelements; } exceldata.sheet.Add(localrow); sheetelements[sheetindex] = rowelements; } //exceldata.data.CopyTo(sheetelements,0); //exceldata.data = (int[][][])sheetelements.Clone(); foreach(_Row cells in exceldata.sheet){ foreach(_Cell test in cells.row){ for(int i = 0;i < 5;i++) Debug.Log(test.cell[i]); } } //exceldata.data = sheetelements; exceldata.check = "Exist"; exceldata.hideFlags = HideFlags.NotEditable; EditorUtility.SetDirty(exceldata); AssetDatabase.CreateAsset(exceldata,createpath); AssetDatabase.SaveAssets(); EditorUtility.FocusProjectWindow(); } }
public void TestLoadSample() { XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("sample.xlsx")); Assert.AreEqual(3, workbook.NumberOfSheets); Assert.AreEqual("Sheet1", workbook.GetSheetName(0)); ISheet sheet = workbook.GetSheetAt(0); IRow row = sheet.GetRow(0); ICell cell = row.GetCell((short)1); Assert.IsNotNull(cell); Assert.AreEqual(111.0, cell.NumericCellValue, 0.0); cell = row.GetCell((short)0); Assert.AreEqual("Lorem", cell.RichStringCellValue.String); }
public void TestCreate() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper; String[] urls = { "http://apache.org/", "www.apache.org", "/temp", "file:///c:/temp", "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"}; for (int i = 0; i < urls.Length; i++) { String s = urls[i]; XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink; link.Address=(s); XSSFCell cell = row.CreateCell(i) as XSSFCell; cell.Hyperlink=(link); } workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri&&rel.TargetUri.IsFile) Assert.AreEqual(urls[i].Replace("file:///","").Replace("/","\\"),rel.TargetUri.LocalPath); else // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); else // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } }
public void SetUp() { if (workbook == null) { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME); OPCPackage pkg = OPCPackage.Open(is1); workbook = new XSSFWorkbook(pkg); sheet = workbook.GetSheetAt(0); } _functionFailureCount = 0; _functionSuccessCount = 0; _EvaluationFailureCount = 0; _EvaluationSuccessCount = 0; }
private void verifyBug54084Unicode(XSSFWorkbook wb) { // expected data is stored in UTF-8 in a text-file byte[] data = HSSFTestDataSamples.GetTestDataFileContent("54084 - Greek - beyond BMP.txt"); String testData = Encoding.UTF8.GetString(data).Trim(); ISheet sheet = wb.GetSheetAt(0); IRow row = sheet.GetRow(0); ICell cell = row.GetCell(0); String value = cell.StringCellValue; //Console.WriteLine(value); Assert.AreEqual(testData, value, "The data in the text-file should exactly match the data that we read from the workbook"); }
protected void btnImport_Click(object sender, EventArgs e) { if (upExcel.HasFile) { string fileName = upExcel.FileName; string fileExt = Path.GetExtension(fileName); if (fileExt.Equals(".xls") || fileExt.Equals(".xlsx")) { string path = Server.MapPath("/File/" + fileName); upExcel.SaveAs(path); using (FileStream file = File.OpenRead(path)) { try { // 根据文件名后缀加载IWorkbook IWorkbook workbook = fileExt == ".xls" ? new HSSFWorkbook(file) : workbook = new XSSFWorkbook(file); ISheet sheet = workbook.GetSheetAt(0); List<AttendanceInfoModel> attcList = new List<AttendanceInfoModel>(); for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); AttendanceInfoModel attc = new AttendanceInfoModel(); attc.UserID = row.Cells[0].ToString(); attc.FaceTime = row.Cells[1].DateCellValue; attcList.Add(attc); } AttendanceInfoBLL bll = new AttendanceInfoBLL(); bll.AppendAttendanceInfo(attcList); ClientScript.RegisterStartupScript(this.GetType(), "import", "alert('保存成功!');parent.dialog.close();", true); } catch { ClientScript.RegisterStartupScript(this.GetType(), "import", "alert('错误信息:\\nExcel的格式不正确,格式:用户ID + 打卡时间。');", true); } finally { if (File.Exists(path)) File.Delete(path); } } } else { ClientScript.RegisterStartupScript(this.GetType(), "import", "alert('错误信息:\\n不支持的文件格式,仅支持Excel2003和2007。');", true); } } }
public override IEnumerable<IFreeDocument> ReadFile(Action<int> alreadyGetSize = null) { XSSFWorkbook hssfworkbook; using (var file = new FileStream(FileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); } ISheet sheet = hssfworkbook.GetSheetAt(0); List<string> titles = null; try { titles= sheet.GetRow(0).Cells.Select(d => d.StringCellValue).ToList(); } catch (Exception ex) { throw new Exception("请填写Excel的表头信息"); } for (int i = 1; i < sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); var data = PluginProvider.GetObjectInstance(DataType) as IFreeDocument; var dict = new Dictionary<string, object>(); for (int index = 0; index < titles.Count; index++) { string title = titles[index]; dict.Add(title, row.GetCell(index).ToString()); } var freeDocument = data as IFreeDocument; if (freeDocument != null) { if (i == 1) { PropertyNames = titles.ToDictionary(d => d, d => d); } } data.DictDeserialize(dict); yield return data; if(i%1000==0) XLogSys.Print.Info($"已经导入数量{i},总共{sheet.LastRowNum}"); } }
public Dictionary<int, string> GetSheets(string path) { var result = new Dictionary<int, string>(); using (var fs = File.OpenRead(path)) { var workbook = new XSSFWorkbook(fs); for (int sheetIdx = 0; sheetIdx < workbook.NumberOfSheets; sheetIdx++) { ISheet sheet = workbook.GetSheetAt(sheetIdx); result.Add(sheetIdx, sheet.SheetName); } } return result; }
public static DataTable GetDataFromExcel(string excelFilePath) { //get exist excel file XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open)); var sheet = excelBook.GetSheetAt(0); var headerRow = sheet.GetRow(0); var table = new DataTable(); //total columns int cellCount = headerRow.LastCellNum; //get column name for (var i = headerRow.FirstCellNum; i < cellCount; i++) { var columnName = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(columnName); } //total rows int rowCount = sheet.LastRowNum; for (var i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { var row = sheet.GetRow(i); if (row == null) { continue; } var dataRow = table.NewRow(); for (var j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(excelBook);//解析带有公式的Excel var cell = e.EvaluateInCell(row.GetCell(j)); dataRow[j] = cell.ToString(); } } table.Rows.Add(dataRow); } return table; }
public override DataTransactionResponse Run() { var dataTransactionResponse = new DataTransactionResponse(); XSSFWorkbook xssfWorkbook; using (var file = new FileStream(Url, FileMode.Open, FileAccess.Read)) { xssfWorkbook = new XSSFWorkbook(file); } var sheet = xssfWorkbook.GetSheetAt(0); var rows = sheet.GetRowEnumerator(); rows.MoveNext(); while (rows.MoveNext()) { IRow row = (XSSFRow) rows.Current; var response = CheckAndInsertData(row); dataTransactionResponse.Messages.Add(response); } xssfWorkbook = null; sheet = null; return dataTransactionResponse; }
public static Dictionary<string, string> ReadConfiguration(string configFile) { //READ CONFIG FILE var dict = new Dictionary<string, string>(); XSSFWorkbook xssfwb; using (FileStream file = new FileStream(configFile, FileMode.Open, FileAccess.Read)) { xssfwb = new XSSFWorkbook(file); } ISheet sheet = xssfwb.GetSheetAt(0); //get the first sheet for (int row = 1; row <= sheet.LastRowNum; row++) { if (sheet.GetRow(row) != null) //null is when the row only contains empty cells { var name = sheet.GetRow(row).GetCell(0).ToString(); var value = sheet.GetRow(row).GetCell(1).ToString(); dict.Add(name, value); } } return dict; }
public static void ReadConfiguration(Dictionary<string, ObjectInfo> library, string libraryFile) { XSSFWorkbook xssfwb; using (FileStream file = new FileStream(libraryFile, FileMode.Open, FileAccess.Read)) { xssfwb = new XSSFWorkbook(file); } ISheet sheet = xssfwb.GetSheetAt(0); //get the first sheet for (int row = 1; row <= sheet.LastRowNum; row++) { if (sheet.GetRow(row) != null) //null is when the row only contains empty cells { var name = sheet.GetRow(row).GetCell(0).ToString(); var value = new ObjectInfo() { Name = name, Type = sheet.GetRow(row).GetCell(1).ToString(), Value = sheet.GetRow(row).GetCell(2).ToString() }; library.Add(name, value); } } }
public void TestReadAnchors() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4); XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape; XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5); XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape; int pictureIndex = wb.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG); XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6); XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape; wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List<XSSFShape> shapes = Drawing.GetShapes(); Assert.AreEqual(3, shapes.Count); Assert.AreEqual(shapes[0].GetAnchor(), anchor1); Assert.AreEqual(shapes[1].GetAnchor(), anchor2); Assert.AreEqual(shapes[2].GetAnchor(), anchor3); }
public void TestSaveLoadNew() { 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); }
public void TestIncrementSheetId() { XSSFWorkbook wb = new XSSFWorkbook(); int sheetId = (int)((XSSFSheet)wb.CreateSheet()).sheet.sheetId; Assert.AreEqual(1, sheetId); sheetId = (int)((XSSFSheet)wb.CreateSheet()).sheet.sheetId; Assert.AreEqual(2, sheetId); //test file with gaps in the sheetId sequence wb = XSSFTestDataSamples.OpenSampleWorkbook("47089.xlsm"); int lastSheetId = (int)((XSSFSheet)wb.GetSheetAt(wb.NumberOfSheets - 1)).sheet.sheetId; sheetId = (int)((XSSFSheet)wb.CreateSheet()).sheet.sheetId; Assert.AreEqual(lastSheetId + 1, sheetId); }
public void TestAddNewTextParagraphWithRTS() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String"); XSSFFont font = wb1.CreateFont() as XSSFFont; font.SetColor(new XSSFColor(Color.FromArgb(0, 255, 255))); font.FontName = ("Arial"); rt.ApplyFont(font); XSSFFont midfont = wb1.CreateFont() as XSSFFont; midfont.SetColor(new XSSFColor(Color.FromArgb(0, 255, 0))); rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font XSSFTextParagraph para = shape.AddNewTextParagraph(rt); // Save and re-load it XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook; wb1.Close(); sheet = wb2.GetSheetAt(0) as XSSFSheet; // Check Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List <XSSFShape> shapes = Drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = sshape.TextParagraphs; Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that. List <XSSFTextRun> runs = para.TextRuns; Assert.AreEqual(3, runs.Count); // first run properties Assert.AreEqual("Test ", runs[0].Text); Assert.AreEqual("Arial", runs[0].FontFamily); Color clr = runs[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 255 }, new int[] { clr.R, clr.G, clr.B })); // second run properties Assert.AreEqual("Rich Text", runs[1].Text); Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily); clr = runs[1].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 0 }, new int[] { clr.R, clr.G, clr.B })); // third run properties Assert.AreEqual(" String", runs[2].Text); Assert.AreEqual("Arial", runs[2].FontFamily); clr = runs[2].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 255 }, new int[] { clr.R, clr.G, clr.B })); checkRewrite(wb2); wb2.Close(); }
/// <summary> /// Get the parsable rows from a workbook /// </summary> /// <param name="rows">a reference to an object that will contain parsable rows</param> /// <param name="workbook">the workbook to extract rows from</param> private void GetRows(ImportData data, XSSFWorkbook workbook) { ISheet sheet = workbook.GetSheetAt(0); bool reading = true; int rowIndex = sheet.FirstRowNum; while (reading) { IRow row = sheet.GetRow(rowIndex); if (RowIsParsable(row)) { var validatorRow = new Row(); validatorRow.lineNumber = rowIndex; for (int i = 0; i < data.fieldNames.Count; ++i) { var fieldName = data.fieldNames[i]; var field = new Field() { value = CellValueAsString(row.GetCell(i)) }; validatorRow.fields.Add(fieldName, field); } data.rows.Add(validatorRow); } rowIndex++; if (rowIndex > sheet.LastRowNum) { reading = false; } } }
public void TestReadTextBoxParagraphs() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox.xlsx"); XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet; //the sheet has one relationship and it is XSSFDrawing List <RelationPart> rels = sheet.RelationParts; Assert.AreEqual(1, rels.Count); RelationPart rp = rels[0]; Assert.IsTrue(rp.DocumentPart is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart; //sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing Assert.AreSame(drawing, sheet.CreateDrawingPatriarch()); String drawingId = rp.Relationship.Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = textbox.TextParagraphs; Assert.AreEqual(3, paras.Count); Assert.AreEqual("Line 2", paras[1].Text); // check content of second paragraph Assert.AreEqual("Line 1\nLine 2\nLine 3", textbox.Text); // check content of entire textbox // check attributes of paragraphs Assert.AreEqual(TextAlign.LEFT, paras[0].TextAlign); Assert.AreEqual(TextAlign.CENTER, paras[1].TextAlign); Assert.AreEqual(TextAlign.RIGHT, paras[2].TextAlign); Color clr = paras[0].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 255, 0, 0 }, new int[] { clr.R, clr.G, clr.B })); clr = paras[1].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 0 }, new int[] { clr.R, clr.G, clr.B })); clr = paras[2].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 0, 255 }, new int[] { clr.R, clr.G, clr.B })); checkRewrite(wb); wb.Close(); }
public void TestAddBulletParagraphs() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet; XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 10, 20)); String paraString1 = "A normal paragraph"; String paraString2 = "First bullet"; String paraString3 = "Second bullet (level 1)"; String paraString4 = "Third bullet"; String paraString5 = "Another normal paragraph"; String paraString6 = "First numbered bullet"; String paraString7 = "Second bullet (level 1)"; String paraString8 = "Third bullet (level 1)"; String paraString9 = "Fourth bullet (level 1)"; String paraString10 = "Fifth Bullet"; XSSFTextParagraph para = shape.AddNewTextParagraph(paraString1); para = shape.AddNewTextParagraph(paraString2); para.SetBullet(true); para = shape.AddNewTextParagraph(paraString3); para.SetBullet(true); para.Level = (1); para = shape.AddNewTextParagraph(paraString4); para.SetBullet(true); para = shape.AddNewTextParagraph(paraString5); para = shape.AddNewTextParagraph(paraString6); para.SetBullet(ListAutoNumber.ARABIC_PERIOD); para = shape.AddNewTextParagraph(paraString7); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(paraString8); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(""); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(paraString9); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(paraString10); para.SetBullet(ListAutoNumber.ARABIC_PERIOD); // Save and re-load it XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1); sheet = wb2.GetSheetAt(0) as XSSFSheet; // Check drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = sshape.TextParagraphs; Assert.AreEqual(12, paras.Count); // this should be 12 as XSSFSimpleShape Creates a default paragraph (no text), and then we Added to that StringBuilder builder = new StringBuilder(); builder.Append(paraString1); builder.Append("\n"); builder.Append("\u2022 "); builder.Append(paraString2); builder.Append("\n"); builder.Append("\t\u2022 "); builder.Append(paraString3); builder.Append("\n"); builder.Append("\u2022 "); builder.Append(paraString4); builder.Append("\n"); builder.Append(paraString5); builder.Append("\n"); builder.Append("1. "); builder.Append(paraString6); builder.Append("\n"); builder.Append("\t3. "); builder.Append(paraString7); builder.Append("\n"); builder.Append("\t4. "); builder.Append(paraString8); builder.Append("\n"); builder.Append("\t"); // should be empty builder.Append("\n"); builder.Append("\t5. "); builder.Append(paraString9); builder.Append("\n"); builder.Append("2. "); builder.Append(paraString10); Assert.AreEqual(builder.ToString(), sshape.Text); checkRewrite(wb2); wb2.Close(); }
public void TestNew() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); //multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); Assert.AreSame(dr1, dr2); List<POIXMLDocumentPart> rels = sheet.GetRelations(); Assert.AreEqual(1, rels.Count); Assert.IsTrue(rels[0] is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rels[0]; String drawingId = drawing.GetPackageRelationship().Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); //XSSFClientAnchor anchor = new XSSFClientAnchor(); XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2)); c1.LineWidth = 2.5; c1.LineStyle = SS.UserModel.LineStyle.DashDotSys; XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5)); XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); c3.SetText(new XSSFRichTextString("Test String")); c3.SetFillColor(128, 128, 128); XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); rt.ApplyFont(0, 5, wb.CreateFont()); rt.ApplyFont(5, 6, wb.CreateFont()); c4.SetText(rt); c4.IsNoFill = (true); Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray()); List<XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[(0)] is XSSFConnector); Assert.IsTrue(shapes[(1)] is XSSFShapeGroup); Assert.IsTrue(shapes[(2)] is XSSFSimpleShape); Assert.IsTrue(shapes[(3)] is XSSFSimpleShape); // Save and re-load it wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; // Check dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing; CT_Drawing ctDrawing = dr1.GetCTDrawing(); // Connector, shapes and text boxes are all two cell anchors Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray()); Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray()); Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray()); shapes = dr1.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[0] is XSSFConnector); Assert.IsTrue(shapes[1] is XSSFShapeGroup); Assert.IsTrue(shapes[2] is XSSFSimpleShape); Assert.IsTrue(shapes[3] is XSSFSimpleShape); // // Ensure it got the right namespaces //String xml = ctDrawing.ToString(); //Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\"")); //Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\"")); }
public void TestLoadSave() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx"); ICreationHelper CreateHelper = workbook.GetCreationHelper(); Assert.AreEqual(3, workbook.NumberOfSheets); XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); // Check hyperlinks Assert.AreEqual(4, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); // Write out, and check // Load up again, check all links 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)); sheet = (XSSFSheet)wb2.GetSheetAt(0); // Check hyperlinks again Assert.AreEqual(4, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); // Add one more, and re-check IRow r17 = sheet.CreateRow(17); ICell r17c = r17.CreateCell(2); IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url); hyperlink.Address = ("http://poi.apache.org/spreadsheet/"); hyperlink.Label = "POI SS Link"; r17c.Hyperlink = (hyperlink); Assert.AreEqual(5, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(17).GetCell(2).Hyperlink.Type); Assert.AreEqual("POI SS Link", sheet.GetRow(17).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/spreadsheet/", sheet.GetRow(17).GetCell(2).Hyperlink.Address); // Save and re-load once more XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2); Assert.AreEqual(3, wb3.NumberOfSheets); Assert.IsNotNull(wb3.GetSheetAt(0)); Assert.IsNotNull(wb3.GetSheetAt(1)); Assert.IsNotNull(wb3.GetSheetAt(2)); sheet = (XSSFSheet)wb3.GetSheetAt(0); Assert.AreEqual(5, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(17).GetCell(2).Hyperlink.Type); Assert.AreEqual("POI SS Link", sheet.GetRow(17).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/spreadsheet/", sheet.GetRow(17).GetCell(2).Hyperlink.Address); }
public void TestGroupingTest() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("GroupTest.xlsx"); Assert.AreEqual(31, wb.GetSheetAt(0).LastRowNum); // NOTE: This is currently based on current behavior of POI, somehow // what POI returns in the calls to collapsed/hidden is not fully matching // the examples in the spec or I did not fully understand how POI stores the data internally... CheckWorkbookGrouping(wb, new bool?[] { // 0-4 false, false, false, false, false, null, null, // 7-11 false, false, true, true, true, null, null, // 14-18 false, false, true, false, false, null, // 20-24 false, false, true, true, false, null, null, // 27-31 false, false, false, true, false }, new bool[] { // 0-4 false, false, false, false, false, false, false, // 7-11 true, true, true, true, false, false, false, // 14-18 true, true, false, false, false, false, // 20-24 true, true, true, false, false, false, false, // 27-31 true, true, true, true, false }, // outlineLevel new int[] { // 0-4 3, 3, 2, 1, 0, 0, 0, // 7-11 3, 3, 2, 1, 0, 0, 0, // 14-18 3, 3, 2, 1, 0, 0, // 20-24 3, 3, 2, 1, 0, 0, 0, // 27-31 3, 3, 2, 1, 0, } ); /* * Row: 0: Level: 3 Collapsed: false Hidden: false * Row: 1: Level: 3 Collapsed: false Hidden: false * Row: 2: Level: 2 Collapsed: false Hidden: false * Row: 3: Level: 1 Collapsed: false Hidden: false * Row: 4: Level: 0 Collapsed: false Hidden: false * Row: 7: Level: 3 Collapsed: false Hidden: true * Row: 8: Level: 3 Collapsed: false Hidden: true * Row: 9: Level: 2 Collapsed: true Hidden: true * Row: 10: Level: 1 Collapsed: true Hidden: true * Row: 11: Level: 0 Collapsed: true Hidden: false * Row: 14: Level: 3 Collapsed: false Hidden: true * Row: 15: Level: 3 Collapsed: false Hidden: true * Row: 16: Level: 2 Collapsed: true Hidden: false * Row: 17: Level: 1 Collapsed: false Hidden: false * Row: 18: Level: 0 Collapsed: false Hidden: false * Row: 20: Level: 3 Collapsed: false Hidden: true * Row: 21: Level: 3 Collapsed: false Hidden: true * Row: 22: Level: 2 Collapsed: true Hidden: true * Row: 23: Level: 1 Collapsed: true Hidden: false * Row: 24: Level: 0 Collapsed: false Hidden: false * Row: 27: Level: 3 Collapsed: false Hidden: true * Row: 28: Level: 3 Collapsed: false Hidden: true * Row: 29: Level: 2 Collapsed: false Hidden: true * Row: 30: Level: 1 Collapsed: true Hidden: true * Row: 31: Level: 0 Collapsed: true Hidden: false */ }
public void TestAddValidations() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx"); ISheet sheet = workbook.GetSheetAt(0); List <XSSFDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations(); /** * For each validation type, there are two cells with the same validation. This Tests * application of a single validation defInition to multiple cells. * * For list ( 3 validations for explicit and 3 for formula ) * - one validation that allows blank. * - one that does not allow blank. * - one that does not show the drop down arrow. * = 2 * * For number validations ( integer/decimal and text length ) with 8 different types of operators. * = 50 * * = 52 ( Total ) */ Assert.AreEqual(52, dataValidations.Count); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH }; int[] SingleOperandOperatorTypes = new int[] { OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL, OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL, OperatorType.EQUAL, OperatorType.NOT_EQUAL }; int[] doubleOperandOperatorTypes = new int[] { OperatorType.BETWEEN, OperatorType.NOT_BETWEEN }; decimal value = (decimal)10, value2 = (decimal)20; double dvalue = (double)10.001, dvalue2 = (double)19.999; int lastRow = sheet.LastRowNum; int offset = lastRow + 3; int lastKnownNumValidations = dataValidations.Count; IRow row = sheet.CreateRow(offset++); ICell cell = row.CreateCell(0); IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" }); CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex); IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList); SetOtherValidationParameters(dataValidation); sheet.AddValidationData(dataValidation); lastKnownNumValidations++; row = sheet.CreateRow(offset++); cell = row.CreateCell(0); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex); ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT"); ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN"); ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL"); int rowNum = row.RowNum + 1; String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString(); IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula); dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList); SetOtherValidationParameters(dataValidation); sheet.AddValidationData(dataValidation); lastKnownNumValidations++; offset++; offset++; for (int i = 0; i < validationTypes.Length; i++) { int validationType = validationTypes[i]; offset = offset + 2; IRow row0 = sheet.CreateRow(offset++); ICell cell_10 = row0.CreateCell(0); cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length"); offset++; for (int j = 0; j < SingleOperandOperatorTypes.Length; j++) { int operatorType = SingleOperandOperatorTypes[j]; IRow row1 = sheet.CreateRow(offset++); //For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells. IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null; cell_10 = row1.CreateCell(0); cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString()); ICell cell_11 = row1.CreateCell(1); ICell cell_21 = row1.CreateCell(2); ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null; ICell cell_13 = row1.CreateCell(3); cell_13.SetCellType(CellType.NUMERIC); cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value); //First create value based validation; IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex)); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); //Now create real formula based validation. String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString(); constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null); if (i == 0 && j == 0) { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } else if (i == 0 && j == 1) { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } else { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } } for (int j = 0; j < doubleOperandOperatorTypes.Length; j++) { int operatorType = doubleOperandOperatorTypes[j]; IRow row1 = sheet.CreateRow(offset++); cell_10 = row1.CreateCell(0); cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString()); ICell cell_11 = row1.CreateCell(1); ICell cell_21 = row1.CreateCell(2); ICell cell_13 = row1.CreateCell(3); ICell cell_14 = row1.CreateCell(4); String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString(); cell_13.SetCellType(CellType.NUMERIC); cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value); String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString(); cell_14.SetCellType(CellType.NUMERIC); cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2); //First create value based validation; IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex)); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); //Now create real formula based validation. String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString(); String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString(); constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } } workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); ISheet sheetAt = workbook.GetSheetAt(0); Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count); }
protected bool OpenWB(string sExcelFileName) { Sheets.Clear(); if (Path.GetExtension(sExcelFileName).Equals(".xlsx")) { StatusChange("Importing from a newer Excel file format"); XSSFWorkbook wb; using (FileStream file = new FileStream(sExcelFileName, FileMode.Open, FileAccess.Read)) { wb = new XSSFWorkbook(file); } for (int i = 0; i < wb.Count; i++) { ISheet sheet = wb.GetSheetAt(i); Sheets.Add(sheet.SheetName, sheet); } } else if (Path.GetExtension(sExcelFileName).Equals(".xls")) { StatusChange("This is an an older Excel file format"); HSSFWorkbook wb; using (FileStream file = new FileStream(sExcelFileName, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(file); } for (int i = 0; i < wb.Count; i++) { ISheet sheet = wb.GetSheetAt(i); Sheets.Add(sheet.SheetName, sheet); } } else { StatusChange("I am not sure what file extention this is.. exiting import"); throw new Exception("I am not sure what file extention this is"); } return true; }
public void TestReferencesToOtherWorkbooks() { XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx"); XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet; // References to a .xlsx file IRow rXSLX = s.GetRow(2); ICell cXSLX_cell = rXSLX.GetCell(4); ICell cXSLX_sNR = rXSLX.GetCell(6); ICell cXSLX_gNR = rXSLX.GetCell(8); Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula); Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula); Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula); Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue); Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue); Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue); // References to a .xls file IRow rXSL = s.GetRow(4); ICell cXSL_cell = rXSL.GetCell(4); ICell cXSL_sNR = rXSL.GetCell(6); ICell cXSL_gNR = rXSL.GetCell(8); Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula); Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula); Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula); Assert.AreEqual("Hello!", cXSL_cell.StringCellValue); Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue); Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue); // Try to Evaluate without references, won't work // (At least, not unit we fix bug #56752 that is1) try { evaluator.Evaluate(cXSL_cell); Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " + "reference to a non-provided linked workbook"); } catch (Exception e) { } // Setup the environment Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>(); evaluators.Add("ref2-56737.xlsx", evaluator); evaluators.Add("56737.xlsx", _testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator()); evaluators.Add("56737.xls", HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator()); evaluator.SetupReferencedWorkbooks(evaluators); // Try Evaluating all of them, ensure we don't blow up foreach (IRow r in s) { foreach (ICell c in r) { // TODO Fix and enable evaluator.Evaluate(c); } } Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString()); Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString()); //Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString()); Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString()); Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString()); Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString()); //Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString()); Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString()); // Add another formula referencing these workbooks ICell cXSL_cell2 = rXSL.CreateCell(40); cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1"); // TODO Shouldn't it become [2] like the others? Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula); Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString()); // Now add a formula that refers to yet another (different) workbook // Won't work without the workbook being linked ICell cXSLX_nw_cell = rXSLX.CreateCell(42); try { cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1"); Assert.Fail("New workbook not linked, shouldn't be able to Add"); } catch (Exception e) {} // Link and re-try IWorkbook alt = new XSSFWorkbook(); alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook"); // TODO Implement the rest of this, see bug #57184 /* * wb.LinkExternalWorkbook("alt.xlsx", alt); * * cXSLX_nw_cell.SetCellFormula"[alt.xlsx]Sheet1!$A$1"); * // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ? * Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.CellFormula); * * // Evaluate it, without a link to that workbook * try { * Evaluator.Evaluate(cXSLX_nw_cell); * Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate"); * } catch(Exception e) {} * * // Add a link, check it does * Evaluators.Put("alt.xlsx", alt.GetCreationHelper().CreateFormulaEvaluator()); * Evaluator.SetupReferencedWorkbooks(evaluators); * * Evaluator.Evaluate(cXSLX_nw_cell); * Assert.AreEqual("In another workbook", cXSLX_nw_cell.StringCellValue); */ }
public void TestNew() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); //multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); Assert.AreSame(dr1, dr2); List <POIXMLDocumentPart> rels = sheet.GetRelations(); Assert.AreEqual(1, rels.Count); Assert.IsTrue(rels[0] is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rels[0]; String drawingId = drawing.GetPackageRelationship().Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); //XSSFClientAnchor anchor = new XSSFClientAnchor(); XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2)); c1.LineWidth = 2.5; c1.LineStyle = SS.UserModel.LineStyle.DashDotSys; XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5)); Assert.IsNotNull(c2); XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); c3.SetText(new XSSFRichTextString("Test String")); c3.SetFillColor(128, 128, 128); XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); rt.ApplyFont(0, 5, wb.CreateFont()); rt.ApplyFont(5, 6, wb.CreateFont()); c4.SetText(rt); c4.IsNoFill = (true); Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray()); List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[(0)] is XSSFConnector); Assert.IsTrue(shapes[(1)] is XSSFShapeGroup); Assert.IsTrue(shapes[(2)] is XSSFSimpleShape); Assert.IsTrue(shapes[(3)] is XSSFSimpleShape); // Save and re-load it wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; // Check dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing; CT_Drawing ctDrawing = dr1.GetCTDrawing(); // Connector, shapes and text boxes are all two cell anchors Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray()); Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray()); Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray()); shapes = dr1.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[0] is XSSFConnector); Assert.IsTrue(shapes[1] is XSSFShapeGroup); Assert.IsTrue(shapes[2] is XSSFSimpleShape); Assert.IsTrue(shapes[3] is XSSFSimpleShape); // // Ensure it got the right namespaces //String xml = ctDrawing.ToString(); //Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\"")); //Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\"")); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb)); }
public void TestSaveLoadNew() { 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); }
public IEvaluationSheet GetSheet(int sheetIndex) { return(new XSSFEvaluationSheet(_uBook.GetSheetAt(sheetIndex))); }
/// <summary> /// 利用NPOI导入Excel文件数据 /// </summary> /// <param name="XlsFilesPathName">获取选定文件的路径名</param> /// <returns></returns> public static DataTable NPOIImportExcelFile(string XlsFilesPathName) { try { DataTable ExcelTable = new DataTable();//创建填充数据表 if (XlsFilesPathName == string.Empty) { MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } else if (Path.GetExtension(XlsFilesPathName) != ".xlsx" && Path.GetExtension(XlsFilesPathName) != ".xls") { MessageBoxEx.Show("请您选择Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } else { IWorkbook WkBk = null;//创建Excel工作簿 ISheet sht = null;//获取第一个sheet表 IRow HeaderRow = null;//获取Excel标题行 FileStream fsxls = new FileStream(XlsFilesPathName, FileMode.Open, FileAccess.Read);//读取Excel文件流 if (Path.GetExtension(XlsFilesPathName) == ".xlsx") { WkBk = new XSSFWorkbook(fsxls);//生成支持Office2007以上版本的Excel表格工作簿 sht = (XSSFSheet)WkBk.GetSheetAt(0);//获取第一个sheet表数据 HeaderRow = (XSSFRow)sht.GetRow(0);//获取Excel标题行 } else if (Path.GetExtension(XlsFilesPathName) == ".xls") { WkBk = new HSSFWorkbook(fsxls);//生成支持Office2003及以下版本的Excel表格工作簿 sht = (HSSFSheet)WkBk.GetSheetAt(0);//获取第一个sheet表数据 HeaderRow = (HSSFRow)sht.GetRow(0);//获取Excel标题行 } fsxls.Close(); fsxls.Dispose(); int FirstCellIndx = HeaderRow.FirstCellNum;//获取Excel标题行首列索引 int LastCellIndx = HeaderRow.LastCellNum;//获取Excel标题行尾列索引 //创建表列 for (int i = FirstCellIndx; i < LastCellIndx; i++) { DataColumn DataCol = new DataColumn(); DataCol.ColumnName = HeaderRow.GetCell(i).StringCellValue;//标题列名称 ExcelTable.Columns.Add(DataCol);//添加表列名 } int FirstRowIndx = sht.FirstRowNum;//获取Excel首行索引 int LastRowIndx = sht.LastRowNum;//获取Excel首行索引 //创建表行 for (int j = FirstRowIndx + 1; j < LastRowIndx; j++) { IRow DatasRow = sht.GetRow(j); DataRow TableRow = ExcelTable.NewRow();//创建表行 for (int k = FirstCellIndx; k < LastCellIndx; k++) { //获取Excel表行数据值 string XlsCellValue = DatasRow.GetCell(k).ToString() == string.Empty ? "" : DatasRow.GetCell(k).ToString(); TableRow[k] = XlsCellValue; } ExcelTable.Rows.Add(TableRow);//添加表行 } WkBk = null; sht = null; } return ExcelTable;//返回数据表 } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); return null; } }
public void TestCommentShowsBox() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet(); XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0); XSSFCell cell = (XSSFCell)sheet.CreateRow(0).CreateCell(0); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFCreationHelper factory = (XSSFCreationHelper)wb.GetCreationHelper(); XSSFClientAnchor anchor = (XSSFClientAnchor)factory.CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + 3; anchor.Row1 = cell.RowIndex; anchor.Row2 = cell.RowIndex + 5; XSSFComment comment = (XSSFComment)drawing.CreateCellComment(anchor); XSSFRichTextString str = (XSSFRichTextString)factory.CreateRichTextString("this is a comment"); comment.String = str; cell.CellComment = comment; XSSFVMLDrawing vml = sheet.GetVMLDrawing(false); CT_Shapetype shapetype = null; ArrayList items = vml.GetItems(); foreach (object o in items) { if (o is CT_Shapetype) { shapetype = (CT_Shapetype)o; } } Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked); Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled); using (MemoryStream ws = new MemoryStream()) { wb.Write(ws); using (MemoryStream rs = new MemoryStream(ws.GetBuffer())) { wb = new XSSFWorkbook(rs); sheet = (XSSFSheet)wb.GetSheetAt(0); vml = sheet.GetVMLDrawing(false); shapetype = null; items = vml.GetItems(); foreach (object o in items) { if (o is CT_Shapetype) { shapetype = (CT_Shapetype)o; } } //wb.Write(new FileStream("comments.xlsx", FileMode.Create)); //using (MemoryStream ws2 = new MemoryStream()) //{ // vml.Write(ws2); // throw new System.Exception(System.Text.Encoding.UTF8.GetString(ws2.GetBuffer())); //} Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked); Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled); } } }
public void TestCreate() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper; String[] urls = { "http://apache.org/", "www.apache.org", "/temp", "file:///c:/temp", "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*" }; for (int i = 0; i < urls.Length; i++) { String s = urls[i]; XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink; link.Address = (s); XSSFCell cell = row.CreateCell(i) as XSSFCell; cell.Hyperlink = (link); } workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) { Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) { Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } }
private LoadPatternList() { using (FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); int sheetnumber = workbook.NumberOfSheets; int [][][] sheetelements = new int[sheetnumber][][]; for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){ ISheet _isheet = workbook.GetSheetAt(sheetindex); int lastrownum = _isheet.LastRowNum; int [][] rowelements = new int[lastrownum+1][]; for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){ IRow row = _isheet.GetRow(rowindex); if(row == null)continue; int lastcellnum = row.LastCellNum; int[] cellelements = new int[lastcellnum+1]; for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){ ICell cell = row.GetCell(cellindex); if(cell != null){ cellelements[cellindex] = Convert.ToInt32(cell.ToString()); } } rowelements[rowindex] = cellelements; } sheetelements[sheetindex] = rowelements; } data = sheetelements; } if(data.Length > 0){ checksign = "No"; } }