/**
* Called to test whether or not the embedded workbook was correctly
* updated. This method simply recovers the first cell from the first row
* of the first workbook and tests the value it contains.
* <p/>
* Note that execution will not continue up to the assertion as the
* embedded workbook is now corrupted and causes an IllegalArgumentException
* with the following message
* <p/>
* <em>java.lang.IllegalArgumentException: Your InputStream was neither an
* OLE2 stream, nor an OOXML stream</em>
* <p/>
* to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
* is executed.
*
* @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
* Rather
* than use the specific classes (HSSF/XSSF) to handle the embedded
* workbook this method uses those defeined in the SS stream. As
* a result, it might be the case that a SpreadsheetML file is
* opened for processing, throwing this exception if that file is
* invalid.
* @throws java.io.IOException Thrown if a problem occurs in the underlying
* file system.
*/
public void CheckUpdatedDoc()
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
PackagePart pPart = null;
IEnumerator <PackagePart> pIter = null;
List <PackagePart> embeddedDocs = this.doc.GetAllEmbedds();
if (embeddedDocs != null && embeddedDocs.Count != 0)
{
pIter = embeddedDocs.GetEnumerator();
while (pIter.MoveNext())
{
pPart = pIter.Current;
if (pPart.PartName.Extension.Equals(BINARY_EXTENSION) ||
pPart.PartName.Extension.Equals(OPENXML_EXTENSION))
{
workbook = WorkbookFactory.Create(pPart.GetInputStream());
sheet = workbook.GetSheetAt(SHEET_NUM);
row = sheet.GetRow(ROW_NUM);
cell = row.GetCell(CELL_NUM);
Assert.Equal(cell.NumericCellValue, NEW_VALUE);
}
}
}
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt)
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet("Sheet1");
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 根据Datable表头创建Excel表头
for (int i = 0; i < dt.Columns.Count; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
//cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
//cell.CellStyle.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.Yellow.Index;
cell.SetCellValue(dt.Columns[i].ColumnName);
tb.AutoSizeColumn(i);//自动调整宽度，貌似对中文支持不好
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return wk;
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public static DataTable ReadExcel(this Stream stream)
{
DataTable dt = new DataTable();
ISheet sheet;
//stream.Position = 0;
//if (sFileExtension == ".xls")
//{
// HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
// sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
//}
//else
//{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
//}
IRow headerRow = sheet.GetRow(0); //Get Header Row
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
if (cell == null || string.IsNullOrWhiteSpace(cell.ToString()))
{
continue;
}
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
try
{
dr[j] = row.GetCell(j);
}
catch
{
}
}
}
dt.Rows.Add(dr);
}
return(dt);
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt, string[] columnName, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j >= columnName.Length)
break;
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return wk;
}
public void GetOrder()
{
//加载数据
var workBook = NPOIHelper.LoadWorkbook(@"D:\Work\项目\香烟\香烟\资料文件参考\订单分拣数据（慧联系统）\订单分拣数据（慧联系统）\分拣订单数据 - 副本.xls");
var sheet = workBook.GetSheetAt(0); //获得第一个sheet
var rows = sheet.GetRowEnumerator(); //获得所有行
OrderTable = new DataTable();
rows.MoveNext(); //移动到第一行，
var rowfirst = (IRow)rows.Current; //获得当前行，
for (int i = 0; i < rowfirst.LastCellNum; i++) //遍历，把第一行数据，当做行加入表中
{
OrderTable.Columns.Add(rowfirst.GetCell(i).ToString());
}
while (rows.MoveNext())//然后循环遍历
{
var row = (IRow)rows.Current;
DataRow dr = OrderTable.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);//取得单元格
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
OrderTable.Rows.Add(dr);
}
}
/**
* This method attempt to find an already existing HSSFCellStyle that matches
* what you want the style to be. If it does not find the style, then it
* Creates a new one. If it does Create a new one, then it applies the
* propertyName and propertyValue to the style. This is necessary because
* Excel has an upper limit on the number of Styles that it supports.
*
*@param workbook The workbook that is being worked with.
*@param propertyName The name of the property that is to be
* changed.
*@param propertyValue The value of the property that is to be
* changed.
*@param cell The cell that needs it's style changes
*@exception NestableException Thrown if an error happens.
*/
public static void SetCellStyleProperty(NPOI.SS.UserModel.ICell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
{
NPOI.SS.UserModel.ICellStyle originalStyle = cell.CellStyle;
NPOI.SS.UserModel.ICellStyle newStyle = null;
Hashtable values = GetFormatProperties(originalStyle);
values[propertyName] = propertyValue;
// index seems like what index the cellstyle is in the list of styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.NumCellStyles;
for (short i = 0; i < numberCellStyles; i++)
{
NPOI.SS.UserModel.ICellStyle wbStyle = workbook.GetCellStyleAt(i);
Hashtable wbStyleMap = GetFormatProperties(wbStyle);
// if (wbStyleMap.Equals(values))
if (CompareHashTableKeyValueIsEqual(wbStyleMap, values))
{
newStyle = wbStyle;
break;
}
}
if (newStyle == null)
{
newStyle = workbook.CreateCellStyle();
SetFormatProperties(newStyle, workbook, values);
}
cell.CellStyle = (newStyle);
}
/// <summary>
/// 作用：分析一行得到自然人实体类
/// 作者：汪建龙
/// 编写时间：2017年3月6日14:52:36
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private static Lawyer AnalyzeLawyer(IRow row)
{
var cells = new NPOI.SS.UserModel.ICell[11];
for (var i = 0; i < cells.Length; i++)
{
cells[i] = row.GetCell(i);
}
var name = cells[0].ToString();
if (string.IsNullOrEmpty(name))
{
return(null);
}
var lawyer = new Lawyer
{
Name = name,
BornTime = cells[2].ToString().Trim(),
Number = cells[4].ToString().Trim(),
Address = cells[8].ToString().Trim(),
TelPhone = cells[9].ToString().Trim(),
EMail = cells[10].ToString().Trim()
};
try
{
lawyer.Sex = EnumHelper.GetEnum <Sex>(cells[1].ToString().Trim());
lawyer.Credential = EnumHelper.GetEnum <Credential>(cells[3].ToString().Trim());
}
catch
{
}
return(lawyer);
}
public static string GetCellValue(NPOI.SS.UserModel.ICell cell)
{
try
{
if (cell == null)
{
return(string.Empty);
}
string value = string.Empty;
switch (cell.CellType)
{
case CellType.Blank: { value = string.Empty; } break;
case CellType.Boolean: { value = ConvertHelper.GetString(cell.BooleanCellValue); } break;
case CellType.Error: { value = ConvertHelper.GetString(cell.ErrorCellValue); } break;
case CellType.Formula: { value = ConvertHelper.GetString(cell); } break;
case CellType.Numeric: { value = ConvertHelper.GetString(cell.NumericCellValue); } break;
case CellType.String: { value = ConvertHelper.GetString(cell.StringCellValue); } break;
case CellType.Unknown:
default: { value = ConvertHelper.GetString(cell); } break;
}
return(value);
}
catch (Exception e)
{
return($"获取cell数据异常：{e.Message}");
}
}
//private void CreateUser(string username, string password, int roleID)
//{
// MembershipUser m = Membership.CreateUser(username, password);
// ProfileCommon.Create(username).Save();
// ProfileCommon p = ProfileCommon.GetProfile(username);
// p.RoleID = roleID;
// p.IsActive = true;
// p.Save();
// new UserProfileBFC().Create(username, "System");
//}
//private void ImportCustomer()
//{
// var filename = "Customer List 2015";
// if (File.Exists(Server.MapPath("~/Import/Customer List 2015.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 2; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var customer = new CustomerModel();
// customer.Code = new CustomerBFC().GetCustomerCode();
// customer.ManualCode = getValueFromCell(sheet.GetRow(row).GetCell(0));
// customer.Name = getValueFromCell(sheet.GetRow(row).GetCell(1));
// customer.Address = getValueFromCell(sheet.GetRow(row).GetCell(2));
// customer.Phone = getValueFromCell(sheet.GetRow(row).GetCell(3));
// customer.TaxFileNumber = getValueFromCell(sheet.GetRow(row).GetCell(4));
// customer.IsActive = true;
// customer.CreatedBy = customer.ModifiedBy = "SYSTEM";
// customer.CreatedDate = customer.ModifiedDate = customer.EffectiveStartDate = customer.EffectiveEndDate = DateTime.Now.Date;
// customer.MAP = false;
// customer.MaxDay = 2;
// //customer.EffectiveEndDate = DateTime.MaxValue.Date;
// customer.City = "";
// //customer.EffectiveStartDate = DateTime.MinValue;
// customer.Fax = "";
// if (!String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(5))))
// customer.SalesmanID = new SalesmanBFC().RetrieveSalesmanByName(getValueFromCell(sheet.GetRow(row).GetCell(5))).ID;
// new CustomerBFC().Create(customer);
// }
// File.Delete(Server.MapPath("~/Import/Customer List 2015.xls"));
// }
//}
//private void importProductSellingPrice()
//{
// var filename = "FINAL PRODUCT LIST JULY15";
// if (File.Exists(Server.MapPath("~/Import/FINAL PRODUCT LIST JULY15.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 2; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var productExist = new ProductBFC().RetrieveByBarcode(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// if (productExist != null)
// {
// productExist.SellingPrice = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(8)));
// productExist.SellingPriceB = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(9)));
// productExist.SellingPriceC = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(10)));
// new ProductBFC().Update(productExist);
// //var quantity = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(6)));
// //new ItemLocationBFC().Create(productExist.ID,
// //0, quantity, quantity);
// //new ProductBFC().Create(productExist);
// }
// }
// }
// LblStatus.Text = "Success!!!";
//}
//private void importProductBuyingPrice()
//{
// var filename = "UPDATED_Latest Blum buying price Aug2015";
// if (File.Exists(Server.MapPath("~/Import/"+ filename +".xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 1; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var productExist = new ProductBFC().RetrieveProductByIndentCode(getValueFromCell(sheet.GetRow(row).GetCell(0)));
// if (productExist != null)
// {
// productExist.AssetPrice = productExist.AssetPriceInDollar = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(2)));
// new ProductBFC().Update(productExist);
// //var quantity = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(6)));
// //new ItemLocationBFC().Create(productExist.ID,
// //0, quantity, quantity);
// //new ProductBFC().Create(productExist);
// }
// else
// {
// var product = new ProductModel();
// product.IndentCode = getValueFromCell(sheet.GetRow(row).GetCell(0));
// product.Code = product.Barcode = getValueFromCell(sheet.GetRow(row).GetCell(1));
// product.ConversionID = 7;
// product.ProductGroupID = 3;
// product.IsActive = false;
// product.BuyingCurrency = product.BuyingUnit = 0;
// product.AssetPrice = product.AssetPriceInDollar = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(2)));
// product.SellingPrice = product.SellingPriceB = product.SellingPriceC = 0;
// product.CreatedBy = product.ModifiedBy = "MIGRATION";
// product.CreatedDate = product.ModifiedDate = DateTime.Now;
// new ProductBFC().Create(product);
// float quantity = 0;
// new ItemLocationBFC().Create(product.ID,
// 1, quantity, quantity);
// }
// }
// }
// LblStatus.Text = "Success!!!";
//}
//private void importProductQty()
//{
// var filename = "FINAL PRODUCT LIST JULY15";
// if (File.Exists(Server.MapPath("~/Import/FINAL PRODUCT LIST JULY15.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 2; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var productExist = new ProductBFC().RetrieveByBarcode(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// if (productExist != null)
// {
// //productExist.ConversionID = new ProductBFC().RetrieveConversionByCode(getValueFromCell(sheet.GetRow(row).GetCell(7))).ID;
// //new ProductBFC().Update(productExist);
// var quantity = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(6)));
// if (quantity != null)
// {
// var itemLoc = new ItemLocationBFC().RetrieveByProductIDWarehouseID(productExist.ID, 1);
// itemLoc.QtyAvailable = quantity;
// itemLoc.QtyOnHand = quantity;
// new ItemLocationBFC().Update(itemLoc);
// }
// // new ItemLocationBFC().Update(productExist.ID,
// //0, quantity, quantity);
// }
// }
// }
// LblStatus.Text = "Success!!!";
//}
//private void importProduct()
//{
// var filename = "FINAL PRODUCT LIST JULY15";
// if (File.Exists(Server.MapPath("~/Import/FINAL PRODUCT LIST JULY15.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 2; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var productExist = new ProductBFC().RetrieveByCode(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// if (productExist == null)
// {
// var product = new ProductModel();
// product.Code = new ProductBFC().GetProductCode();
// product.ProductGroupID = new ProductGroupBFC().RetrieveByCode(getValueFromCell(sheet.GetRow(row).GetCell(1))).ID;
// product.Barcode = getValueFromCell(sheet.GetRow(row).GetCell(4));
// product.ProductName = getValueFromCell(sheet.GetRow(row).GetCell(5));
// product.IndentCode = getValueFromCell(sheet.GetRow(row).GetCell(2));
// product.MappingCode = getValueFromCell(sheet.GetRow(row).GetCell(3));
// //prodDetail.Quantity = prodDetail.QtyStart = prodDetail.QtySo =
// product.StockQty = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(6)));
// product.SellingPrice = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(8)));
// product.SellingPriceB = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(9)));
// product.SellingPriceC = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(10)));
// product.AssetPrice = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(11)));
// product.AssetPriceB = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(12)));
// var rateStr = getValueFromCell(sheet.GetRow(row).GetCell(13));
// if (rateStr != null)
// {
// RateModel rate = new MQUESTSYSDAC().RetrieveRates(rateStr.Substring(0, 3));
// product.BuyingCurrency = rate.ID;
// }
// var buyingUnit = getValueFromCell(sheet.GetRow(row).GetCell(14));
// if (buyingUnit != null )
// if (buyingUnit != "")
// product.BuyingUnit = Convert.ToInt32(buyingUnit);
// else
// product.BuyingUnit = 1;
// else
// product.BuyingUnit = 1;
// //product.BaseDoosPrice = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(3)));
// //product.StockQty = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(3)));
// //product.StockSo = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(5)));
// // product.QtyInDoos = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(7)));
// //product.StockQty = 0;//Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// product.ConversionID = new ProductBFC().RetrieveConversionByCode(getValueFromCell(sheet.GetRow(row).GetCell(7))).ID;
// //product.StockPo = product.StockSo = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(2)));
// product.IsActive = true;
// product.CreatedBy = product.ModifiedBy = "MIGRATION";
// product.CreatedDate = product.ModifiedDate = DateTime.Now.Date;
// //product.MAP = false;
// //customer.EffectiveEndDate = DateTime.MaxValue.Date;
// //product.City = "";
// //customer.EffectiveStartDate = DateTime.MinValue;
// //product.Fax = "";
// new ProductBFC().Create(product);
// }
// }
// //File.Delete(Server.MapPath("~/Import/Stock List Jan 2015.xls"));
// }
// LblStatus.Text = "Success!!!";
//}
//private void importPurchasingList()
//{
// var filename = "Purchasing List Jan 2015update2";
// if (File.Exists(Server.MapPath("~/Import/Purchasing List Jan 2015update2.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// for (int row = 3; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var inv = new InventoryModel();
// inv.Code = new InventoryBFC().GetInventoryCode();
// inv.ProductGroupID = new ProductGroupBFC().RetrieveByCode(getValueFromCell(sheet.GetRow(row).GetCell(0))).ID;
// inv.Barcode = getValueFromCell(sheet.GetRow(row).GetCell(2));
// inv.InventoryName = getValueFromCell(sheet.GetRow(row).GetCell(3));
// inv.IndentCode = getValueFromCell(sheet.GetRow(row).GetCell(1));
// //inv.MappingCode = getValueFromCell(sheet.GetRow(row).GetCell(3));
// inv.StockQty = 0; // Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(6)));
// inv.AssetPrice = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// inv.AssetPriceB = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(5)));
// //inv.SellingPriceC = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(10)));
// //product.StockQty = 0;//Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// inv.ConversionID = 1;//new ProductBFC().RetrieveConversionByCode(getValueFromCell(sheet.GetRow(row).GetCell(7))).ID;
// inv.IsActive = true;
// inv.CreatedBy = inv.ModifiedBy = "SYSTEM";
// inv.CreatedDate = inv.ModifiedDate = DateTime.Now.Date;
// new InventoryBFC().Create(inv);
// }
// //File.Delete(Server.MapPath("~/Import/Stock List Jan 2015.xls"));
// }
// LblStatus.Text = "Success!!!";
//}
//private void importStock()
//{
// var filename = "Stock List Jan 2015 UPDATE2.xls";
// if (File.Exists(Server.MapPath("~/Import/Stock List Jan 2015 UPDATE2.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// var purchaseOrder = new PurchaseOrderModel();
// var poDetails = new List<PurchaseOrderDetailModel>();
// purchaseOrder.Code = "1";
// purchaseOrder.QuotationID = 0;
// purchaseOrder.Date = DateTime.Now;
// purchaseOrder.Title = "Import " + DateTime.Now.ToShortDateString();
// purchaseOrder.Status = (int)PurchaseOrderStatus.PD;
// purchaseOrder.CreatedBy = purchaseOrder.ModifiedBy = purchaseOrder.ApprovedBy = "SYSTEM";
// purchaseOrder.CreatedDate = purchaseOrder.ModifiedDate = purchaseOrder.ApprovedDate = DateTime.Now;
// //Create
// for (int row = 3; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var poDetail = new PurchaseOrderDetailModel();
// var product = new ProductBFC().RetrieveByCode(getValueFromCell(sheet.GetRow(row).GetCell(2)));
// poDetail.ProductID = product.ID;
// poDetail.Price = poDetail.AssetPrice = poDetail.AssetPriceInDollar = poDetail.Discount = 0;
// poDetail.Quantity = Convert.ToDouble(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// poDetails.Add(poDetail);
// }
// new PurchaseOrderBFC().Create(purchaseOrder, poDetails);
// foreach (var poDetail in poDetails)
// {
// var product = new ProductBFC().RetrieveByID(poDetail.ProductID);
// var productDetails = new ProductBFC().RetrieveDetails(poDetail.ProductID);
// var prodDetail = new ProductDetailModel();
// prodDetail.PurchaseOrderID = purchaseOrder.ID;
// prodDetail.PurchaseOrderItemNo = poDetail.ItemNo;
// prodDetail.ItemNo = productDetails.Count() + 1;
// prodDetail.Date = purchaseOrder.Date;
// prodDetail.Price = prodDetail.AssetPrice = 0;
// prodDetail.Quantity = prodDetail.QtyStart = prodDetail.QtySo = Convert.ToDouble(poDetail.Quantity);
// prodDetail.IsSaved = true;
// //productDetails.Add(prodDetail);
// product.StockQty += Convert.ToDouble(poDetail.Quantity);
// new ProductBFC().Update(product, productDetails);
// }
// //File.Delete(Server.MapPath("~/Import/Stock List Jan 2015.xls"));
// }
//}
//private void updateInventoryMappingCode()
//{
// var filename = "Mapping list";
// if (File.Exists(Server.MapPath("~/Import/Mapping list.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// for (int row = 2; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var inv = new InventoryBFC().RetrieveByIndentCode(sheet.GetRow(row).GetCell(1).ToString());
// if (inv != null)
// {
// inv.MappingCode = sheet.GetRow(row).GetCell(3).ToString();
// inv.ModifiedBy = "SYSTEM";
// inv.ModifiedDate = DateTime.Now.Date;
// new InventoryBFC().Update(inv);
// }
// }
// //File.Delete(Server.MapPath("~/Import/Stock List Jan 2015.xls"));
// }
// LblStatus.Text = "Success!!!";
//}
//private void importVendor()
//{
// var filename = "vendor";
// if (File.Exists(Server.MapPath("~/Import/vendor.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 5; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var vendor = new VendorModel();
// vendor.Code = new VendorBFC().GetVendorCode();
// vendor.Name = getValueFromCell(sheet.GetRow(row).GetCell(1));
// vendor.Address = getValueFromCell(sheet.GetRow(row).GetCell(2));
// vendor.Phone = getValueFromCell(sheet.GetRow(row).GetCell(3));
// //vendor.StockSo = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// //vendor.Specification = "";
// vendor.IsActive = true;
// vendor.CreatedBy = vendor.ModifiedBy = "SYSTEM";
// vendor.CreatedDate = vendor.ModifiedDate = vendor.EffectiveStartDate = vendor.EffectiveEndDate = DateTime.Now.Date;
// //product.MAP = false;
// //customer.EffectiveEndDate = DateTime.MaxValue.Date;
// //product.City = "";
// //customer.EffectiveStartDate = DateTime.MinValue;
// //product.Fax = "";
// new VendorBFC().Create(vendor);
// }
// }
//}
//private void importMarketing()
//{
// var filename = "marketing";
// if (File.Exists(Server.MapPath("~/Import/marketing.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 5; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var salesman = new SalesmanModel();
// salesman.Code = new SalesmanBFC().GetSalesmanCode();
// salesman.Name = getValueFromCell(sheet.GetRow(row).GetCell(1));
// salesman.Address = getValueFromCell(sheet.GetRow(row).GetCell(2));
// salesman.Phone1 = getValueFromCell(sheet.GetRow(row).GetCell(4));
// // salesman.Specification = "";
// salesman.IsActive = true;
// salesman.CreatedBy = salesman.ModifiedBy = "SYSTEM";
// salesman.CreatedDate = salesman.ModifiedDate = salesman.EffectiveStartDate = salesman.EffectiveEndDate = DateTime.Now.Date;
// //product.MAP = false;
// //customer.EffectiveEndDate = DateTime.MaxValue.Date;
// //product.City = "";
// //customer.EffectiveStartDate = DateTime.MinValue;
// //product.Fax = "";
// new SalesmanBFC().Create(salesman);
// }
// }
//}
//private void importStaff()
//{
// var filename = "staff";
// if (File.Exists(Server.MapPath("~/Import/staff.xls")))
// {
// var workbook = new HSSFWorkbook(File.OpenRead(Server.MapPath("~/Import/" + filename + ".xls")));
// ISheet sheet = workbook.GetSheetAt(0);
// //Create
// //for (int row = 5; row <= sheet.LastRowNum; row++)
// for (int row = 5; row <= sheet.LastRowNum; row++)
// {
// if (String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(0))) && String.IsNullOrEmpty(getValueFromCell(sheet.GetRow(row).GetCell(1))))
// break;
// var staff = new StaffModel();
// //staff.Code = new StaffBFC().Get();
// staff.Name = getValueFromCell(sheet.GetRow(row).GetCell(1));
// staff.BasicSalary = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(2)));
// // staff.StockSo = Convert.ToDecimal(getValueFromCell(sheet.GetRow(row).GetCell(4)));
// //staff.Specification = "";
// staff.IsActive = true;
// staff.CreatedBy = staff.ModifiedBy = "SYSTEM";
// staff.CreatedDate = staff.ModifiedDate = DateTime.Now.Date;
// //product.MAP = false;
// //customer.EffectiveEndDate = DateTime.MaxValue.Date;
// //product.City = "";
// //customer.EffectiveStartDate = DateTime.MinValue;
// //product.Fax = "";
// new StaffBFC().Create(staff);
// }
// }
//}
#region "Generic Function"
private string getValueFromCell(NPOI.SS.UserModel.ICell Cell)
{
string Value = "";
try
{
if (Cell.CellType == CellType.STRING)
{
Value = Cell.StringCellValue;
}
else if (Cell.CellType == CellType.NUMERIC)
{
Value = Convert.ToString(Cell.NumericCellValue);
}
else
{
Value = Cell.StringCellValue;
}
}
catch (Exception)
{
return(null);
}
return(Value);
}
public void TestNotCreateEmptyCells()
{
IWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = new SheetBuilder(wb, testData).Build();
Assert.AreEqual(sheet.PhysicalNumberOfRows, 3);
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
NPOI.SS.UserModel.ICell firstCell = firstRow.GetCell(0);
Assert.AreEqual(firstCell.CellType, CellType.NUMERIC);
Assert.AreEqual(1.0, firstCell.NumericCellValue, 0.00001);
NPOI.SS.UserModel.IRow secondRow = sheet.GetRow(1);
Assert.IsNotNull(secondRow.GetCell(0));
Assert.IsNull(secondRow.GetCell(2));
NPOI.SS.UserModel.IRow thirdRow = sheet.GetRow(2);
Assert.AreEqual(CellType.STRING, thirdRow.GetCell(0).CellType);
String cellValue = thirdRow.GetCell(0).StringCellValue;
Assert.AreEqual(testData[2][0].ToString(), cellValue);
Assert.AreEqual(CellType.FORMULA, thirdRow.GetCell(2).CellType);
Assert.AreEqual("A1+B2", thirdRow.GetCell(2).CellFormula);
}
/// <summary>
/// 赋值单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="list"></param>
private static void SetPurchaseOrder(ISheet sheet, List <TemplateMode> list)
{
try
{
foreach (var item in list)
{
IRow row = null;
ICell cell = null;
row = sheet.GetRow(item.row);
if (row == null)
{
row = sheet.CreateRow(item.row);
}
cell = row.GetCell(item.cell);
if (cell == null)
{
cell = row.CreateCell(item.cell);
}
cell.SetCellValue(item.value);
}
}
catch (Exception)
{
throw;
}
}
public static NPOI.SS.UserModel.ICell GetCell(IRow row, int line, IRow modelRow = null)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(line);
if (cell == null)
{
if (modelRow != null)
{
var modelcell = modelRow.GetCell(line);
if (modelcell != null)
{
cell = row.CreateCell(line, modelcell.CellType);
cell.CellStyle = modelcell.CellStyle;
}
else
{
cell = row.CreateCell(line);
}
}
else
{
cell = row.CreateCell(line);
}
}
return(cell);
}
public override bool Equals(Object obj)
{
NPOI.SS.UserModel.ICell cellb = ((HSSFEvaluationCell)obj)._cell;
return(_cell.RowIndex == cellb.RowIndex &&
_cell.ColumnIndex == cellb.ColumnIndex &&
_cell.CellFormula == cellb.CellFormula &&
_cell.Sheet == cellb.Sheet);
}
private void btnExcelExport_Click(object sender, EventArgs e)
{
ManagerInfoBll miBll = new ManagerInfoBll();
var list = miBll.GetList();
dataGridView1.DataSource = list;
//创建Excel工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
//创建单元格样式
ICellStyle cellTitleStyle = workbook.CreateCellStyle();
//设置单元格居中显示
cellTitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//创建字体
IFont font = workbook.CreateFont();
//设置字体加粗显示
font.IsBold = true;
cellTitleStyle.SetFont(font);
//创建Excel工作表
ISheet sheet = workbook.CreateSheet("管理员");
//创建Excel行
IRow row = sheet.CreateRow(0);
//创建Excel单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
//设置单元格值
cell.SetCellValue("管理员管理");
//设置单元格合并
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
cell.CellStyle = cellTitleStyle;
for (int i = 0; i < list.Count; i++)
{
IRow rowDate = sheet.CreateRow(i + 1);
Type t = list[i].GetType();
int count = 0;
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(list[i]);
string name = pi.Name;
NPOI.SS.UserModel.ICell cellDate = rowDate.CreateCell(count);
if (i == 0)
{
cellDate.SetCellValue(name);
}
else
{
cellDate.SetCellValue(value.ToString());
}
sheet.AutoSizeColumn(count);
count++;
}
}
using (FileStream fs = new FileStream(@"C:\Users\Saber\Desktop\Demo.xlsx", FileMode.OpenOrCreate))
{
workbook.Write(fs);
}
}
/// <summary>
/// render datatable to workbook stream
/// </summary>
/// <param name="sourceTable">datatable</param>
/// <returns>workbook stream</returns>
public static Stream RenderDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
//設定 Header Style
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headerStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
headerStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
headerStyle.BorderTop = headerStyle.BorderLeft;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HSSFFont headerFont = (HSSFFont)workbook.CreateFont();
headerFont.Color = HSSFColor.White.Index;
headerFont.Boldweight = 1;
headerStyle.SetFont(headerFont);
// handling header.
for (int i = 0; i < sourceTable.Columns.Count; i++)
{
DataColumn column = sourceTable.Columns[i];
NPOI.SS.UserModel.ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerStyle;
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static List <List <List <string> > > ReadExcel(string fileName)
{
//打开Excel工作簿
XSSFWorkbook hssfworkbook = null;
try
{
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
//LogHandler.LogWrite(string.Format("文件{0}打开失败，错误：{1}", new string[] { fileName, e.ToString() }));
}
//循环Sheet页
int sheetsCount = hssfworkbook.NumberOfSheets;
List <List <List <string> > > workBookContent = new List <List <List <string> > >();
for (int i = 0; i < sheetsCount; i++)
{
//Sheet索引从0开始
ISheet sheet = hssfworkbook.GetSheetAt(i);
//循环行
List <List <string> > sheetContent = new List <List <string> >();
int rowCount = sheet.PhysicalNumberOfRows;
for (int j = 0; j < rowCount; j++)
{
//Row（逻辑行）的索引从0开始
IRow row = sheet.GetRow(j);
//循环列（各行的列数可能不同）
List <string> rowContent = new List <string>();
int cellCount = row.PhysicalNumberOfCells;
for (int k = 0; k < cellCount; k++)
{
//ICell cell = row.GetCell(k);
NPOI.SS.UserModel.ICell cell = row.Cells[k];
if (cell == null)
{
rowContent.Add("NIL");
}
else
{
rowContent.Add(cell.ToString());
//rowContent.Add(cell.StringCellValue);
}
}
//添加行到集合中
sheetContent.Add(rowContent);
}
//添加Sheet到集合中
workBookContent.Add(sheetContent);
}
return(workBookContent);
}
/// <summary>
/// 从指定的sheet中获取指定的cell
/// </summary>
/// <param name="sheet"></param>
/// <param name="row"></param>
/// <param name="column"></param>
/// <returns></returns>
public static ICell GetCell(ISheet sheet, int row, int column)
{
NPOI.SS.UserModel.ICell cell = null;
if (sheet != null)
{
cell = sheet.GetRow(row).GetCell(column);
}
return(cell);
}
public void TestEmptyCells()
{
NPOI.SS.UserModel.IWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = new SheetBuilder(wb, testData).SetCreateEmptyCells(true).Build();
NPOI.SS.UserModel.ICell emptyCell = sheet.GetRow(1).GetCell(1);
Assert.IsNotNull(emptyCell);
Assert.AreEqual(CellType.BLANK, emptyCell.CellType);
}
private static DataTable ReadFromFile(string filePath)
{
IWorkbook hssfworkbook;
#region 初始化信息
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(file) as IWorkbook;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
IRow row = rows.Current as IRow;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//将第一列作为列表头
DataColumn dtc = new DataColumn(row.GetCell(j).ToString(), typeof(string));
dt.Columns.Add(dtc);
}
while (rows.MoveNext())
{
try
{
row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
Console.WriteLine(cell == null ? "" : cell + "\t\t");
Console.WriteLine();
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
catch (Exception ex)
{
continue;
// throw;
}
}
return(dt);
}
private NPOI.SS.UserModel.ICell SetBorder(NPOI.SS.UserModel.ICell cell)
{
cell.CellStyle.BorderLeft = BorderStyle.Thin;
cell.CellStyle.BorderRight = BorderStyle.Thin;
cell.CellStyle.BorderTop = BorderStyle.Thin;
cell.CellStyle.BorderBottom = BorderStyle.Thin;
cell.CellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
return(cell);
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.IRow row, int column)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return(cell);
}
/// <summary>
/// 读取模板，根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="ModUrl">模板地址</param>
/// <param name="startRowIndex">起始行,首行=0，默认0</param>
/// <param name="RemoveOtherRows">是否移除起始行后的数据，默认=是</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList_Mod(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string ModUrl, int startRowIndex = 0, bool RemoveOtherRows = true, string SheetName = "Sheet1")
{
startRowIndex = startRowIndex >= 0 ? startRowIndex : 0;
string ExcelModePath = NodeBase + ModUrl;
if (!File.Exists(ExcelModePath))
{
return(null);
}
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
using (FileStream fileExcelMod = new FileStream(ExcelModePath, FileMode.Open, FileAccess.Read))
{
wk = new XSSFWorkbook(fileExcelMod);
fileExcelMod.Close();
}
if (wk == null)
{
return(null);
}
//创建一个Sheet
ISheet tb = wk.GetSheetAt(0);
#region 移除起始行后的所有行
if (RemoveOtherRows && tb.LastRowNum > startRowIndex + 1)
{
for (int rmr = tb.LastRowNum - 1; rmr > startRowIndex; rmr--)
{
tb.ShiftRows(rmr, rmr + 1, -1);
}
}
#endregion
//设置单元格宽度
//创建表头(在第0行)
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + startRowIndex);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
// HSSFWorkbook hssfworkbook;
IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
string fileExt = Path.GetExtension(strFileName);
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
hssfworkbook = new XSSFWorkbook(file);
}
else
{
return(new DataTable());
}
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
/// <summary>
/// xlsx
/// </summary>
/// <param name="Postfile"></param>
/// <param name="dt"></param>
/// <param name="iStartRow"></param>
/// <param name="iStartColumn"></param>
/// <returns></returns>
private static string ImportExcelFileXSSF(HttpPostedFileBase Postfile, DataTable dt, int iStartRow, int iStartColumn)
{
XSSFWorkbook Xssfworkbook;
#region//初始化信息
try
{
//.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file);
//而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);
Stream file = Postfile.InputStream;
Xssfworkbook = new XSSFWorkbook(file);
//HSSFWorkbook workbook = new HSSFWorkbook(file);
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = Xssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int i = 0; i < iStartRow; i++)
{
rows.MoveNext();
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
int j = 0;
for (int i = iStartColumn; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[j] = null;
}
else
{
if (dt.Columns[j].DataType.FullName == "System.Int32")
{
dr[j] = Convert.ToInt32(cell.ToString());
}
else
{
dr[j] = cell;
}
}
j++;
}
dt.Rows.Add(dr);
}
return(JsonHelper.DataTable2Json(dt));
}
/// <summary>
/// Creates a cell, gives it a value, and applies a style if provided
/// </summary>
/// <param name="row">the row to Create the cell in</param>
/// <param name="column">the column index to Create the cell in</param>
/// <param name="value">The value of the cell</param>
/// <param name="style">If the style is not null, then Set</param>
/// <returns>A new HSSFCell</returns>
public static NPOI.SS.UserModel.ICell CreateCell(NPOI.SS.UserModel.IRow row, int column, String value, HSSFCellStyle style)
{
NPOI.SS.UserModel.ICell cell = GetCell(row, column);
cell.SetCellValue(new HSSFRichTextString(value));
if (style != null)
{
cell.CellStyle = (style);
}
return(cell);
}
public static DataTable GetData(string filePath)
{
IWorkbook workbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
//hssfworkbook = new HSSFWorkbook(file);
workbook = WorkbookFactory.Create(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
//HSSFRow row = (HSSFRow)rows.Current;
IRow row = (IRow)rows.Current;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
private static void SetCell(ICell newCell, ICellStyle dateStyle, Type dataType, string drValue)
{
switch (dataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
if (System.DateTime.TryParse(drValue, out dateV))
{
newCell.SetCellValue(dateV);
}
else
{
newCell.SetCellValue("");
}
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
//protected void Button1_Click(object sender, EventArgs e)
//{
// using (MemoryStream ms = Export())
// {
// Response.ContentType = "application/vnd.ms-word";
// Response.ContentEncoding = Encoding.UTF8;
// Response.Charset = "";
// Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("123.doc", Encoding.UTF8));
// Response.BinaryWrite(Export().GetBuffer());
// Response.End();
// }
//}
//private void ExportExcel(string fileName)
//{
// //byte[] byteArray;
// //if (Request.Browser.Browser == "IE")
// // eFilePath = HttpUtility.UrlEncode(eFilePath);
// //using (FileStream fs = new FileStream(eFilePath, FileMode.Open))
// //{
// // byteArray = new byte[fs.Length];
// // fs.Read(byteArray, 0, byteArray.Length);
// //}
// //Response.Buffer = false;
// //Response.Clear();
// //Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document; name=" + eFilePath;
// //Response.AddHeader("content-disposition", "attachment;filename=" + fileName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
// //Response.BinaryWrite(byteArray);
// //Response.End();
//}
public static MemoryStream ExportExcel(string templateFileName)
{
string filePath = templateFileName;
XSSFWorkbook workBook = null;
XSSFSheet sheet1 = null;
using (FileStream fs = File.OpenRead(filePath))
{
workBook = new XSSFWorkbook(fs);
sheet1 = (XSSFSheet)workBook.GetSheet("Sheet1");
//添加或修改WorkSheet里的数据
System.Data.DataTable dt = new System.Data.DataTable();
//dt = DbHelperMySQLnew.Query("select * from t_jb_info where id='" + id + "'").Tables[0];
//if (dt.Rows.Count > 0)
//{
// if (!string.IsNullOrEmpty(dt.Rows[0]["blrq"].ToString()))
// {
//sheet.GetRow(2).GetCell(1).SetCellValue("56565");
//sheet.GetRow(2).GetCell(2).SetCellValue("hahaha");
//sheet.GetRow(2).GetCell(3).SetCellValue(DateTime.Now.ToString());
// }
//}
// 创建新增行
for (var i = 1; i <= 10; i++)
{
IRow row1 = sheet1.CreateRow(i);
for (var j = 0; j < 10; j++)
{
//新建单元格
NPOI.SS.UserModel.ICell cell = row1.CreateCell(j);
// 单元格赋值
cell.SetCellValue("");
}
}
sheet1.GetRow(1).GetCell(0).SetCellValue("56565");
sheet1.GetRow(1).GetCell(1).SetCellValue("hahaha");
sheet1.GetRow(1).GetCell(2).SetCellValue(DateTime.Now.ToString());
sheet1.ForceFormulaRecalculation = true;
}
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
return(ms);
}
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public HSSFEvaluationCell(NPOI.SS.UserModel.ICell cell, IEvaluationSheet evalSheet)
{
_cell = cell;
_evalSheet = evalSheet;
}
public HSSFEvaluationCell(NPOI.SS.UserModel.ICell cell)
{
_cell = cell;
_evalSheet = new HSSFEvaluationSheet((HSSFSheet)cell.Sheet);
}