VS2013
專案/管理nuget件 搜尋NPOI,然後安裝它
安裝好後,就會看到方案裡的參考多了
NPOI
NPOI.OOXML
NPOI.OpenXml4Net
NPOI.OpenXmlFormats
資料串透由IWorkbook,來存取.
優點:
保留公式
EXCEL 2007可以用
EXCEL 2003可以用
圓頭人
NPOI writer = new NPOI();
writer.open(@"E:\test.xls");
writer.SetCell(1, 1, "3333",, NPOI.SS.UserModel.CellType.String);//rowcount,column count 都由0開始
writer.SaveClose(@"E:\test.xls");
using System.IO;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
public class NPOI
{
public ISheet sheet;
public FileStream fileStream;
public IWorkbook workbook = null; //新建IWorkbook對象
public void open(String fileName)
{
try
{
fileStream = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xls數據讀入workbook
}
sheet = workbook.GetSheetAt(0); //獲取第一個工作表
}
catch (Exception ex)
{
throw ex;
}
}
public void SetCell(int iRow, int iCol, string value, CellType _celltype)
{
HSSFRow row;
ICell cell = null;
if (sheet.GetRow(iRow)!=null)
row = (HSSFRow)sheet.GetRow(iRow);
else
{
//int ostatniWiersz = sheet.LastRowNum;
//row = (HSSFRow)sheet.CreateRow(ostatniWiersz + 1);//這樣會有問題
row = (HSSFRow)sheet.CreateRow(iRow);//add row
}
if (row != null)
{
cell= row.GetCell(iCol);
if ( cell == null)
{
cell = row.CreateCell(iCol, _celltype);//add cell
}
if (cell != null)
{
//cell.SetCellType ( _celltype);//reset type不用reset也可以
if (_celltype == CellType.Numeric)
cell.SetCellValue(double.Parse(value));
else if (_celltype == CellType.Formula)
cell.SetCellFormula(value);
else
cell.SetCellValue(value);
}
}
}
public void Clear(int ifromRow )
{
for (int i = (sheet.FirstRowNum + 0); i <= sheet.LastRowNum; i++) //-- 每一列做迴圈
{
HSSFRow row = (HSSFRow)sheet.GetRow(i); //--不包含 Excel表頭列的 "其他資料列"
if (row != null)
{
if (i >= ifromRow)
{
for (int j = row.FirstCellNum; j < row.LastCellNum; j++) //-- 每一個欄位做迴圈
{
SetCell(i, j, "", CellType.Blank);
//CellType.Blank);不會清空格式化的cell
//CellType.Formula);清空格式化的cell,也清不是格式化的
}
}
}
}
}
public void SaveClose(string path)
{
FileStream fs = null;
try
{
sheet.ForceFormulaRecalculation = true;//更新公式的值
fs = new FileStream(path, FileMode.Create);
workbook.Write(fs);
fs.Close();
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
throw ex;
}
finally
{
fileStream.Close();
}
}
ref
https://www.itread01.com/content/1537631886.html
要裝什麼嗎? (待確認)
讀出來,放datatable
寫進去,看datatable
缺:公式為sum(A1+A2),會變成值為sum(A1+A2)
缺:EXCEL 2003 OK,但如果是EXCEL2007,檔案就會毁壞.
System.Data.DataTable dt = NPOI .getexcel(@"E:\test.xls");
dt.Rows[1][1] = "3332";
dt.Rows[2][1] = "3332";
NPOI.DataTableToExcel(@"E:\test.xls", dt);
using System.IO;
using System.Text;
public class NPOI
{
/// <summary>
/// 讀Excel.
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static DataTable getexcel(String fileName)
{
DataTable dt = new DataTable();
try
{
IWorkbook workbook = null; //新建IWorkbook對象
FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xls數據讀入workbook
}
ISheet sheet = workbook.GetSheetAt(0); //獲取第一個工作表
IRow row;// = sheet.GetRow(0); //新建當前工作表行數據
// MessageBox.Show(sheet.LastRowNum.ToString());
row = sheet.GetRow(0); //row讀入頭部
if (row != null)
{
for (int m = 0; m < row.LastCellNum; m++) //表頭
{
string cellValue = row.GetCell(m).ToString(); //獲取i行j列數據
//Console.WriteLine(cellValue);
dt.Columns.Add(cellValue);
}
}
for (int i = 1; i <= sheet.LastRowNum; i++) //對工作表每一行
{
System.Data.DataRow dr = dt.NewRow();
row = sheet.GetRow(i); //row讀入第i行數據
if (row != null)
{
for (int j = 0; j < row.LastCellNum; j++) //對工作表每一列
{
string cellValue = row.GetCell(j).ToString(); //獲取i行j列數據
//Console.WriteLine(cellValue);
dr[j] = cellValue;
}
}
dt.Rows.Add(dr);
}
//Console.ReadLine();//這個有問題,讀不出來,反正它只是debug用的,所以取消它
fileStream.Close();
return dt;
}
catch(Exception ex)
{
throw ex;
}
return dt;
}
/// <summary>
/// 將datatable對象保存為Excel文件
/// 提供Excel保存路徑及datatable數據對象，成功返回真，失敗返回假。
/// 寫完EXCEL 2007檔案就壞了,但2003不會壞
/// </summary>
/// <param name="path"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static bool DataTableToExcel(String path, DataTable dt)
{
bool result = false;
IWorkbook workbook = null;
FileStream fs = null;
IRow row = null;
ISheet sheet = null;
ICell cell = null;
try
{
if (dt != null && dt.Rows.Count > 0)
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("Sheet0");//創建一個名稱為Sheet0的表
int rowCount = dt.Rows.Count;//行數
int columnCount = dt.Columns.Count;//列數
//設置列頭
row = sheet.CreateRow(0);//excel第一行設為列頭
for (int c = 0; c < columnCount; c++)
{
cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
}
//設置每行每列的單元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//excel第二行開始寫入數據
cell.SetCellValue( dt.Rows[i][j].ToString());
}
}
using (fs = File.OpenWrite(path))
{
workbook.Write(fs);//向打開的這個xls文件中寫入數據
result = true;
}
}
return result;
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
throw ex;
return false;
}
}
}
ref
https://www.itread01.com/content/1537631886.html
優:EXCEL2003 EXCEL2003都OK
缺:它用dt存進去,用dt讀出來,所以公式會不見.
或許可以直接update(待研究)
System.Data.DataTable dt = AccessExcel_ole.DBExcelToDataTable(@"E:\test.xlsx");
dt.Rows[1][1] = "3332";
dt.Rows[2][1] = "3332";
AccessExcel_ole.DTToExcel(@"E:\test.xlsx", dt);
List GetExcelTableName(string pathName
List getExcelFileInfo(string pathName)
DataTable DBExcelToDataTable(string pathName, string sheetName = "")
void DTToExcel(string Path, System.Data.DataTable dt)
public class AccessExcel_ole
{
public static List<string> GetExcelTableName(string pathName)
{
List<string> tableName = new List<string>();
if (File.Exists(pathName))
{
string strConn = string.Empty;
FileInfo file = new FileInfo(pathName);
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
{
conn.Open();
System.Data.DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
foreach (System.Data.DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//過濾無效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
strSheetTableName = strSheetTableName.Replace("'", ""); //可能會有 '1X$' 出現
strSheetTableName = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
tableName.Add(strSheetTableName);
}
}
}
}
return tableName;
}/// <summary>
/// 獲取EXCEL工作表的列名 返回list集合
/// </summary>
/// <param name="Path">Excel路徑名</param>
/// <returns></returns>
public static List<string> getExcelFileInfo(string pathName)
{
string strConn;
List<string> lstColumnName = new List<string>();
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("檔案不存在"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, null });
foreach (System.Data.DataRow drow in table.Rows)
{
string TableName = drow["Table_Name"].ToString();
if (TableName.Contains("$") && TableName.Replace("'", "").EndsWith("$"))
{
System.Data.DataTable tableColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
foreach (System.Data.DataRow drowColumns in tableColumns.Rows)
{
string ColumnName = drowColumns["Column_Name"].ToString();
lstColumnName.Add(ColumnName);
}
}
}
return lstColumnName;
} /// <summary>
/// OLEDB方式讀取Excel,這裡讀到的是值,不會讀到公式.
/// </summary>
/// <param name="pathName">Excel路徑</param>
/// <param name="sheetName">工作表名，預設讀取第一個有資料的工作表（至少有2列資料）</param>
/// <returns></returns>
public static System.Data.DataTable DBExcelToDataTable(string pathName, string sheetName = "")
{
System.Data.DataTable dt = new System.Data.DataTable();
string ConnectionString = string.Empty;
System.IO.FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("檔案不存在"); }
string extension = file.Extension;
switch (extension) // 連線字串
{
case ".xls":
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
case ".xlsx":
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";
break;
default:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);
try
{
con.Open();
if (sheetName != "") //若指定了工作表名
{ //讀Excel的過程中，發現dt末尾有些行是空的，所以在sql語句中加了Where 條件篩選符合要求的資料。OLEDB會自動生成列名F1,F2……Fn
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
try
{
apt.Fill(dt);
}
catch (Exception ex) { throw new Exception("該Excel檔案中未找到指定工作表名," + ex.Message); }
dt.TableName = sheetName;
}
else
{
//預設讀取第一個有資料的工作表
var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { });
if (tables.Rows.Count == 0)
{ throw new Exception("Excel必須包含一個表"); }
foreach (System.Data.DataRow row in tables.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//過濾無效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strSheetTableName, null });
if (tableColumns.Rows.Count < 2) //工作表列數
continue;
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
apt.Fill(dt);
dt.TableName = strSheetTableName.Replace("$", "").Replace("'", "");
break;
}
}
}
if (dt.Rows.Count < 2)
throw new Exception("表必須包含資料");
//重構欄位名
System.Data.DataRow headRow = dt.Rows[0];
foreach (System.Data.DataColumn c in dt.Columns)
{
string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim();
if (headValue.Length == 0)
{ throw new Exception("必須輸入列標題"); }
if (dt.Columns.Contains(headValue))
{ throw new Exception("不能用重複的列標題：" + headValue); }
c.ColumnName = headValue;
}
dt.Rows.RemoveAt(0);
return dt;
}
catch (Exception ee)
{ throw ee; }
finally
{ con.Close(); }
}
/// <summary>
/// 把值寫到excel
/// </summary>
/// <param name="Path">路徑</param>
/// <param name="dt">DataTable</param>
public static void DTToExcel(string Path, System.Data.DataTable dt)
{
string strCon = string.Empty;
FileInfo file = new FileInfo(Path);
string extension = file.Extension;
switch (extension)
{
case ".xls":
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";
//strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
//strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
break;
case ".xlsx":
//strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=Excel 12.0;";
//strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'"; //出現錯誤了
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0;'";
break;
default:
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
break;
}
try
{
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
System.Data.OleDb.OleDbCommand cmd;
try
{
cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", dt.TableName), con); //覆蓋檔案時可能會出現Table 'Sheet1' already exists.所以這裡先刪除了一下
cmd.ExecuteNonQuery();
}
catch { }
//建立表格欄位
strSQL.Append("CREATE TABLE ").Append("[" + dt.TableName + "]");
strSQL.Append("(");
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
//新增資料
for (int i = 0; i < dt.Rows.Count; i++)
{
strSQL.Clear();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strvalue.Append("'" + dt.Rows[i][j].ToString().Replace("'", "''") + "'");
if (j != dt.Columns.Count - 1)
{
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = strSQL.Append(" insert into [" + dt.TableName + "] values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch (Exception ex){
throw ex;
}
}
}
```
public class AccessExcel_ole
{
string strSQL="";
string strvalue="";
public static string GetExcelTableName(string pathName)
{
string tableName = "";
if (File.Exists(pathName))
{
string strConn = string.Empty;
FileInfo file = new FileInfo(pathName);
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
{
conn.Open();
System.Data.DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
foreach (System.Data.DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//過濾無效SheetName
if (strSheetTableName.IndexOf("$")!=-1 && strSheetTableName.Replace("'", "").EndsWith("$"))
{
strSheetTableName = strSheetTableName.Replace("'", ""); //可能會有 '1X$' 出現
strSheetTableName = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
return strSheetTableName;
}
}
}
}
return tableName;
}///
/// 獲取EXCEL工作表的列名 返回list集合
///
/// Excel路徑名
///
public static string getExcelFileInfo(string pathName)
{
string strConn;
string sheetName="";
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("檔案不存在"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, null });
foreach (System.Data.DataRow drow in table.Rows)
{
string TableName = drow["Table_Name"].ToString();
if (TableName.IndexOf("$")!=-1 && TableName.Replace("'", "").EndsWith("$"))
{
System.Data.DataTable tableColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
foreach (System.Data.DataRow drowColumns in tableColumns.Rows)
{
string ColumnName = drowColumns["Column_Name"].ToString();
return ColumnName;
}
}
}
return sheetName;
} /// <summary>
/// OLEDB方式讀取Excel,這裡讀到的是值,不會讀到公式.
/// </summary>
/// <param name="pathName">Excel路徑</param>
/// <param name="sheetName">工作表名，預設讀取第一個有資料的工作表（至少有2列資料）</param>
/// <returns></returns>
public static System.Data.DataTable DBExcelToDataTable(string pathName, string sheetName )
{
System.Data.DataTable dt = new System.Data.DataTable();
string ConnectionString = string.Empty;
System.IO.FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("檔案不存在"); }
string extension = file.Extension;
switch (extension) // 連線字串
{
case ".xls":
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
case ".xlsx":
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";
break;
default:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);
try
{
con.Open();
if (sheetName != "") //若指定了工作表名
{ //讀Excel的過程中，發現dt末尾有些行是空的，所以在sql語句中加了Where 條件篩選符合要求的資料。OLEDB會自動生成列名F1,F2……Fn
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
try
{
apt.Fill(dt);
}
catch (Exception ex) { throw new Exception("該Excel檔案中未找到指定工作表名," + ex.Message); }
dt.TableName = sheetName;
}
else
{
//預設讀取第一個有資料的工作表
System.Data.DataTable tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { });
if (tables.Rows.Count == 0)
{ throw new Exception("Excel必須包含一個表"); }
foreach (System.Data.DataRow row in tables.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//過濾無效SheetName
if (strSheetTableName.IndexOf("$")!=-1 && strSheetTableName.Replace("'", "").EndsWith("$"))
{
System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strSheetTableName, null });
if (tableColumns.Rows.Count < 2) //工作表列數
continue;
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
apt.Fill(dt);
dt.TableName = strSheetTableName.Replace("$", "").Replace("'", "");
break;
}
}
}
if (dt.Rows.Count < 2)
throw new Exception("表必須包含資料");
//重構欄位名
System.Data.DataRow headRow = dt.Rows[0];
foreach (System.Data.DataColumn c in dt.Columns)
{
string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim();
if (headValue.Length == 0)
{ throw new Exception("必須輸入列標題"); }
if (dt.Columns.Contains(headValue))
{ throw new Exception("不能用重複的列標題：" + headValue); }
c.ColumnName = headValue;
}
dt.Rows.RemoveAt(0);
return dt;
}
catch (Exception ee)
{ throw ee; }
finally
{ con.Close(); }
}
}
ref
https://www.itread01.com/content/1545079919.html
https://blog.darkthread.net/blog/npoi-forceformularecalculation/
log
NPOI 寫入 2007把excel弄壞了
NPOI 寫入2003 就不會壞
NPOI 2003 用dt讀寫,公式變成文字
NPOI 2003 用fs讀寫,公式不會變文字了,但如果來源的值改了,公式卻仍是未改前的加總
NPOI 2003 用fs讀寫,儲存前,Sheet.ForceFormulaRecalculation 就會更新加總了.感謝黑大 https://blog.darkthread.net/blog/npoi-forceformularecalculation/
iThome鐵人賽
看影片追技術