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