用户投稿开发环境建置
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(); }} }refhttps://www.itread01.com/content/1545079919.htmlhttps://blog.darkthread.net/blog/npoi-forceformularecalculation/logNPOI 写入 2007把excel弄坏了NPOI 写入2003 就不会坏NPOI 2003 用dt读写,公式变成文字 NPOI 2003 用fs读写,公式不会变文字了,但如果来源的值改了,公式却仍是未改前的加总NPOI 2003 用fs读写,储存前,Sheet.ForceFormulaRecalculation 就会更新加总了.感谢黑大https://blog.darkthread.net/blog/npoi-forceformularecalculation/
微信扫一扫打赏
支付宝扫一扫打赏
