public void TestCloneSheetMultipleTimes() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone"); Row row = sheet.CreateRow(0); Cell cell = row.CreateCell(0); cell.SetCellValue(new HSSFRichTextString("Clone_Test")); //Clone the sheet multiple times workbook.CloneSheet(0); workbook.CloneSheet(0); Assert.IsNotNull(workbook.GetSheet("Test Clone")); Assert.IsNotNull(workbook.GetSheet("Test Clone (2)")); Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2)); Assert.IsNotNull(workbook.GetSheet("Test Clone (3)")); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.RemoveSheetAt(0); workbook.CreateSheet("abc ( 123)"); workbook.CloneSheet(0); Assert.AreEqual("abc (124)", workbook.GetSheetName(1)); }
public void TestNamedRange() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls"); //Creating new Named Range IName newNamedRange = wb.CreateName(); //Getting Sheet Name for the reference String sheetName = wb.GetSheetName(0); //Setting its name newNamedRange.NameName = "RangeTest"; //Setting its reference newNamedRange.RefersToFormula = sheetName + "!$D$4:$E$8"; //Getting NAmed Range IName namedRange1 = wb.GetNameAt(0); //Getting it sheet name sheetName = namedRange1.SheetName; // sanity check SanityChecker c = new SanityChecker(); c.CheckHSSFWorkbook(wb); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); IName nm = wb.GetNameAt(wb.GetNameIndex("RangeTest")); Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.AreEqual(wb.GetSheetName(0) + "!$D$4:$E$8", nm.RefersToFormula); }
public void TestMultipleNamedWrite() { HSSFWorkbook wb = new HSSFWorkbook(); wb.CreateSheet("TestSheet1"); String sheetName = wb.GetSheetName(0); Assert.AreEqual("TestSheet1", sheetName); //Creating new Named Range NPOI.SS.UserModel.Name newNamedRange = wb.CreateName(); newNamedRange.NameName = ("RangeTest"); newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8"); //Creating another new Named Range NPOI.SS.UserModel.Name newNamedRange2 = wb.CreateName(); newNamedRange2.NameName = ("AnotherTest"); newNamedRange2.RefersToFormula = (sheetName + "!$F$1:$G$6"); NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(0); String referece = namedRange1.RefersToFormula; wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name nm = wb.GetNameAt(wb.GetNameIndex("RangeTest")); Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.IsTrue((wb.GetSheetName(0) + "!$D$4:$E$8").Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula); nm = wb.GetNameAt(wb.GetNameIndex("AnotherTest")); Assert.IsTrue("AnotherTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.IsTrue(newNamedRange2.RefersToFormula.Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula); }
public void TestDBCSName() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("DBCSSheetName.xls"); wb.GetSheetAt(1); Assert.AreEqual(wb.GetSheetName(1), "\u090f\u0915", "DBCS Sheet Name 2"); Assert.AreEqual(wb.GetSheetName(0), "\u091c\u093e", "DBCS Sheet Name 1"); }
public static DataTable FormatToDatatable(string filePath, string sheetName) { HSSFWorkbook hssfworkbook = null; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } int sheetCount = hssfworkbook.Workbook.NumSheets; int sheetIndex = -1; for (int i = sheetCount; i < sheetCount; i++) { if (hssfworkbook.GetSheetName(i) == sheetName.Trim()) { sheetIndex = i; } } if (sheetIndex > 0) { return(FormatToDatatable(hssfworkbook, sheetIndex)); } else { return(new DataTable()); } }
/// <summary> /// 获取所有的Sheet的名称 /// </summary> /// <param name="FileName"></param> /// <returns></returns> public static string[] GetExcelSheetNames(string FileName) { string[] Result = null; if (File.Exists(FileName)) { try { using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); int Count = hssfworkbook.Count; if (Count > 0) { Result = new string[Count]; for (int Index = 0; Index < Count; Index++) { Result[Index] = hssfworkbook.GetSheetName(Index); } } } } catch (Exception ex) { Debug.WriteLine(ex.Message); } } return(Result); }
/// <summary> /// .xls后缀的Excel文件添加图片 /// </summary> /// <param name="excelPath"></param> /// <param name="imgPath"></param> public static void InsertImageToXLSExcel(string excelPath, string imgPath) { byte[] bytes = System.IO.File.ReadAllBytes(imgPath); try { using (FileStream fs = new FileStream(excelPath, FileMode.Open)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet(hssfworkbook.GetSheetName(0)); int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 5, 2, 10, 3);//(255, 125, 1023, 150, 5, 2, 10, 3);//(0, 0, 0, 0, 5, 2, 10, 3); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); FileStream file = new FileStream(excelPath, FileMode.Create); hssfworkbook.Write(file); hssfworkbook.Close(); } } catch (Exception ex) { throw new Exception(ex.Message); } }
public void TestFunctionsFromTestSpreadsheet() { HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook(SS.FILENAME); ConfirmReadMeSheet(workbook); int nSheets = workbook.NumberOfSheets; for (int i = 1; i < nSheets; i++) { int sheetResult = ProcessTestSheet(workbook, i, workbook.GetSheetName(i)); switch (sheetResult) { case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount++; break; case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount++; break; } } // Confirm results String successMsg = "There were " + _sheetSuccessCount + " successful sheets(s) and " + _evaluationSuccessCount + " function(s) without error"; if (_sheetFailureCount > 0) { String msg = _sheetFailureCount + " sheets(s) failed with " + _evaluationFailureCount + " evaluation(s). " + successMsg; throw new AssertFailedException(msg); } //if(false) { // normally no output for successful tests // System.out.println(GetType().Name + ": " + successMsg); //} }
void DisplayBook(HSSFWorkbook book) { ToolStripButton firstBtn = null; int cx = book.NumberOfSheets; for (int x = 0; x < cx; x++) { int si = x; ToolStripButton tsb = new ToolStripButton(book.GetSheetName(x)); tsb.Click += delegate(object sender, EventArgs e) { for (int t = 1; t < tss.Items.Count; t++) { ((ToolStripButton)tss.Items[t]).Checked = (tss.Items[t] == tsb); } ActivateSheet((NPOI.SS.UserModel.Sheet)book.GetSheetAt(si)); }; tss.Items.Add(tsb); firstBtn = firstBtn ?? tsb; } if (firstBtn != null) { firstBtn.PerformClick(); } CloseWIP(); }
/// <summary> /// Excel转换成DataTable(.xlsx/.xls). /// </summary> /// <param name="filePath">Excel文件路径.</param> /// <returns>.</returns> public static DataTable ExcelToDataTable(string filePath) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); string fileType = Path.GetExtension(filePath).ToLower(); string fileName = Path.GetFileName(filePath).ToLower(); try { ISheet sheet = null; int sheetNumber = 0; FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); if (fileType == ".xlsx") { // 2007版本 XSSFWorkbook workbook = new XSSFWorkbook(fs); sheetNumber = workbook.NumberOfSheets; for (int i = 0; i < sheetNumber; i++) { string sheetName = workbook.GetSheetName(i); sheet = workbook.GetSheet(sheetName); if (sheet != null) { dt = GetSheetDataTable(sheet); if (dt != null) { dt.TableName = sheetName.Trim(); ds.Tables.Add(dt); } } } } else if (fileType == ".xls") { // 2003版本 HSSFWorkbook workbook = new HSSFWorkbook(fs); sheetNumber = workbook.NumberOfSheets; for (int i = 0; i < sheetNumber; i++) { string sheetName = workbook.GetSheetName(i); sheet = workbook.GetSheet(sheetName); if (sheet != null) { dt = GetSheetDataTable(sheet); if (dt != null) { dt.TableName = sheetName.Trim(); ds.Tables.Add(dt); } } } } return(ds.Tables[0]); } catch (Exception ex) { return(null); } }
List <Verse> xlsxToVerse(String xlsxFile, int sheetNumber) { if (xlsxFile == null || sheetNumber < 1) { throw new ArgumentException(); } try { HSSFWorkbook hssfwb; using (FileStream file = new FileStream(@xlsxFile, FileMode.Open, FileAccess.Read)) { hssfwb = new HSSFWorkbook(file); } ISheet sheet = hssfwb.GetSheet(hssfwb.GetSheetName(sheetNumber - 1)); // at least two rows if (sheet.LastRowNum > 0) { return(getVerses((HSSFSheet)sheet)); } else { throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is empty"); } } catch (ArgumentException e) { throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is not found"); } }
private bool OutTestRecFile(string strFilePathName) { bool bRet = true; string srcFileName = Application.StartupPath + "\\试验记录模板.xls"; try { if (!File.Exists(srcFileName)) //模板是否存在 { MessageBox.Show("试验记录模板不存在,请检查!", "警告"); return(false); } string[,] RecInfoCell = new string[TestRecDgView.RowCount, TestRecDgView.ColumnCount]; for (int i = 0; i < TestRecDgView.RowCount; i++) { for (int j = 0; j < TestRecDgView.ColumnCount; j++) { RecInfoCell[i, j] = TestRecDgView.Rows[i].Cells[j].Value.ToString(); } } HSSFWorkbook workbook = null; using (FileStream stream = new FileStream(srcFileName, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(stream); stream.Close(); } int InitRow = 4, InitCol = 0; HSSFSheet hSheet0 = (HSSFSheet)workbook.GetSheet(workbook.GetSheetName(0)); for (int i = 0; i < TestRecDgView.RowCount; i++) { IRow row = (IRow)hSheet0.GetRow(i + InitRow); if (row != null) { for (int j = 0; j < TestRecDgView.ColumnCount; j++) { row.CreateCell(j + InitCol, CellType.STRING).SetCellValue(RecInfoCell[i, j]); } } } FileStream file = new FileStream(strFilePathName, FileMode.Create); workbook.Write(file); file.Close(); } catch (Exception e) { MessageBox.Show("Excel文件保存出错:" + e.Message); bRet = false; } return(bRet); }
/// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="ExcelFileStream">Excel文件流</param> /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) { IWorkbook workbook = new HSSFWorkbook(ExcelFileStream); ExcelFileStream.Close(); string SheetName = workbook.GetSheetName(SheetIndex); return(RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex)); }
public void TestPrintAreaFileRead() { HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithPrintArea.xls"); String sheetName = workbook.GetSheetName(0); String reference = sheetName + "!$A$1:$C$5"; Assert.AreEqual(reference, workbook.GetPrintArea(0)); }
/// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="strFileName">Excel文件全路径(服务器路径)</param> /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex) { using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = new HSSFWorkbook(file); string SheetName = workbook.GetSheetName(SheetIndex); return(RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex)); } }
public void TestMultiNamedRange() { // Create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // Create a worksheet 'sheet1' in the new workbook wb.CreateSheet(); wb.SetSheetName(0, "sheet1"); // Create another worksheet 'sheet2' in the new workbook wb.CreateSheet(); wb.SetSheetName(1, "sheet2"); // Create a new named range for worksheet 'sheet1' NPOI.SS.UserModel.Name namedRange1 = wb.CreateName(); // Set the name for the named range for worksheet 'sheet1' namedRange1.NameName = ("RangeTest1"); // Set the reference for the named range for worksheet 'sheet1' namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41"); // Create a new named range for worksheet 'sheet2' NPOI.SS.UserModel.Name namedRange2 = wb.CreateName(); // Set the name for the named range for worksheet 'sheet2' namedRange2.NameName = ("RangeTest2"); // Set the reference for the named range for worksheet 'sheet2' namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21"); // Write the workbook to a file // Read the Excel file and verify its content wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1")); Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName); Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula); NPOI.SS.UserModel.Name nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2")); Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName); Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula); }
/** * Asserts that the 'read me' comment page exists, and has this class' name in one of the * cells. This back-link is to make it easy to find this class if a reader encounters the * spreadsheet first. */ private void confirmReadMeSheet(HSSFWorkbook workbook) { String firstSheetName = workbook.GetSheetName(0); if (!firstSheetName.Equals(SS.README_SHEET_NAME, StringComparison.InvariantCultureIgnoreCase)) { throw new Exception("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'"); } //ISheet sheet = workbook.GetSheetAt(0); //String specifiedClassName = sheet.GetRow(2).GetCell(0).RichStringCellValue.String; //Assert.AreEqual(this.GetType().FullName, specifiedClassName, "Test class name in spreadsheet comment"); }
/** * Asserts that the 'read me' comment page exists, and has this class' name in one of the * cells. This back-link is to make it easy to find this class if a Reader encounters the * spreadsheet first. */ private void ConfirmReadMeSheet(HSSFWorkbook workbook) { String firstSheetName = workbook.GetSheetName(0); if (!firstSheetName.Equals(SS.README_SHEET_NAME, StringComparison.OrdinalIgnoreCase)) { throw new RuntimeException("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'"); } ISheet sheet = workbook.GetSheetAt(0); String specifiedClassName = sheet.GetRow(2).GetCell(0).RichStringCellValue.String; Assert.AreEqual("org.apache.poi.ss.formula.functions.TestLookupFunctionsFromSpreadsheet", specifiedClassName, "Test class name in spreadsheet comment"); }
public static int?BuscarIndiceHoja(HSSFWorkbook libro, string nombreHoja) { var sheets = new List <Sheet>(); for (int i = 0; i < libro.NumberOfSheets; i++) { if (libro.GetSheetName(i).ToUpper() == nombreHoja.ToUpper()) { return(i); } } return(null); }
/// <summary> /// 获取excel的sheet列表 /// </summary> /// <param name="filePath">Excel文件</param> /// <returns></returns> public static List <ISheet> getExcelSheets(string filePath) { if (!File.Exists(filePath)) { throw new Exception("Excel文件不存在!"); } string fileExt = Path.GetExtension(filePath).ToLower(); if ((!fileExt.Equals(".xlsx")) && (!fileExt.Equals(".xls"))) { throw new Exception("Excel文件后缀名不正确!"); } List <ISheet> sheets = new List <ISheet>(); int sheetNumber = 0; using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (fileExt == ".xlsx") { // 2007版本 XSSFWorkbook workbook = new XSSFWorkbook(fs); sheetNumber = workbook.NumberOfSheets; for (int i = 0; i < sheetNumber; i++) { ISheet sheet = workbook.GetSheet(workbook.GetSheetName(i)); if (sheet != null) { sheets.Add(sheet); } } } else if (fileExt == ".xls") { // 2003版本 HSSFWorkbook workbook = new HSSFWorkbook(fs); sheetNumber = workbook.NumberOfSheets; for (int i = 0; i < sheetNumber; i++) { ISheet sheet = workbook.GetSheet(workbook.GetSheetName(i)); if (sheet != null) { sheets.Add(sheet); } } } } return(sheets); }
public void TestNamedReadModify() { HSSFWorkbook wb = OpenSample("namedinput.xls"); NPOI.SS.UserModel.Name name = wb.GetNameAt(0); String sheetName = wb.GetSheetName(0); Assert.AreEqual(sheetName + "!$A$1:$D$10", name.RefersToFormula); name = wb.GetNameAt(1); String newReference = sheetName + "!$A$1:$C$36"; name.RefersToFormula = (newReference); Assert.AreEqual(newReference, name.RefersToFormula); }
public void TestNamedReadModify() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("namedinput.xls"); IName name = wb.GetNameAt(0); String sheetName = wb.GetSheetName(0); Assert.AreEqual(sheetName + "!$A$1:$D$10", name.RefersToFormula); name = wb.GetNameAt(1); String newReference = sheetName + "!$A$1:$C$36"; name.RefersToFormula = newReference; Assert.AreEqual(newReference, name.RefersToFormula); }
public static Dictionary <string, int> GetAllSheets(string excelFilePath) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { HSSFWorkbook workbook = new HSSFWorkbook(stream); Dictionary <string, int> dic = new Dictionary <string, int>(); int count = workbook.NumberOfSheets; for (int i = 0; i < count; i++) { string name = workbook.GetSheetName(i); dic.Add(name, i); } return(dic); } }
/// <summary>获取excel中sheet的集合 /// /// </summary> /// <param name="filepath"></param> /// <returns></returns> internal static List <string> GetExcelSheet(string filepath) { HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } List <string> list = new List <string>(); for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { list.Add(hssfworkbook.GetSheetName(i)); } return(list); }
// 取得所有的 SheetName public List <string> GetAllSheetName(string ExcelFilename) { // Get All Sheet Name FileStream fs = new FileStream(ExcelFilename, FileMode.Open); // 取得該 Excel 的資料流 IWorkbook WorkBook = new HSSFWorkbook(fs); List <string> listSheetName = new List <string>(); for (int Index = 0; Index < WorkBook.NumberOfSheets; Index++) { listSheetName.Add(WorkBook.GetSheetName(Index)); } fs.Close(); WorkBook = null; return(listSheetName); }
public void TestPrintAreaCoords() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = sheetName + "!$A$1:$B$1"; workbook.SetPrintArea(0, 0, 1, 0, 0); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); Assert.AreEqual("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea); }
public List <String> getsheets(String filename) { using (FileStream fs = File.OpenRead(filename)) //打开myxls.xls文件 { NPOI.HSSF.UserModel.HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中 int n = wk.NumberOfSheets; List <String> sheetnames = new List <string>(); for (int i = 0; i < n; i++) { sheetnames.Add(wk.GetSheetName(i)); } return(sheetnames); } }
public void TestPrintAreaUnion() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = "$A$1:$B$1,$D$1:$F$2"; workbook.SetPrintArea(0, reference); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); Assert.AreEqual("'Test Print Area'!$A$1:$B$1,'Test Print Area'!$D$1:$F$2", retrievedPrintArea); }
public void TestPrintAreaRemove() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = sheetName + "!$A$1:$B$1"; workbook.SetPrintArea(0, 0, 1, 0, 0); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); workbook.RemovePrintArea(0); Assert.IsNull(workbook.GetPrintArea(0), "PrintArea was not Removed"); }
/// <summary> /// 获取Excel文件中,Sheet表单的名字列表 /// </summary> /// <param name="filePath">文件地址</param> /// <returns></returns> public static ArrayList GetSheetName(string filePath) { ArrayList arrayList = new ArrayList(); try { FileStream readfile = new FileStream(filePath, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { arrayList.Add(hssfworkbook.GetSheetName(i)); } } catch (Exception exception) { } return(arrayList); }