//处理excel2007
private static DataTable ExcelToDataTableFirstRowAsHeader(XSSFSheet sheet, XSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
XSSFRow firstRow = sheet.GetRow(0) as XSSFRow;
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return(dt);
}
}
protected void Button7_Click(object sender, EventArgs e) //匯入
{
if (FileUpload1.HasFile)
{
try
{
//XSSFWorkbook 活頁簿
XSSFWorkbook myWorkbook = new XSSFWorkbook(FileUpload1.FileContent);
//建立XSSFSHEET 工作表
ISheet mySheet = myWorkbook.GetSheetAt(0);
//建立DATATABLE
DataTable myDT = new DataTable();
//抓取MYSHEET工作表中的標題欄位，並存入DATATABLE
XSSFRow headerRow = mySheet.GetRow(0) as XSSFRow;
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum - 2; i++)
{
if (headerRow.GetCell(i) != null)
{
DataColumn myColumn = new DataColumn(headerRow.GetCell(i).StringCellValue);
myDT.Columns.Add(myColumn);
}
}
//抓取XSSFSHEET第一列以後的所有資料，並存入DATATABLE中
for (int i = mySheet.FirstRowNum + 1; i <= mySheet.LastRowNum; i++)
{
XSSFRow row = mySheet.GetRow(i) as XSSFRow;
DataRow myRow = myDT.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum - 2; j++)
{
if (row.GetCell(j) != null)
{
myRow[j] = row.GetCell(j).ToString();
}
}
myDT.Rows.Add(myRow);
}
//釋放活頁簿、工作表資源
myWorkbook = null;
mySheet = null;
DataView myView = new DataView(myDT);
GridView2.DataSource = myDT;
GridView2.DataBind();
Label9.Text = "上傳成功";
}
catch (Exception ex)
{
Response.Write("thie Error Message---" + ex.ToString());
}
}
else
{
Label9.Text = "請先挑選檔案之後";
}
}
private void assertCellsWithMissingR(XSSFRow row)
{
XSSFCell a1 = (XSSFCell)row.GetCell(0);
Assert.IsNotNull(a1);
XSSFCell a2 = (XSSFCell)row.GetCell(1);
Assert.IsNotNull(a2);
XSSFCell a5 = (XSSFCell)row.GetCell(4);
Assert.IsNotNull(a5);
XSSFCell a6 = (XSSFCell)row.GetCell(5);
Assert.IsNotNull(a6);
Assert.AreEqual(6, row.LastCellNum);
Assert.AreEqual(4, row.PhysicalNumberOfCells);
Assert.AreEqual(a1.StringCellValue, "A1");
Assert.AreEqual(a2.StringCellValue, "B1");
Assert.AreEqual(a5.StringCellValue, "E1");
Assert.AreEqual(a6.StringCellValue, "F1");
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
Assert.AreEqual(a1.GetReference(), "A1");
Assert.AreEqual(a2.GetReference(), "B1");
Assert.AreEqual(a5.GetReference(), "E1");
Assert.AreEqual(a6.GetReference(), "F1");
}
private static DataTable getDaTableBy07Excel(Stream excelFileStream, int sheetIndex = 0, int headerRowIndex = 0)
{
DataTable table = new DataTable();
XSSFWorkbook workbook = new XSSFWorkbook(excelFileStream);
XSSFSheet sheet = workbook.GetSheetAt(sheetIndex) as XSSFSheet;
XSSFRow headerRow = sheet.GetRow(headerRowIndex) as XSSFRow; //表头行的索引
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = 0; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
if (row == null || row.Cells == null || row.Cells.TrueForAll(p => string.IsNullOrWhiteSpace(p.ToString())))
{
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j) != null?row.GetCell(j) + "" : "";
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return(table);
}
/// <summary>
/// 生成对应的cs文件
/// </summary>
/// <param name="sheet"></param>
static void GenertorDao(XSSFSheet sheet)
{
CodeGenerator code = new CodeGenerator();
code.PrintLine("//***************************************************************");
code.PrintLine("//类名：", sheet.SheetName, "Ex");
code.PrintLine("//作者：", System.Environment.MachineName);
code.PrintLine("//日期：", DateTime.Now.ToString());
code.PrintLine("//作用：", sheet.SheetName, "的数据类");
code.PrintLine("//注意:", "不要在此类里面写代码!!!");
code.PrintLine("//***************************************************************");
code.PrintLine();
code.PrintLine("using System;");
code.PrintLine("using System.Collections.Generic;");
code.PrintLine();
code.PrintLine("public class ", sheet.SheetName, "{");
code.In();
#region 生成变量
XSSFRow typeRow = (XSSFRow)sheet.GetRow(0);
XSSFRow desRow = (XSSFRow)sheet.GetRow(1);
XSSFRow nameRow = (XSSFRow)sheet.GetRow(2);
for (int i = 0; i < typeRow.LastCellNum; i++)
{
string type = typeRow.GetCell(i).ToString();
string des = string.Empty;
if (desRow.GetCell(i) != null)
{
des = desRow.GetCell(i).ToString();
}
string name = nameRow.GetCell(i).ToString();
code.PrintLine("/// <summary>");
code.PrintLine("///" + des);
code.PrintLine("/// </summary>");
if (!type.Contains("List") && !type.Contains("Dictionary"))
{
code.PrintLine("public ", type, " ", name, ";");
}
else
{
code.PrintLine("public ", type, " ", name, "=new ", type, "();");
}
}
#endregion
code.Out();
code.PrintLine("}");
code.WriteFile(codePath + sheet.SheetName + ".cs");
Console.WriteLine(sheet.SheetName + ".cs 代码生成完成");
Log.Append(sheet.SheetName + ".cs 代码生成完成\n");
}
/// <summary>
/// Xlsx文件转换(可以获取第一行标题)
/// </summary>
/// <param name="fs">excel文件流</param>
/// <param name="sheetIndex">sheet页索引，默认0</param>
/// <returns></returns>
public static DataTable XlsxTitleToDataTable(FileStream fs, int sheetIndex = 0)
{
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
XSSFWorkbook workbook = new XSSFWorkbook(fs);
//获取excel的第一个sheet
XSSFSheet sheet = workbook.GetSheetAt(sheetIndex) as XSSFSheet;
DataTable table = new DataTable();
//获取sheet的首行
XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
if (headerRow != null)
{
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum); i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric)
{
dataRow[j] = row.GetCell(j).NumericCellValue;
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return(table);
}
else
{
return(null);
}
}
/// <summary>读取excel2007
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
private static DataTable Import2007(string strFileName)
{
//2013/10/22 夏梁峰 add begin
//修改使用NPOI读取excel
//需求编号：OTS_SZDX_01_R00027_D00001
XSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
DataTable dt = new DataTable();
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = headerRow.GetCell(j) as XSSFCell;
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
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);
//2013/10/22 夏梁峰 add end
//2013/10/22 夏梁峰 删除
//string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
//OleDbConnection myConn = new OleDbConnection(strCon);
//string strCom = " SELECT * FROM [Sheet1$]";
//myConn.Open();
//OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//DataSet myDataSet = new DataSet();
//myCommand.Fill(myDataSet, "[Sheet1$]");
//myConn.Close();
//return myDataSet.Tables[0];
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
public void TestCopyRowFrom()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet("test") as XSSFSheet;
XSSFRow srcRow = sheet.CreateRow(0) as XSSFRow;
srcRow.CreateCell(0).SetCellValue("Hello");
XSSFRow destRow = sheet.CreateRow(1) as XSSFRow;
destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
Assert.IsNotNull(destRow.GetCell(0));
Assert.AreEqual("Hello", destRow.GetCell(0).StringCellValue);
workbook.Close();
}
public static DataTable ImportExcelFileXSSF_Org(HttpFileCollectionBase files)
{
XSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
//.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file);
//而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);
Stream file = files[0].InputStream;
hssfworkbook = new XSSFWorkbook(file);
//HSSFWorkbook workbook = new HSSFWorkbook(file);
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
XSSFRow row = (XSSFRow)rows.Current;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
row = (XSSFRow)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);
}
void ConvertToDataTable()
{
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
SetData(dt);
}
public static DataTable readExcel(string filePath, string sheetName, int headIndex = 0)
{
DataTable table = new DataTable();
try
{
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(sheetName);
XSSFRow headRow = (XSSFRow)sheet.GetRow(headIndex);
int columnCount = headRow.Cells.Count;
//获取标题名
for (int index = 0; index < headRow.Cells.Count; index++)
{
DataColumn dc = new DataColumn(headRow.GetCell(index).ToString());
table.Columns.Add(dc);
}
for (int i = sheet.FirstRowNum + 1 + headIndex; i <= sheet.LastRowNum; i++)
{
DataRow newRow = table.NewRow();
XSSFRow row = (XSSFRow)sheet.GetRow(i);
int isEmpty = 0;
if (row != null)
{
//复制整行数据
for (int j = 0; j < columnCount - 1; j++)
{
//if (row.GetCell(j).ToString()==string.Empty)
//{
// isEmpty++;
//}
newRow[j] = row.GetCell(j);
if (newRow[j].ToString() == string.Empty || newRow[j].ToString() == "NULL")
{
newRow[j] = null;
isEmpty++;
}
}
if (isEmpty == columnCount)
{
continue;
}
//添加到数据表中
table.Rows.Add(newRow);
}
}
}
}
catch (Exception exMsg)
{
throw (exMsg);
//throw (exMsg);
}
return(table);
}
private static void CopyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List <CellRangeAddress> mergedRegions)
{
destRow.Height = srcRow.Height;
for (int j = srcRow.FirstCellNum; srcRow.LastCellNum >= 0 && j <= srcRow.LastCellNum; j++)
{
var oldCell = (HSSFCell)srcRow.GetCell(j);
var newCell = (XSSFCell)destRow.GetCell(j);
if (oldCell != null)
{
if (newCell == null)
{
newCell = (XSSFCell)destRow.CreateCell(j);
}
CopyCell(oldCell, newCell);
var mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
var newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
destSheet.AddMergedRegion(newMergedRegion);
}
}
}
}
}
/// <summary>
/// 用NPOI直接读取excel返回DataTable
/// </summary>
/// <param name="ExcelFileStream"></param>
/// <param name="SheetIndex"></param>
/// <param name="StartRowIndex"></param>
/// <returns></returns>
public static DataTable ReadXlsxToDataTable(Stream ExcelFileStream, int SheetIndex, int StartRowIndex)
{
XSSFWorkbook workbook = new XSSFWorkbook(ExcelFileStream);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(SheetIndex);
bool isHaveData = false;
DataTable table = new DataTable();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(StartRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (StartRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
isHaveData = false;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null && row.GetCell(j).ToString() != "")
{
dataRow[j] = row.GetCell(j).ToString();
isHaveData = true;
}
}
if (isHaveData)
{
table.Rows.Add(dataRow);
}
}
catch (Exception)
{
}
}
ExcelFileStream.Close();
workbook = null;
sheet = null;
return(table);
}
/// <summary>
/// Convert Excel sheets to DataTable list
/// </summary>
/// <param name="filename"></param>
/// <returns>list of datatable</returns>
public static List <DataTable> ExceltoDataTable(string filename)
{
XSSFWorkbook xssfwb;
List <DataTable> dts = new List <DataTable>();
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
for (int i = 0; i < xssfwb.NumberOfSheets; i++)
{
XSSFSheet sheet = (XSSFSheet)xssfwb.GetSheetAt(i);
DataTable dt = new DataTable();
int num = 0;
while (sheet.GetRow(num) != null)
{
if (dt.Columns.Count < sheet.GetRow(num).Cells.Count)
{
for (int j = 0; j < sheet.GetRow(num).Cells.Count; j++)
{
dt.Columns.Add("", typeof(string));
}
}
XSSFRow row = (XSSFRow)sheet.GetRow(num);
DataRow dr = dt.Rows.Add();
for (int k = 0; k < row.Cells.Count; k++)
{
XSSFCell cell = (XSSFCell)row.GetCell(k);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
dr[k] = cell.NumericCellValue;
break;
case CellType.String:
dr[k] = cell.StringCellValue;
break;
case CellType.Blank:
dr[k] = "";
break;
case CellType.Boolean:
dr[k] = cell.BooleanCellValue;
break;
}
}
}
num++;
}
dts.Add(dt);
}
return(dts);
}
private static void MergeData(string path, DataTable dt)
{
// write data in workbook from xls document.
XSSFWorkbook workbook = new XSSFWorkbook(path);
// read the current table data
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
// read the current row data
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
// LastCellNum is the number of cells of current rows
int cellCount = headerRow.LastCellNum;
if (dt.Rows.Count == 0)
{
// build header for there is no data after the first implementation
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
// get data as the column header of DataTable
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
}
else
{
// TODO: check if the subsequent sheet corresponds
}
// LastRowNum is the number of rows of current table
int rowCount = sheet.LastRowNum + 1;
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
// get data and convert them into character string type, then save them into the rows of datatable
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
}
private void GetSalaryList(ISheet mainSheet)
{
FileStream salaryFileStream = new FileStream(@"e:/2018年3月发薪名单.xlsx", FileMode.Open);
XSSFWorkbook salaryWorkbook = new XSSFWorkbook(salaryFileStream);
XSSFSheet salarySheet = (XSSFSheet)salaryWorkbook.GetSheet("Sheet1");
for (int i = 0; i < salarySheet.PhysicalNumberOfRows; i++)
{
XSSFRow tRow = (XSSFRow)mainSheet.CreateRow(i);
XSSFRow sRow = (XSSFRow)salarySheet.GetRow(i);
if (sRow != null && tRow != null)
{
for (int j = 0; j < 2; j++)
{
XSSFCell sCell = (XSSFCell)sRow.GetCell(j);
if (sCell == null)
{
break;
}
string cellValue = sCell.ToString();
XSSFCell tCell = (XSSFCell)tRow.CreateCell(j);
//CopyCellStyle(mainWorkbook, salaryWorkbook, tCell, sCell);
XSSFCellStyle style = (XSSFCellStyle)sCell.CellStyle;
XSSFCellStyle style1 = (XSSFCellStyle)mainWorkbook.CreateCellStyle();
XSSFColor color = null;
if (style.FillForegroundColorColor != null)
{
byte[] pa = style.FillForegroundColorColor.RGB;
string key = pa[0] + "," + pa[1] + "," + pa[2];
if (dictionary.ContainsKey(key))
{
style1.FillForegroundColor = dictionary[key];
}
else
{
Console.WriteLine("找不到该颜色!" + key);
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
}
else
{
Console.WriteLine("找不到该颜色!");
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
//byte[] pa1 = style.FillBackgroundColorColor.RGB;
//style1.FillForegroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
style1.FillPattern = sCell.CellStyle.FillPattern;
//style1.FillBackgroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
tCell.CellStyle = style1;
tCell.SetCellValue(cellValue);
}
}
}
salaryFileStream.Close();
salaryWorkbook.Close();
}
/// <summary> 取Excel Row的欄位值
///
/// </summary>
/// <param name="Row">Excel Row</param>
/// <param name="Cellindex">第幾個欄位(0為起始)</param>
/// <returns></returns>
public string GetCellValue(XSSFRow Row, int Cellindex)
{
string result = string.Empty;
//取得欄位的值，如果為null或空白則返回空值
result = Row.GetCell(Cellindex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString() ?? "";
return(result);
}
/// <summary>
/// xlsx导入DataTable
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="sheetName">Excel工作表索引</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromXlsx(string fileName, int sheetIndex, int headerRowIndex)
{
XSSFWorkbook workbook;
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(sheetIndex);
DataTable table = new DataTable();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i + 1;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)sheet.GetRow(i);
if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
{
// 如果遇到第一个空行，则不再继续向后读取
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = (XSSFCell)row.GetCell(j);
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return(table);
}
public void TestCopyRowOverwritesExistingRow()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet1 = workbook.CreateSheet("Sheet1") as XSSFSheet;
ISheet sheet2 = workbook.CreateSheet("Sheet2");
IRow srcRow = sheet1.CreateRow(0);
XSSFRow destRow = sheet1.CreateRow(1) as XSSFRow;
IRow observerRow = sheet1.CreateRow(2);
IRow externObserverRow = sheet2.CreateRow(0);
srcRow.CreateCell(0).SetCellValue("hello");
srcRow.CreateCell(1).SetCellValue("world");
destRow.CreateCell(0).SetCellValue(5.0); //A2 -> 5.0
destRow.CreateCell(1).CellFormula = ("A1"); // B2 -> A1 -> "hello"
observerRow.CreateCell(0).CellFormula = ("A2"); // A3 -> A2 -> 5.0
observerRow.CreateCell(1).CellFormula = ("B2"); // B3 -> B2 -> A1 -> "hello"
externObserverRow.CreateCell(0).CellFormula = ("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0
// overwrite existing destRow with row-copy of srcRow
destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
// copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer
// to the new row (and allow the old row to be garbage collected)
// this is mostly so existing references to rows that are overwritten are updated
// rather than allowing users to continue updating rows that are no longer part of the sheet
Assert.AreSame(srcRow, sheet1.GetRow(0), "existing references to srcRow are still valid");
Assert.AreSame(destRow, sheet1.GetRow(1), "existing references to destRow are still valid");
Assert.AreSame(observerRow, sheet1.GetRow(2), "existing references to observerRow are still valid");
Assert.AreSame(externObserverRow, sheet2.GetRow(0), "existing references to externObserverRow are still valid");
// Make sure copyRowFrom actually copied row (this is tested elsewhere)
Assert.AreEqual(CellType.String, destRow.GetCell(0).CellType);
Assert.AreEqual("hello", destRow.GetCell(0).StringCellValue);
// We don't want #REF! errors if we copy a row that contains cells that are referred to by other cells outside of copied region
Assert.AreEqual("A2", observerRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified");
Assert.AreEqual("B2", observerRow.GetCell(1).CellFormula, "references to overwritten cells are unmodified");
Assert.AreEqual("Sheet1!A2", externObserverRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified");
workbook.Close();
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2007ToString(string path)
{
XSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
}
/*
* ②：将文档保存到指定路径
*/
string destFileName = @"D:\test.xlsx";
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
sb.Append("</table>");
return(sb.ToString());
}
public static DataTable ImportExcelXlsXFile(string filePath, string extension, int sheetnum = 0, int columnnum = 0)
{
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
xssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
NPOI.SS.UserModel.ISheet sheet;
sheet = xssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//给DdataTable添加表头
if (sheet.GetRow(sheetnum) != null)
{
for (int j = 0; j < (sheet.GetRow(columnnum).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(columnnum).Cells[j].ToString());
}
//读取数据
while (rows.MoveNext())
{
XSSFRow row2 = null;
row2 = (XSSFRow)rows.Current;
int j = row2.LastCellNum;
DataRow dr = dt.NewRow();
if (j > dt.Columns.Count)
{
j = dt.Columns.Count;
}
for (int i = 0; i < j; i++)
{
NPOI.SS.UserModel.ICell cell = row2.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
private static void CopyRow(XSSFSheet srcSheet, HSSFSheet destSheet, XSSFRow srcRow, HSSFRow destRow,
Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
// manage a list of merged zone in order to not insert two times a
// merged zone
List <CellRangeAddress> mergedRegions = new List <CellRangeAddress>();
destRow.Height = srcRow.Height;
// pour chaque row
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
XSSFCell oldCell = (XSSFCell)srcRow.GetCell(j); // ancienne cell
HSSFCell newCell = (HSSFCell)destRow.GetCell(j); // new cell
if (oldCell != null)
{
if (newCell == null)
{
newCell = (HSSFCell)destRow.CreateCell(j);
}
// copy chaque cell
CopyCell(oldCell, newCell, styleMap, retVal);
// copy les informations de fusion entre les cellules
CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
destSheet.AddMergedRegion(newMergedRegion);
}
if (newMergedRegion.FirstColumn == 0 && newMergedRegion.LastColumn == 6 && newMergedRegion.FirstRow == newMergedRegion.LastRow)
{
HSSFCellStyle style2 = (HSSFCellStyle)retVal.CreateCellStyle();
style2.VerticalAlignment = VerticalAlignment.Center;
style2.Alignment = HorizontalAlignment.Left;
style2.FillForegroundColor = HSSFColor.Teal.Index;
style2.FillPattern = FillPattern.SolidForeground;
for (int i = destRow.FirstCellNum; i <= destRow.LastCellNum; i++)
{
if (destRow.GetCell(i) != null)
{
destRow.GetCell(i).CellStyle = style2;
}
}
}
}
}
}
}
public static DataTable ImportExcelFilexlsx(string filePath)
{
XSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).GetCell(j).ToString());
}
//sheet.cu
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
if (row.RowNum != 0)
{
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);
}
public static XSSFSheet ChangeColor(XSSFSheet sheet, int x, int y, Color color, XSSFCellStyle cellstyle)
{
XSSFRow row = (XSSFRow)sheet.GetRow(x);
XSSFCell cell = (XSSFCell)row.GetCell(y);
XSSFColor XlColour = new XSSFColor(color);
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cell.CellStyle = cellstyle;
return(sheet);
}
//Excel第一行为标题名
public static DataTable readExcel(string filePath, ref List <string> columnNameArray)
{
DataTable table = new DataTable();
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow headRow = (XSSFRow)sheet.GetRow(0);
int columnCount = headRow.Cells.Count;
//获取标题名
for (int index = 0; index < headRow.Cells.Count; index++)
{
DataColumn dc = new DataColumn(headRow.GetCell(index).StringCellValue.ToString());
table.Columns.Add(dc);
columnNameArray.Add(headRow.GetCell(index).ToString());
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = table.NewRow();
XSSFRow row = (XSSFRow)sheet.GetRow(i);
if (row != null)
{
if (row.GetCell(1) != null)
{
//复制整行数据
for (int j = 0; j < columnCount; j++)
{
newRow[j] = row.GetCell(j);
}
//添加到数据表中
table.Rows.Add(newRow);
}
}
}
}
return(table);
}
public static DataTable readExcel(string filePath, List <string> columnNameList, int sheetNum, int startRow)
{
DataTable table = new DataTable();
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(sheetNum);
XSSFRow headRow = (XSSFRow)sheet.GetRow(0);
int columnCount = columnNameList.Count;
DataColumn[] dcArray = new DataColumn[columnCount];
//获取标题名
for (int index = 0; index < columnCount; index++)
{
dcArray[index] = new DataColumn(columnNameList[index]);
}
table.Columns.AddRange(dcArray);
for (int i = sheet.FirstRowNum + startRow; i <= sheet.LastRowNum; i++)
{
DataRow newRow = table.NewRow();
XSSFRow row = (XSSFRow)sheet.GetRow(i);
if (row != null)
{
if (row.GetCell(1) != null)
{
//复制整行数据
for (int j = 0; j < columnCount; j++)
{
newRow[j] = row.GetCell(j);
}
//添加到数据表中
table.Rows.Add(newRow);
}
}
}
}
return(table);
}
/// <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>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
XSSFWorkbook XSSFworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
XSSFworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)XSSFworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)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);
}
public ActionResult UploadOrder(IEnumerable <HttpPostedFileBase> UploadFile)
{
ViewBag.PageName = "批次上傳";
// 儲存檔案
foreach (var file in UploadFile)
{
if (file == null)
{
ViewBag.Error = "請選擇檔案!";
return(View());
}
if (Path.GetExtension(file.FileName) != ".xlsx")
{
ViewBag.Error = "副檔名錯誤，請下載樣板並上傳資料!";
return(View());
}
MemoryStream ms = new MemoryStream();
file.InputStream.CopyTo(ms);
string FilePath = @"D:\";
file.InputStream.Position = 0;
string FileName = DateTime.Now.ToString("yyyyMMddhhmmssfff") + Path.GetExtension(file.FileName);
file.SaveAs(FilePath + FileName);
ms.Dispose();
ms.Close();
// NPOI讀取
XSSFWorkbook wb;
using (FileStream fs = new FileStream(FilePath + FileName, FileMode.Open, FileAccess.ReadWrite))
{
wb = new XSSFWorkbook(fs);
XSSFSheet MySheet;
MySheet = (XSSFSheet)wb.GetSheetAt(0);
// 迴圈讀每筆資料，從1開始(跳過標題列)
for (int i = 1; i <= MySheet.LastRowNum; i++)
{
XSSFRow Row = (XSSFRow)MySheet.GetRow(i);
// 讀取每欄資料
for (int k = 0; i < Row.Cells.Count; i++)
{
string MyTemp = Row.GetCell(k).ToString();
}
}
}
}
return(View());
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
else
{
int prevNum = row.LastCellNum;
if (prevNum != -1)
{
_cellNum = row.GetCell(prevNum - 1).ColumnIndex + 1;
}
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
private void assertCellsWithMissingR(XSSFRow row)
{
XSSFCell a1 = (XSSFCell)row.GetCell(0);
Assert.IsNotNull(a1);
XSSFCell a2 = (XSSFCell)row.GetCell(1);
Assert.IsNotNull(a2);
XSSFCell a5 = (XSSFCell)row.GetCell(4);
Assert.IsNotNull(a5);
XSSFCell a6 = (XSSFCell)row.GetCell(5);
Assert.IsNotNull(a6);
Assert.AreEqual(6, row.LastCellNum);
Assert.AreEqual(4, row.PhysicalNumberOfCells);
Assert.AreEqual(a1.StringCellValue, "A1");
Assert.AreEqual(a2.StringCellValue, "B1");
Assert.AreEqual(a5.StringCellValue, "E1");
Assert.AreEqual(a6.StringCellValue, "F1");
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
Assert.AreEqual(a1.GetReference(), "A1");
Assert.AreEqual(a2.GetReference(), "B1");
Assert.AreEqual(a5.GetReference(), "E1");
Assert.AreEqual(a6.GetReference(), "F1");
}
