public void Test3DArea()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
wb.CreateSheet();
wb.SetSheetName(1, "Sheet2");
IRow row = sheet1.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("isblank(Sheet2!A1:A1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(CellType.Boolean, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
cell.CellFormula = ("isblank(D7:D7)");
result = fe.Evaluate(cell);
Assert.AreEqual(CellType.Boolean, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
}
public void Test3DArea()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
wb.CreateSheet();
wb.SetSheetName(1, "Sheet2");
Row row = sheet1.CreateRow(0);
Cell cell = row.CreateCell((short)0);
cell.CellFormula = ("isblank(Sheet2!A1:A1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue result = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
cell.CellFormula = ("isblank(D7:D7)");
result = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
}
/// <summary>
/// 增加一個新的表格，並將新增的表格作為活動表
/// </summary>
/// <param name="sheetName">表名稱</param>
public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
_currentSheet = _workbook.CloneSheet(i);
sheetIndex = _workbook.GetSheetIndex(_currentSheet);
_workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
_defaultExcelCellStyle = new ExcelCellStyle();
}
/// <summary>
/// DataTable 数据至二进制
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="header"></param>
/// <param name="commnet"></param>
/// <returns>二进制数组</returns>
public byte[] Export(DataTable dt)
{
_workBook = new HSSFWorkbook();
ISheet sheet = _workBook.CreateSheet();
if (header == "钢联数据")
{
_workBook.SetSheetName(0, "Sheet1");
}
else
{
_workBook.SetSheetName(0, "数据");
}
//设置列宽
setColumnWidth(dt, sheet);
int rowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
//新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = _workBook.CreateSheet();
}
if (!string.IsNullOrEmpty(header))
{
IRow titleRow = sheet.CreateRow(0);
createTitleComment(sheet, titleRow);
rowIndex = 1;
}
}
//设置单元格数据
setCellsValue(sheet, dt, dr, rowIndex);
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
_workBook.Write(ms);
ms.Flush();
ms.Position = 0;
_workBook.Close();
_workBook = null;
return(ms.ToArray());
}
}
public void TestToFormulaString()
{
Ref3DPtg target = new Ref3DPtg("A1", (short)0);
HSSFWorkbook wb = CreateWorkbookWithSheet("my sheet");
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual("'my sheet'!A1", target.ToFormulaString(book));
wb.SetSheetName(0, "ProfitAndLoss");
Assert.AreEqual("ProfitAndLoss!A1", target.ToFormulaString(book));
wb.SetSheetName(0, "profit+loss");
Assert.AreEqual("'profit+loss'!A1", target.ToFormulaString(book));
}
/// <summary>
/// 导出订单汇总
/// </summary>
public static void Excel_DingdanHZ(String yearMonth, DataTable dt, string openFilePath, String saveFilePath)
{
try
{
FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
hssfworkbook.SetSheetName(0, yearMonth);
ISheet sheet1 = hssfworkbook.GetSheet(yearMonth);
InsertRows2(sheet1, 1, dt.Rows.Count);
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.GetRow(1 + i);
row.GetCell(0).SetCellValue(dt.Rows[i]["AgentId"].ToString());
row.GetCell(1).SetCellValue(dt.Rows[i]["AgentName"].ToString());
row.GetCell(2).SetCellValue(dt.Rows[i]["CareerStatus"].ToString());
row.GetCell(3).SetCellValue(dt.Rows[i]["Rank"].ToString());
var state = dt.Rows[i]["State"].ToString();
row.GetCell(4).SetCellValue(state == "0" ? "已删除" : (state == "1" ? "正常" : (state == "-1" ? "新添加" : "")));
row.GetCell(5).SetCellValue(dt.Rows[i]["CountOrders"].ToString());
row.GetCell(6).SetCellValue(dt.Rows[i]["Price"].ToString());
}
sheet1.ForceFormulaRecalculation = true;
file = new FileStream(saveFilePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 导出产品汇总
/// </summary>
public static void Excel_ChanpinHZ(String yearMonth, List <OrdersDetail> list, string openFilePath, String saveFilePath)
{
try
{
FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
hssfworkbook.SetSheetName(0, yearMonth);
ISheet sheet1 = hssfworkbook.GetSheet(yearMonth);
InsertRows2(sheet1, 1, list.Count);
//写入数据
for (int i = 0; i < list.Count; i++)
{
IRow row = sheet1.GetRow(1 + i);
row.GetCell(0).SetCellValue(list[i].ProductName);
row.GetCell(1).SetCellValue((double)list[i].UnitPrice);
row.GetCell(2).SetCellValue(list[i].Num);
row.GetCell(3).SetCellValue((double)list[i].Price);
}
sheet1.ForceFormulaRecalculation = true;
file = new FileStream(saveFilePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
catch (Exception ex)
{
throw;
}
}
public void TestBug50298()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
ISheet sheet = wb.CloneSheet(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)");
wb.SetSheetName(wb.GetSheetIndex(sheet), "copy");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy");
wb.SetSheetOrder("copy", 0);
assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received");
wb.RemoveSheetAt(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
// check that the overall workbook serializes with its correct size
int expected = wb.Workbook.Size;
int written = wb.Workbook.Serialize(0, new byte[expected * 2]);
Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected);
HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb);
assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
}
public void TestEvaluateBooleanInCell_bug44508()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("1=1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
try
{
fe.EvaluateInCell(cell);
}
catch (FormatException)
{
Assert.Fail("Identified bug 44508");
}
Assert.AreEqual(true, cell.BooleanCellValue);
wb.Close();
}
public void TestToFormulaString()
{
Area3DPtg target = new Area3DPtg("A1:B1", (short)0);
string sheetName = "my sheet";
HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName);
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book));
wb.SetSheetName(0, "Sheet1");
Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book));
wb.SetSheetName(0, "C64");
Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book));
}
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
//HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
NPOI.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(NPOI.SS.UserModel.CellType.Numeric, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
public static void SaveSOExcel(List <So> soList, List <List <SoItemsContentAndState> > soitemsListList)
{
if (soList.Count() != soitemsListList.Count())
{
MessageBox.Show("Internal Error. Please send the log file to the Author");
Logger.Error(soList.Count() + "," + soitemsListList.Count());
return;
}
FileStream file;
try
{
file = new FileStream(@"SoTemplate.dll", FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
MessageBox.Show("Please check the SoTemplate.dll.");
return;
}
hssfworkbook = new HSSFWorkbook(file);
WriteDsiInfo();
for (int i = 0; i < soList.Count(); i++)
{
ISheet sheet = hssfworkbook.CloneSheet(0);
FillTheSoSheet(sheet, soList[i], soitemsListList[i]);
hssfworkbook.SetSheetName(3 + i, "SO" + i.ToString());
}
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
WriteToFile();
}
public void TestParseStringLiterals_bug28754()
{
StringPtg sp;
try
{
sp = (StringPtg)ParseSingleToken("\"test\"\"ing\"", typeof(StringPtg));
}
catch (Exception e)
{
if (e.Message.StartsWith("Cannot Parse"))
{
throw new AssertFailedException("Identified bug 28754a");
}
throw e;
}
Assert.AreEqual("test\"ing", sp.Value);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell((short)0);
cell.CellFormula = ("right(\"test\"\"ing\", 3)");
String actualCellFormula = cell.CellFormula;
if ("RIGHT(\"test\"ing\",3)".Equals(actualCellFormula))
{
throw new AssertFailedException("Identified bug 28754b");
}
Assert.AreEqual("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
}
public void TestDuplicateNames()
{
HSSFWorkbook b = new HSSFWorkbook();
b.CreateSheet("Sheet1");
b.CreateSheet();
b.CreateSheet("name1");
try
{
b.CreateSheet("name1");
Assert.Fail();
}
catch (ArgumentException)// pass
{
}
b.CreateSheet();
try
{
b.SetSheetName(3, "name1");
Assert.Fail();
}
catch (ArgumentException)// pass
{
}
try
{
b.SetSheetName(3, "name1");
Assert.Fail();
}
catch (ArgumentException)// pass
{
}
b.SetSheetName(3, "name2");
b.SetSheetName(3, "name2");
b.SetSheetName(3, "name2");
HSSFWorkbook c = new HSSFWorkbook();
c.CreateSheet("Sheet1");
c.CreateSheet("Sheet2");
c.CreateSheet("Sheet3");
c.CreateSheet("Sheet4");
}
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);
}
public void ChangeSheetName(int k, string sheetname)
{
k = k - 1;
if (wb == null)
{
return;
}
wb.SetSheetName(k, sheetname);
//wb.CreateSheet(sheetname);//克隆原有sheet页作为基础
//wb.SetSheetOrder(sheetname, k);//将最新的sheet页挪到第一页
}
public void TestSetFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
if ("SUM(A-32767:A-32766)".Equals(cell.CellFormula))
{
Assert.Fail("Identified bug 44539");
}
Assert.AreEqual("SUM(A32769:A32770)", cell.CellFormula);
}
/// <summary>
/// 直接导出Datatable数据到Excel
/// </summary>
/// <param name="exportTable">导出的Table信息</param>
/// <param name="title">Sheet名称</param>
/// <returns></returns>
public static HSSFWorkbook ExportFromTable(DataTable exportTable, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
workbook.SetSheetName(0, title);
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = Company;
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = Author;
si.ApplicationName = ApplicationName;
si.Title = title;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < exportTable.Columns.Count; i++)
{
ICell headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(exportTable.Columns[i].ColumnName?.ToString());
}
for (int i = 0; i < exportTable.Rows.Count; i++)
{
IRow dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < exportTable.Columns.Count; j++)
{
ICell dataCell = dataRow.CreateCell(j);
dataCell.SetCellValue(exportTable.Rows[i][j]?.ToString());
}
}
for (int i = 0; i < exportTable.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
return(workbook);
}
public void TestBug57231()
{
HSSFWorkbook wb = HSSFTestDataSamples
.OpenSampleWorkbook("57231_MixedGasReport.xls");
HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet;
HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet;
int idx = wb.GetSheetIndex(newSheet);
wb.SetSheetName(idx, "newName");
// Write the output to a file
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbBack);
Assert.IsNotNull(wbBack.GetSheet("master"));
Assert.IsNotNull(wbBack.GetSheet("newName"));
}
public static void Excel_daochu(DataTable dt, string filepath, bool isCg)//常规
{
try
{
string wj = "";
if (isCg)
{
wj = "常规";
}
FileStream file = new FileStream(filepath + "导出模板" + wj + ".xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
hssfworkbook.SetSheetName(0, "数据");
ISheet sheet1 = hssfworkbook.GetSheet("数据");
InsertRows(sheet1, 1, dt.Rows.Count, hssfworkbook, dt.Columns.Count);
//列标题
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet1.GetRow(0).GetCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//写入数据
for (int j = 1; j <= dt.Rows.Count; j++)
{
//dt.Columns[0].ColumnName;
//IRow row1 = sheet1.GetRow(j);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet1.GetRow(j).GetCell(i).SetCellValue(dt.Rows[j - 1][i].ToString());
}
}
sheet1.ForceFormulaRecalculation = true;
file = new FileStream(filepath + dao_name(), FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 导出收入明细
/// </summary>
public static void Excel_IncomeMX(String yearMonth, List <Income> list, string openFilePath, String saveFilePath)
{
try
{
FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
hssfworkbook.SetSheetName(0, yearMonth);
ISheet sheet1 = hssfworkbook.GetSheet(yearMonth);
InsertRows2(sheet1, 1, list.Count);
//写入数据
for (int i = 0; i < list.Count; i++)
{
IRow row = sheet1.GetRow(1 + i);
row.GetCell(0).SetCellValue(list[i].YearMonth);
row.GetCell(1).SetCellValue(list[i].AgentId);
row.GetCell(2).SetCellValue(list[i].AgentName);
row.GetCell(3).SetCellValue(list[i].CareerStatus);
row.GetCell(4).SetCellValue(list[i].Rank);
row.GetCell(5).SetCellValue((double)list[i].SalesServiceMoney);
row.GetCell(6).SetCellValue((double)list[i].PersonalServiceMoney);
row.GetCell(7).SetCellValue((double)list[i].MarketServiceMoney);
row.GetCell(8).SetCellValue((double)list[i].RegionServiceMoney);
row.GetCell(9).SetCellValue((double)list[i].RegionServiceYum);
row.GetCell(10).SetCellValue((double)list[i].IncomeMoney);
}
sheet1.ForceFormulaRecalculation = true;
file = new FileStream(saveFilePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
catch (Exception ex)
{
throw;
}
}
public void SalvarDataTable(DataTable dt, string path)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//Criar uma tabela em branco
ISheet sheet = hssfworkbook.CreateSheet();
//Colocar dados nela
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
hssfworkbook.CreateSheet();
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheet), sheet.SheetName);
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 打开xls文件，我修改成了，如果没有这个文件，就创建这个文件
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">工作表名</param>
/// <param name="clear">清空标识？</param>
/// <returns></returns>
public string open_xls(string fileName = "buyma-need-sell.xls", string sheetName = "data", bool clear = true)
{
try
{
//文件名 全路径
strFilename = System.IO.Directory.GetCurrentDirectory() + "\\" + fileName;
if (System.IO.File.Exists(strFilename) == false)
{
//这里有修改项，如果没有这个xls文件，我就进行创建操作
//System.IO.File.Create(strFilename);
xlBook = new HSSFWorkbook();
xlBook.CreateSheet(sheetName);
}
else
{
if (isFileCanWrite(strFilename) == false)
{
return("当該ファイル（" + fileName + ")は既に他のアプリより開いている状態であるため、まず関連アプリを閉じてください");
}
System.IO.FileStream fs = new System.IO.FileStream(strFilename, System.IO.FileMode.Open);
xlBook = new HSSFWorkbook(fs);
xlBook.SetSheetName(0, sheetName);
sheetClear(sheetName);
}
//在工作簿中以修改第一个工作表为指定工作表名
xlSheet = xlBook.GetSheet(sheetName);
working_row = 1;
_hasOpen = true;
return("");
}
catch (Exception ex)
{
return("sheet [data] is not exit." + ex.ToString());
throw;
}
}
public void TestInvoke()
{
HSSFWorkbook wb;
NPOI.SS.UserModel.Sheet sheet ;
Cell cell;
if (false)
{
// TODO - this code won't work until we can create user-defined functions directly with POI
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
NPOI.SS.UserModel.Name hssfName = wb.CreateName();
hssfName.NameName = ("myFunc");
}
else
{
// This sample spreadsheet already has a VB function called 'myFunc'
wb = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
sheet = wb.GetSheetAt(0);
Row row = sheet.CreateRow(0);
cell = row.CreateCell(1);
}
cell.CellFormula = ("myFunc()");
String actualFormula = cell.CellFormula;
Assert.AreEqual("myFunc()", actualFormula);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
NPOI.SS.UserModel.CellValue evalResult = fe.Evaluate(cell);
// Check the return value from ExternalFunction.evaluate()
// TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better
Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, evalResult.CellType);
Assert.AreEqual(ErrorEval.FUNCTION_NOT_IMPLEMENTED.ErrorCode, evalResult.ErrorValue);
}
public void SetSheetName(int index, string sheetName)
{
workbook.SetSheetName(index, sheetName);
}
private void tsbExcel_Click(object sender, EventArgs e)
{
HSSFWorkbook workbook = null;
using (MemoryStream ms = new MemoryStream(Resource.template))
{
workbook = new HSSFWorkbook(ms); // 工作簿
}
Dictionary <string, HSSFCellStyle> styleDic = new Dictionary <string, HSSFCellStyle>();
Dictionary <string, IFont> fontDic = new Dictionary <string, IFont>();
//准备sheet
var sheetIndex = 0;
foreach (I3ReportData rd in reportDatas.Datas)
{
if (sheetIndex > 0)
{
workbook.CloneSheet(0);
}
sheetIndex++;
}
sheetIndex = 0;
foreach (I3ReportData rd in reportDatas.Datas)
{
string sheetName = string.IsNullOrEmpty(rd.Name) ? ("sheet" + (sheetIndex + 1).ToString()) : rd.Name;
//ISheet sheet = workbook.CreateSheet(sheetName);
ISheet sheet = workbook.GetSheetAt(sheetIndex);
workbook.SetSheetName(sheetIndex, sheetName);
//设置列宽
var colIndex = -1;
foreach (I3ReportCol col in rd.Cols)
{
colIndex++;
if (col.Width == 0)
{
sheet.SetColumnHidden(colIndex, true);
}
else
{
//int width = (int)Math.Ceiling(col.Width * 34.612159); //向上取整，与excel中保持一致 //34.612159是从ebiao导入excel处获取的
//在列旁边按住鼠标不动，可以显示默认单位与像素的对应关系，10单位对应85像素，即1像素对应10/85单位 (好像与excel默认字体有关)
//SetColumnWidth的单位是1/256，因此1像素对应10*256/85=30.117647
double dw = (double)col.Width * (double)10 * (double)256 / (double)85;
int width = (int)Math.Ceiling(dw);
sheet.SetColumnWidth(colIndex, width);
}
}
//导出数据
int rowCount = 0;
foreach (I3PrintArea area in rd.PrintAreas.Dic.Values)
{
exportAreaToSheet(area, workbook, sheet, ref rowCount, styleDic, fontDic);
}
//页面设置 //列宽会有微调导致在excel中左右分页
sheet.SetMargin(MarginType.RightMargin, rd.PageSetting.PaperRightMarginMM / (float)25.4);
sheet.SetMargin(MarginType.TopMargin, rd.PageSetting.PaperTopMarginMM / (float)25.4);
sheet.SetMargin(MarginType.LeftMargin, rd.PageSetting.PaperLeftMarginMM / (float)25.4);
sheet.SetMargin(MarginType.BottomMargin, rd.PageSetting.PaperBottomMarginMM / (float)25.4);
sheet.PrintSetup.Landscape = rd.PageSetting.PaperOrientation == PaperOrientation.横向;
int paperType = rd.PageSetting.GetNPOIPaperType();
if (paperType == 0) //自定义，不清楚NPOI中怎样设置，用A4
{
sheet.PrintSetup.PaperSize = 9;
}
else if (paperType == -1) //未打到对应关系
{
sheet.PrintSetup.PaperSize = 9;
}
else
{
sheet.PrintSetup.PaperSize = (short)paperType;
}
sheet.IsPrintGridlines = true;
sheet.FitToPage = false;//默认值是true
//设置锁定
sheet.ProtectSheet("{D49C4D85-F46E-456F-9C71-DB7D880B5B04}");
sheet.IsPrintGridlines = false; //不设置这个，打印时会打印出空白单元格的网络线（即使没有网格线也会打印）
sheetIndex++;
}
string tmpFileName = getTmpFileName();
using (FileStream file = new FileStream(tmpFileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
I3PCUtil.CreateAndWaitProcessByEvent(null, tmpFileName, "", IntPtr.Zero, 0, 0);
}
protected void btnImport_Click(object sender, EventArgs e)
{
string strPrintId = this.strPrintId.Value;
TMisMonitorSubtaskVo objSubTask = new TMisMonitorSubtaskVo();
objSubTask.TASK_ID = strPrintId;
DataTable dtSub = new TMisMonitorSubtaskLogic().SelectByTable(objSubTask);
string strSampleIDs = "";
for (int i = 0; i < dtSub.Rows.Count; i++)
{
GetPoint_UnderTask(dtSub.Rows[i]["ID"].ToString(), ref strSampleIDs);
}
//获取基本信息
DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleIDs, "021", "0");
int iPageCount = dt.Rows.Count / 17;
if (dt.Rows.Count % 17 != 0)
{
iPageCount += 1;
}
FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/QHDSamplingCode.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//sheet复制
for (int k = 1; k < iPageCount; k++)
{
hssfworkbook.CloneSheet(0);
hssfworkbook.SetSheetName(k, "Sheet" + (k + 1).ToString());
}
for (int m = 1; m <= iPageCount; m++)
{
ISheet sheet = hssfworkbook.GetSheet("Sheet" + m.ToString());
sheet.GetRow(23).GetCell(0).SetCellValue("样品交接数量： " + dt.Rows.Count.ToString());
sheet.GetRow(2).GetCell(0).SetCellValue(string.Format(" 采 样 日 期： {0} 年 {1} 月 {2} 日", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString()));
sheet.GetRow(3).GetCell(0).SetCellValue(string.Format(" 样品交接日期： {0} 年 {1} 月 {2} 日 {3} 点 {4} 分", DateTime.Now.Year.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Hour.ToString(), DateTime.Now.Minute.ToString()));
sheet.GetRow(1).GetCell(6).SetCellValue(string.Format("第 {0} 页 共 {1} 页", m.ToString(), iPageCount.ToString()));
DataTable dtNew = new DataTable();
dtNew = dt.Copy();
dtNew.Clear();
for (int n = (m - 1) * 17; n < m * 17; n++)
{
if (n >= dt.Rows.Count)
{
break;
}
dtNew.ImportRow(dt.Rows[n]);
}
for (int i = 0; i < dtNew.Rows.Count; i++)
{
string strItmeNum = "";
TMisMonitorResultVo objResult = new TMisMonitorResultVo();
objResult.SAMPLE_ID = dtNew.Rows[i]["ID"].ToString();
DataTable dtResult = new TMisMonitorResultLogic().SelectByTable(objResult);
for (int j = 0; j < dtResult.Rows.Count; j++)
{
TBaseItemInfoVo objItem = new TBaseItemInfoVo();
objItem.ID = dtResult.Rows[j]["ITEM_ID"].ToString();
objItem.IS_SAMPLEDEPT = "否";
DataTable dtItem = new TBaseItemInfoLogic().SelectByTable(objItem);
if (dtItem.Rows.Count > 0 && dtItem.Rows[0]["ITEM_NUM"].ToString().Length > 0)
{
strItmeNum += (strItmeNum.Length > 0) ? "," + dtItem.Rows[0]["ITEM_NUM"].ToString() : dtItem.Rows[0]["ITEM_NUM"].ToString();
}
}
sheet.GetRow(i + 6).GetCell(0).SetCellValue((i + 1).ToString());
sheet.GetRow(i + 6).GetCell(1).SetCellValue(dtNew.Rows[i]["SAMPLE_CODE"].ToString());
sheet.GetRow(i + 6).GetCell(6).SetCellValue(strItmeNum);
}
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("水质样品交接记录表.xls", Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
//刷新表注释
private void RefreshNote(string fileFullName)
{
bool change = false;
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
change = true; break;
}
}
if (!change)
{
return;
}
IWorkbook workbook = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite));
ISheet sheet = workbook.GetSheetAt(0);
string sheetName = sheet.SheetName;
ISheet sheetBack = workbook.CreateSheet("__sheetBackup");
sheetBack.CreateFreezePane(2, START_ROW);
int columnNum = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.LastCellNum > columnNum)
{
columnNum = row.LastCellNum;
}
IRow rowBack = sheetBack.CreateRow(i);
for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j)
{
string val = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK));
var cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
Util.SetCellString(cell, val);
}
}
for (int i = 0; i < columnNum; ++i)
{
sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i));
}
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
CellRangeAddressList cellRange = new CellRangeAddressList(START_ROW, 65535, i, i);
IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(GetEnumList(filed.Type));
IDataValidation dataValidation = helper.CreateValidation(constraint, cellRange);
dataValidation.SuppressDropDownArrow = false;
dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type));
sheetBack.AddValidationData(dataValidation);
}
}
workbook.RemoveSheetAt(0);
workbook.SetSheetOrder(sheetBack.SheetName, 0);
workbook.SetSheetName(0, sheetName);
using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) {
workbook.Write(stream);
}
}
private void Recover(string fpSrc, string fpNew)
{
var si = new MemoryStream(File.ReadAllBytes(fpSrc));
var wb = new HSSFWorkbook(si);
var wb2 = new HSSFWorkbook();
var df = wb.CreateDataFormat();
var df2 = wb2.CreateDataFormat();
SortedDictionary <int, ICellStyle> csMap = new SortedDictionary <int, ICellStyle>();
SortedDictionary <int, IFont> csF = new SortedDictionary <int, IFont>();
for (int z = 0; z < wb.NumberOfSheets; z++)
{
var sh = wb.GetSheetAt(z);
var sh2 = new HSSFSheet(wb2);
wb2.Add(sh2);
wb2.SetSheetName(z, sh.SheetName);
var y0 = sh.FirstRowNum;
var y1 = sh.LastRowNum;
SortedDictionary <int, int> useX = new SortedDictionary <int, int>();
for (int t = 0; t < sh.NumMergedRegions; t++)
{
sh2.AddMergedRegion(sh.GetMergedRegion(t));
}
for (int y = y0; y <= y1; y++)
{
var row = sh.GetRow(y);
if (row == null)
{
continue;
}
var row2 = sh2.CreateRow(y);
row2.Height = row.Height;
var x0 = row.FirstCellNum;
var x1 = row.LastCellNum;
if (x0 < 0)
{
continue;
}
for (int x = x0; x <= x1; x++)
{
useX[x] = 0;
var cell = row.GetCell(x);
if (cell == null)
{
continue;
}
var cell2 = row2.CreateCell(x, cell.CellType);
switch (cell.CellType)
{
case CellType.Boolean:
cell2.SetCellValue(cell.BooleanCellValue);
break;
case CellType.Numeric:
cell2.SetCellValue(cell.NumericCellValue);
break;
case CellType.String:
cell2.SetCellValue(cell.StringCellValue);
break;
case CellType.Formula:
cell2.SetCellFormula(cell.CellFormula);
//d["" + cell.CellFormula] = "";
break;
}
{
ICellStyle ns = null;
{
int iSrc = cell.CellStyle.Index;
if (!csMap.TryGetValue(iSrc, out ns))
{
ns = wb2.CreateCellStyle();
csMap[iSrc] = ns;
}
}
IFont font2 = null;
{
int iSrc = cell.CellStyle.FontIndex;
if (!csF.TryGetValue(iSrc, out font2))
{
font2 = wb2.CreateFont();
var font = cell.CellStyle.GetFont(wb);
font2.IsBold = font.IsBold;
font2.Charset = font.Charset;
font2.Color = font.Color;
font2.FontHeight = font.FontHeight;
font2.FontName = font.FontName;
font2.IsBold = font.IsBold;
font2.IsItalic = font.IsItalic;
font2.IsStrikeout = font.IsStrikeout;
font2.TypeOffset = font.TypeOffset;
font2.Underline = font.Underline;
csF[iSrc] = font2;
}
}
ns.Alignment = cell.CellStyle.Alignment;
ns.BorderBottom = cell.CellStyle.BorderBottom;
ns.BorderDiagonal = cell.CellStyle.BorderDiagonal;
ns.BorderDiagonalColor = cell.CellStyle.BorderDiagonalColor;
ns.BorderDiagonalLineStyle = cell.CellStyle.BorderDiagonalLineStyle;
ns.BorderLeft = cell.CellStyle.BorderLeft;
ns.BorderRight = cell.CellStyle.BorderRight;
ns.BorderTop = cell.CellStyle.BorderTop;
ns.BottomBorderColor = cell.CellStyle.BottomBorderColor;
ns.DataFormat = df2.GetFormat(cell.CellStyle.GetDataFormatString()
?? GetBuiltinFormat(cell.CellStyle.DataFormat)
);
ns.FillBackgroundColor = cell.CellStyle.FillBackgroundColor;
ns.FillForegroundColor = cell.CellStyle.FillForegroundColor;
ns.FillPattern = cell.CellStyle.FillPattern;
ns.SetFont(font2);
ns.Indention = cell.CellStyle.Indention;
ns.IsHidden = cell.CellStyle.IsHidden;
ns.IsLocked = cell.CellStyle.IsLocked;
ns.LeftBorderColor = cell.CellStyle.LeftBorderColor;
ns.RightBorderColor = cell.CellStyle.RightBorderColor;
ns.Rotation = cell.CellStyle.Rotation;
ns.ShrinkToFit = cell.CellStyle.ShrinkToFit;
ns.TopBorderColor = cell.CellStyle.TopBorderColor;
ns.VerticalAlignment = cell.CellStyle.VerticalAlignment;
ns.WrapText = cell.CellStyle.WrapText;
cell2.CellStyle = ns;
}
}
}
foreach (var x in useX.Keys)
{
sh2.SetColumnWidth(x, sh.GetColumnWidth(x));
}
}
using (var os = File.Create(fpNew)) {
wb2.Write(os);
}
}
/// <summary>
/// 根据收货出账单号生成Execl
/// </summary>
/// <param name="bill"></param>
/// <param name="list"></param>
public static void ExeclCreate(ReceivingBill bill, List <BillModel> list)
{
Log.Info("LMS开始生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new
{
ShippingMethodName = g.Key,
TotalFee = g.Sum(p => p.Freight + p.FuelCharge +
p.Register + p.Surcharge +
p.TariffPrepayFee +
p.SpecialFee + p.RemoteAreaFee)
}).ToList();
using (
var file = new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open,
FileAccess.Read))
{
IWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheetNew = hssfworkbook.CloneSheet(0);
sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称：{0}".FormatWith(bill.CustomerName));
sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间：{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd")));
IRow sourceRow = sheetNew.GetRow(2);
int startRow = 2;
foreach (var t in groupShipping)
{
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow row = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref row);
row.GetCell(0).SetCellValue(t.ShippingMethodName);
row.GetCell(1).SetCellValue("总费用：{0}".FormatWith(t.TotalFee.ToString("F2")));
}
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow srow = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref srow);
srow.GetCell(0).SetCellValue("结算人：{0}".FormatWith(bill.ReceivingBillAuditor));
startRow = startRow + 3;
int startR = startRow;
foreach (var b in list)
{
IRow row = sheetNew.CreateRow(startRow);
row.CreateCell(0).SetCellValue(b.WayBillNumber);
row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? "");
row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(4).SetCellValue(b.TrackingNumber ?? "");
row.CreateCell(5).SetCellValue(b.ChineseName);
row.CreateCell(6).SetCellValue(b.InShippingMethodName);
row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4"));
row.CreateCell(8).SetCellValue(b.Weight.ToString("F4"));
row.CreateCell(9).SetCellValue(b.CountNumber);
row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2")));
row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2")));
row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2")));
row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2")));
row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2")));
row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2")));
row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2")));
startRow++;
row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")");
}
IRow totalRow = sheetNew.CreateRow(startRow);
for (int i = 0; i < 18; i++)
{
totalRow.CreateCell(i).SetCellValue("");
}
startR++;
totalRow.Cells[9].SetCellValue("各项计费小计：");
totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")");
totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")");
totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")");
totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")");
totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")");
totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")");
totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")");
totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")");
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls")))
{
File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls"));
}
var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.Write(savefile);
file.Close();
}
Log.Info("LMS完成生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
}
