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"); }
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)); }
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 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 TestPrintAreaFile() { HSSFWorkbook workbook = new HSSFWorkbook(); workbook.CreateSheet("Test Print Area"); String sheetName = workbook.GetSheetName(0); String reference = "$A$1:$B$1"; workbook.SetPrintArea(0, reference); workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); String retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet"); Assert.AreEqual("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea, "References Match"); }
public void TestMultiplePrintAreaFile() { HSSFWorkbook workbook = new HSSFWorkbook(); workbook.CreateSheet("Sheet1"); workbook.CreateSheet("Sheet2"); workbook.CreateSheet("Sheet3"); String reference1 = "$A$1:$B$1"; String reference2 = "$B$2:$D$5"; String reference3 = "$D$2:$F$5"; workbook.SetPrintArea(0, reference1); workbook.SetPrintArea(1, reference2); workbook.SetPrintArea(2, reference3); //Check Created print areas String retrievedPrintArea; retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 1)"); Assert.AreEqual("Sheet1!" + reference1, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(1); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 2)"); Assert.AreEqual("Sheet2!" + reference2, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(2); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 3)"); Assert.AreEqual("Sheet3!" + reference3, retrievedPrintArea); // Check print areas after re-reading workbook workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook); retrievedPrintArea = workbook.GetPrintArea(0); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 1)"); Assert.AreEqual("Sheet1!" + reference1, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(1); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 2)"); Assert.AreEqual("Sheet2!" + reference2, retrievedPrintArea); retrievedPrintArea = workbook.GetPrintArea(2); Assert.IsNotNull(retrievedPrintArea, "Print Area Not Found (Sheet 3)"); Assert.AreEqual("Sheet3!" + reference3, retrievedPrintArea); }
public string XlsToJson(string xls) { string Result = ""; try { //文件不存在就直接退出 if ((string.IsNullOrEmpty(xls)) || (!File.Exists(xls))) { return("");//没有文件 } JObject staff = new JObject(); FileStream file = new FileStream(xls, FileMode.Open, FileAccess.Read); HSSFWorkbook mywk = new HSSFWorkbook(file); xlsfile = xls; string fileType = Path.GetExtension(xls).ToLower(); string fileName = Path.GetFileName(xls).ToLower(); staff.Add("type", fileType.Remove(0, 1)); staff.Add("fileName", fileName); file.Close(); JArray sheets = new JArray(); for (int k = 0; k < mywk.NumberOfSheets; k++) { JObject onesheet = new JObject(); ISheet sheet = mywk.GetSheetAt(k); string printarea = mywk.GetPrintArea(k); JObject printsetup = new JObject(); printsetup.Add("PrintArea", printarea); printsetup.Add("Scale", sheet.PrintSetup.Scale); printsetup.Add("PaperSize", sheet.PrintSetup.PaperSize); string RawText = ((NPOI.HSSF.UserModel.HSSFHeader)sheet.Header).RawText; //string left = ((NPOI.HSSF.UserModel.HSSFHeader)sheet.Header).Left; //页眉页脚 //Console.WriteLine(sheet.Header.ToString()); //Console.WriteLine(sheet.Footer.ToString()); //string hleft = sheet.Header.Left; //if (string.IsNullOrEmpty(sheet.Header.Left)) Console.WriteLine("hello"); //Console.WriteLine("left=" + sheet.Header.Left); //try //{ // string hl = sheet.Header.Left; //} //catch(Exception e) //{ // Console.WriteLine(e); //throw e; //} //printsetup.Add("HC", sheet.Header.Center); //printsetup.Add("HR", sheet.Header.Right); String[] v = SplitParts(RawText); printsetup.Add("HL", v[0]); printsetup.Add("HC", v[1]); printsetup.Add("HR", v[2]); RawText = ((NPOI.HSSF.UserModel.HSSFFooter)sheet.Footer).RawText; v = SplitParts(RawText); printsetup.Add("FL", v[0]); printsetup.Add("FC", v[1]); printsetup.Add("FR", v[2]); //获取页边距 double tmargin = sheet.GetMargin(MarginType.TopMargin); //上边距 double lmargin = sheet.GetMargin(MarginType.LeftMargin); //左边距 double rmargin = sheet.GetMargin(MarginType.RightMargin); //右边距 double bmargin = sheet.GetMargin(MarginType.BottomMargin); //下边距 double hmargin = sheet.GetMargin(MarginType.HeaderMargin); //页眉距 double fmargin = sheet.GetMargin(MarginType.FooterMargin); //页脚边距 printsetup.Add("tmargin", tmargin); printsetup.Add("lmargin", lmargin); printsetup.Add("rmargin", rmargin); printsetup.Add("bmargin", bmargin); printsetup.Add("hmargin", hmargin); printsetup.Add("fmargin", fmargin); //if (string.IsNullOrEmpty(printarea)) printsetup.Add("PrintArea", printarea); //if (sheet.PrintSetup.Scale != 100) printsetup.Add("Scale", sheet.PrintSetup.Scale); //if (sheet.PrintSetup.PaperSize != 9) printsetup.Add("PaperSize", sheet.PrintSetup.PaperSize); onesheet.Add("printsetup", printsetup.ToString()); string sheetName = mywk.GetSheetName(k); //读取当前表数据 onesheet.Add("sheetName", sheetName); JObject data = new JObject(); JObject rowheightobj = new JObject(); JObject colwidthobj = new JObject(); for (int i = 0; i <= sheet.LastRowNum; i++) { JObject rowobj = new JObject(); IRow row = sheet.GetRow(i); if (row != null) { short rowheight = (short)(row.Height / 20); rowheightobj.Add("L" + i.ToString(), rowheight); for (int j = 0; j <= row.LastCellNum; j++) { ICell cell = row.GetCell(j); if (i == 0) { float ColumnWidth = (float)((float)(sheet.GetColumnWidth(j)) / 256 - 0.63); colwidthobj.Add("C" + j.ToString(), ColumnWidth); } if (cell != null) { string style = GetCellStyle(cell, mywk); JObject cellobj = new JObject(); cellobj.Add("Text", cell.ToString()); if (style != "") { cellobj.Add("style", style); } rowobj.Add("C" + j.ToString(), cellobj); Dimension dimension; bool result = IsMergeCell(sheet, i, j, out dimension); if (result) { cellobj.Add("rowSpan", dimension.RowSpan.ToString()); cellobj.Add("columnSpan", dimension.ColumnSpan.ToString()); if ((i == dimension.FirstRowIndex) && (j == dimension.FirstColumnIndex)) { cellobj.Add("_mergeCount", dimension.ColumnSpan - 1); cellobj.Add("_rowmergeCount", dimension.RowSpan - 1); } } } } } data.Add("L" + i.ToString(), rowobj); } onesheet.Add("data", data); // 添加data onesheet.Add("RowHeight", rowheightobj); onesheet.Add("ColumnWidth", colwidthobj); //JArray pictures = new JArray(); string pictures = Getbase64PictureTest1(sheet); onesheet.Add("pictures", pictures); sheets.Add(onesheet); } staff.Add("sheets", sheets); Result = staff.ToString(); } finally { } return(Result); }