iT邦幫忙

4

C# WriteExcel & ReadExcel (NPOI/OLE)

readexcel npoi ole
圓頭人 2019-06-27 14:27:41 ‧ 32208 瀏覽

  • 分享至 

  • xImage
    •  

開發環境建置

VS2013
專案/管理nuget件 搜尋NPOI,然後安裝它
安裝好後,就會看到方案裡的參考多了
NPOI
NPOI.OOXML
NPOI.OpenXml4Net
NPOI.OpenXmlFormats

NPOI 改EXCEL內容

資料串透由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

伺服器建置

要裝什麼嗎? (待確認)

其它讀取方式-NPOI

讀出來,放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

其它讀取方法-ole

優: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;
            }
        }
    }

OLE (VS2003有些function要改一下)

```

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/

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22183 篇
完賽人數
597 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙