excel操作类
来源：互联网 发布：视频放大的软件 编辑：程序博客网 时间：2024/06/17 22:25
public class ExcelOper { private Application _excelApp = null; public ExcelOper() { } #region 读取Excel的内容 /// <summary> /// 获取Excel的内容 /// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable /// 的列标题 /// </summary> /// <returns></returns> public static System.Data.DataTable GetExcelTable(string excelPath, string sheetName) { try { //打开Excel连接 string connString = ""; OleDbConnection conn = OpenExcelEx(excelPath, out connString); //读取Excel string strSheetName = sheetName + "$"; string strSql = "select * from [" + strSheetName + "]"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); DataSet dtSet = new DataSet(); dataAdapter.Fill(dtSet); System.Data.DataTable dtTable = dtSet.Tables[0]; dataAdapter.Dispose(); return dtTable; } catch (Exception ex) { LogHelper.Error.Append(ex); return null; } } /// <summary> /// 获取Excel的内容 /// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable /// 的列标题 /// </summary> /// <returns></returns> public static System.Data.DataTable GetExcelTable(string excelPath) { return GetExcelTable(excelPath, false); } /// <summary> /// 获取Excel的内容 /// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable /// 的列标题 /// </summary> /// <param name="excelPath"></param> /// <param name="HasColumn">是否把一行做表头</param> /// <returns></returns> public static System.Data.DataTable GetExcelTable(string excelPath, bool HasColumn) { try { //打开Excel连接 string connString = ""; OleDbConnection conn = null; if (HasColumn == true) { conn = OpenExcelEx(excelPath, out connString); //连接 } else { conn = OpenExcel(excelPath, out connString); //连接 } //读取Excel //string strSheetName = GetFirstSheetName(connString);//第一页如果改成其他名称，eg “名称”，sheet2，sheet3，则第一页为sheet2，还需要判断是否有内容 List<string> lstName = GetFirstSheetNames(connString);//对所有图层遍历看是否有内容 System.Data.DataTable dtTable = null; foreach (string strSheetName in lstName) { string strSql = "select * from [" + strSheetName + "]"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询 DataSet dtSet = new DataSet(); dataAdapter.Fill(dtSet); System.Data.DataTable table = dtSet.Tables[0]; dataAdapter.Dispose(); if (table.Columns.Count > 1) { dtTable = table; break; } } conn.Close(); //进程就不会被占用 return dtTable; } catch (Exception ex) { LogHelper.Error.Append(ex); return null; } } /// <summary> /// 获取Excel的内容 /// 备注：读取EXCEL从单元格(N,M)读取到单元格(X,Y)，N,M,X,Y由strFilter指定,如：A3:C65535 /// </summary> /// <param name="excelPath"></param> /// <param name="HasColumn">是否把第N行做为列名（表头）</param> /// <returns></returns> public static System.Data.DataTable GetExcelTable(string excelPath, string strFilter, bool HasColumn) { try { //打开Excel连接 string connString = ""; OleDbConnection conn = null; if (HasColumn == true) { conn = OpenExcelEx(excelPath, out connString); //连接 } else { conn = OpenExcel(excelPath, out connString); //连接 } //读取Excel string strSheetName = GetFirstSheetName(connString) + strFilter; string strSql = "select * from [" + strSheetName + "]"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询 DataSet dtSet = new DataSet(); dataAdapter.Fill(dtSet); System.Data.DataTable dtTable = dtSet.Tables[0]; dataAdapter.Dispose(); conn.Close(); //cql 进程就不会被占用 return dtTable; } catch (Exception ex) { LogHelper.Error.Append(ex); return null; } } /// <summary> /// 表单以数值开头的处理方式，湖南项目 /// </summary> /// <param name="excelPath"></param> /// <param name="HasColumn"></param> /// <returns></returns> public static System.Data.DataTable GetNumExcelTable(string excelPath,int line, bool HasColumn) { try { //打开Excel连接 string connString = ""; OleDbConnection conn = null; if (HasColumn == true) { conn = OpenExcelEx(excelPath, out connString); //连接 } else { conn = OpenExcel(excelPath, out connString); //连接 } //读取Excel List<string> _strSheetName = GetFirstSheetNames(connString); string strSheetName = SelectSheet(_strSheetName); string strSql = "select * from [" + strSheetName + "]"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询 DataSet dtSet = new DataSet(); dataAdapter.Fill(dtSet); System.Data.DataTable dtTable = dtSet.Tables[0]; dataAdapter.Dispose(); conn.Close(); //进程就不会被占用 ClearNullTable(dtTable); System.Data.DataTable newDt = dtTable.Clone(); DataRow row = null; for (int i = line-1; i < dtTable.Rows.Count; i++) { newDt.Rows.Add(dtTable.Rows[i].ItemArray); } return newDt; } catch (Exception ex) { LogHelper.Error.Append(ex); return null; } } //去除空行空列 public static void ClearNullTable(System.Data.DataTable table) { bool bNull = true; for (int i = 0; i < table.Rows.Count; i++) { bNull = true; for (int j = 0; j < table.Columns.Count; j++) { if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString())) { bNull = false; } } if (bNull) { table.Rows.RemoveAt(i); i--; } } for (int j = 0; j < table.Columns.Count; j++) { bNull = true; for (int i = 0; i < table.Rows.Count; i++) { if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString())) { bNull = false; } } if (bNull) { table.Columns.RemoveAt(j); j--; } } } /// <summary> /// 去除列全部为空的行 /// </summary> /// <param name="table"></param> public static void ClearNullRows(System.Data.DataTable table) { bool bNull = true; for (int i = 0; i < table.Rows.Count; i++) { bNull = true; for (int j = 0; j < table.Columns.Count; j++) { if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString())) { bNull = false; } } if (bNull) { table.Rows.RemoveAt(i); i--; } } } /// <summary> /// 获取第一个Sheet页的名称 /// </summary> /// <param name="connectionString"></param> /// <returns></returns> private static string GetFirstSheetName(string connectionString) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string tableName = schemaTable.Rows[0][2].ToString().Trim(); connection.Close(); return tableName; } } private static List<string> GetFirstSheetNames(string connectionString) { List<string> lstName = new List<string>(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in schemaTable.Rows) { string tableName = row[2].ToString().Trim(); lstName.Add(tableName); } connection.Close();// } return lstName; } #region 直接打开读取Excel获取第一个页 //private static string GetFirstSheetNameEx(string filePath) //{ // Application xApp = null; // Workbook xBook = null; // try // { // xApp = new ApplicationClass(); // xBook = xApp.Application.Workbooks.Open(filePath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//新建文件的代码 // string name = ((Worksheet)xBook.Sheets[0]).Name; // return name; // } // catch(Exception ex) // { // LogHelper.Error.Append(ex); // } // finally // { // if (xBook != null) // { // xBook.Close(Type.Missing, Type.Missing, Type.Missing); // xBook = null; // xApp.Workbooks.Close(); // xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 // xApp = null; // } // } // return ""; //} #endregion #endregion /// <summary> /// 判断文件是否正在使用 /// </summary> /// <param name="fileName"></param> /// <returns></returns> public static bool IsFileInUse(string fileName) { bool inUse = true; if (File.Exists(fileName)) { FileStream fs = null; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.None); inUse = false; } catch (Exception ex) { LogHelper.Error.Append(ex); } finally { if (fs != null) { fs.Close(); } } return inUse; //true表示正在使用,false没有使用 } else { return false; //文件不存在则一定没有被使用 } } public static System.Data.DataTable GetExcelTable(string excelPath, string strFilter, string strSheetName, bool HasColumn) { try { //打开Excel连接 string connString = ""; OleDbConnection conn = null; if (HasColumn == true) { conn = OpenExcelEx(excelPath, out connString); //连接 } else { conn = OpenExcel(excelPath, out connString); //连接 } //读取Excel strSheetName = strSheetName + "$" + strFilter; string strSql = "select * from [" + strSheetName + "]"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn); //查询 DataSet dtSet = new DataSet(); dataAdapter.Fill(dtSet); System.Data.DataTable dtTable = dtSet.Tables[0]; dataAdapter.Dispose(); conn.Close(); //进程就不会被占用 return dtTable; } catch (Exception ex) { LogHelper.Error.Append(ex); return null; } } #region 写Excel /// <summary> /// 创建Excel进程 /// </summary> /// <returns></returns> private Application GetExcelApp() { Application excelApp = new Application(); excelApp.Application.Workbooks.Add(true); _excelApp = excelApp; return excelApp; } /// <summary> /// 把DataTable的内容写入Excel /// </summary> /// <param name="strExcelPath">excel文件的路径</param> /// <param name="htDataTable">key：sheetName，value：DataTable</param> /// <returns></returns> public bool WriteExcel(string strExcelPath, Hashtable htDataTable) { if (htDataTable == null || htDataTable.Count == 0) { return false; } bool writeRst = false; try { if (_excelApp == null) { GetExcelApp(); } //依次写入Sheet页 int countNum = 1; foreach (DictionaryEntry de in htDataTable) { string sheetName = de.Key.ToString(); System.Data.DataTable dtTable = (System.Data.DataTable)de.Value; Worksheet excelSheet = null; if (countNum == 1) { excelSheet = (Worksheet)_excelApp.Worksheets[countNum]; } else { excelSheet = (Worksheet)_excelApp.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } excelSheet.Name = sheetName; bool sheetRst = dtTable.Columns.Contains("数据路径") ? writeSheet(excelSheet, dtTable, dtTable.Columns["数据路径"].Ordinal) : writeSheet(excelSheet, dtTable); if (!sheetRst) { throw new Exception(sheetName + "创建失败！"); } countNum++; } //保存 _excelApp.Visible = false; _excelApp.DisplayAlerts = false; _excelApp.AlertBeforeOverwriting = false; _excelApp.ActiveWorkbook.SaveAs(strExcelPath, Type.Missing, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); writeRst = true; } catch (Exception ex) { LogHelper.Error.Append(ex); writeRst = false; } finally { //关闭Excel进程 object missing = System.Reflection.Missing.Value; _excelApp.ActiveWorkbook.Close(missing, missing, missing); _excelApp.Quit(); _excelApp = null; //垃圾回收 GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } return writeRst; } [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); /// <summary> /// 销毁Excel /// </summary> /// <param name="excelApp"></param> /// <param name="excelWorkbook"></param> /// <param name="excelWorksheet"></param> private static void DisposeExcelCOMObject(ref Microsoft.Office.Interop.Excel.Application excelApp, Microsoft.Office.Interop.Excel.Workbook excelWorkbook, Microsoft.Office.Interop.Excel.Worksheet excelWorksheet) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet); excelWorksheet = null; excelWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); excelWorkbook = null; excelApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 IntPtr t = new IntPtr(excelApp.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); if (null != excelApp) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; } System.GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } /// <summary> /// 写Sheet页 /// </summary> /// <param name="excelSheet"></param> /// <param name="dtTable"></param> /// <returns></returns> private bool writeSheet(Worksheet excelSheet, System.Data.DataTable dtTable, int Links) { if (excelSheet == null || dtTable == null) { return false; } //列名 for (int i = 0; i < dtTable.Columns.Count; i++) { DataColumn dtColumn = dtTable.Columns[i]; string caption = dtColumn.Caption; excelSheet.Cells[1, i + 1] = caption; } //写入值 for (int i = 0; i < dtTable.Rows.Count; i++) { for (int j = 0; j < dtTable.Columns.Count; j++) { object objValue = dtTable.Rows[i][j]; excelSheet.Cells[2 + i, j + 1] = objValue; if (Links.Equals(j)) excelSheet.Hyperlinks.Add(excelSheet.Cells[2 + i, j + 1], objValue.ToString(), Type.Missing, Type.Missing, Type.Missing); } } excelSheet.Columns.AutoFit(); return true; } private bool writeSheet(Worksheet excelSheet, System.Data.DataTable dtTable) { return writeSheet(excelSheet, dtTable, -1); } #endregion #region 打开Excel连接 ADO.Net /// <summary> /// 打开Excel文件,把一行做表头 /// 解决03版本和07版本Excel的访问驱动不兼容得问题 /// </summary> /// <param name="excelPath">Excel文件全路径</param> /// <param name="strConn">连接字符串</param> /// <returns></returns> public static OleDbConnection OpenExcelEx(string excelPath, out string strConn) { OleDbConnection conn = null; //先用03版Excel连接方式 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; if (!OpenExcelConnection(strConn, out conn)) { //在尝试用07的连接方式 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\""; if (!OpenExcelConnection(strConn, out conn)) return null; } return conn; } /// <summary> /// 打开Excel文件 /// 解决03版本和07版本Excel的访问驱动不兼容得问题 /// </summary> /// <param name="excelPath">Excel文件全路径</param> /// <param name="strConn">连接字符串</param> /// <returns></returns> public static OleDbConnection OpenExcel(string excelPath, out string strConn) { OleDbConnection conn = null; //先用03版Excel连接方式 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\""; if (!OpenExcelConnection(strConn, out conn)) { //在尝试用07的连接方式 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\""; if (!OpenExcelConnection(strConn, out conn)) return null; } return conn; } /// <summary> /// 打开Excel文件 /// </summary> /// <param name="connectString">连接字符串</param> /// <param name="conn">连接信息</param> /// <returns></returns> private static bool OpenExcelConnection(string connectString, out OleDbConnection conn) { conn = new OleDbConnection(connectString); try { conn.Open(); return true; } catch (Exception ex) { LogHelper.Error.Append(ex); return false; } } #endregion /// <summary> /// Excel中插入值，保存，数值传输 /// </summary> /// <param name="excelpath">保存路径</param> /// <param name="sourcepath">excel模板路径</param> /// <param name="insertTable"></param> /// <returns></returns> public static bool InsertExcelTable(string excelpath, string sourcepath, System.Data.DataTable insertTable, ExcelStatPara excelPar) { Application excelapp = new ApplicationClass(); Workbook mybook = null; try { #region 不进行提示是否以只读方式打开 //mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); #endregion Worksheet mysheet = null; for (int i = 1; i <= mybook.Worksheets.Count; i++) { mysheet = (Worksheet)mybook.Worksheets[i]; if (mysheet.Name == excelPar.SheetName) break; } bool bPicture = false; if (string.IsNullOrEmpty(excelPar.PicturePath) == false && File.Exists(excelPar.PicturePath))//存在图片 { bPicture = true; } //mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[3]; //mysheet.Columns.AutoFit(); //excelapp.Cells[excelPar.DateX, excelPar.DateY] = excelPar.StatDate; if (excelPar.StatDate != null && string.IsNullOrEmpty(excelPar.StatDate) == false) { mysheet.Cells[excelPar.DateX, excelPar.DateY] = excelPar.StatDate; } int startLine = excelPar.StartLine; Range range = null; for (int i = startLine; i < insertTable.Rows.Count + startLine; i++) //第一列日期 { if (bPicture == true) { range = null; range = (Range)mysheet.Rows[i, Missing.Value]; range.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);//插入空行 } for (int j = 1; j < insertTable.Columns.Count + 1; j++) { //excelapp.Cells[i, j] = insertTable.Rows[i - startLine][j - 1].ToString();//加行了用这个无效 mysheet.Cells[i, j] = insertTable.Rows[i - startLine][j - 1].ToString(); } } if (bPicture == true) { mysheet.Select(Missing.Value); range = null; string position = excelPar.PicPosition; position = position.Substring(0, 1) + (Convert.ToInt32(position.Substring(1)) + insertTable.Rows.Count).ToString(); range = mysheet.get_Range(position, Missing.Value); range.Select();//不选插入图片会乱，先mysheet.Select(),要不会出错 Pictures pics = (Pictures)mysheet.Pictures(Missing.Value); pics.Insert(excelPar.PicturePath, Missing.Value); } mybook.SaveCopyAs(excelpath); mybook.Close(false, Missing.Value, false); excelapp.DisplayAlerts = false;//很重要，要不删除sheet不成功 //删除其他标签 mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= mybook.Sheets.Count; i++) { mysheet = (Worksheet)mybook.Worksheets[i]; if (mysheet.Name != excelPar.SheetName) { mysheet.Delete(); i--; } } excelapp.DisplayAlerts = true;// mybook.Save(); mybook.Close(false, Missing.Value, false); excelapp.Workbooks.Close(); excelapp.Quit(); return true; } catch (Exception ex) { LogHelper.Error.Append(ex); return false; } //finally //{ // if (mybook != null) // mybook.Close(false, Missing.Value, false); // excelapp.Workbooks.Close(); // excelapp.Quit(); //} } /// <summary> /// 根据模板导出 /// </summary> /// <param name="excelpath">文件输出全路径</param> /// <param name="sourcepath">模板文件全路径</param> /// <param name="insertTable">需要插入的dataTable</param> /// <param name="excelPar">关于excel控制参数，如：从哪一个开始填充数据，sheet的名称</param> /// <returns>wcj</returns> public static bool InsertExcelTable1(string excelpath, string sourcepath, System.Data.DataTable insertTable, ExcelStatPara excelPar) { Application excelapp = new ApplicationClass(); Workbook mybook = null; try { mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Worksheet mysheet = null; for (int i = 1; i <= mybook.Worksheets.Count; i++) { mysheet = (Worksheet)mybook.Worksheets[i]; if (mysheet.Name == excelPar.SheetName) break; } int startLine = excelPar.StartLine; Range range = null; for (int i = startLine; i < insertTable.Rows.Count + startLine; i++) //从第6行开始 { for (int j = 1; j < insertTable.Columns.Count - 1; j++) //+1 最后的两个字段不在excel中显示 { mysheet.Cells[i, j] = insertTable.Rows[i - startLine][j].ToString(); } } mybook.SaveCopyAs(excelpath); mybook.Close(false, Missing.Value, false); excelapp.DisplayAlerts = false;//很重要，要不删除sheet不成功 //删除其他sheet标签 mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); for (int i = 1; i <= mybook.Sheets.Count; i++) { mysheet = (Worksheet)mybook.Worksheets[i]; if (mysheet.Name != excelPar.SheetName) { mysheet.Delete(); i--; } } excelapp.DisplayAlerts = true;// mybook.Save(); mybook.Close(false, Missing.Value, false); excelapp.Workbooks.Close(); excelapp.Quit(); return true; } catch (Exception ex) { LogHelper.Error.Append(ex); return false; } } /// <summary> /// Excel中插入值，保存，数值传输 /// </summary> /// <param name="excelpath">保存路径</param> /// <param name="sourcepath">excel模板路径</param> /// <param name="insertTable"></param> /// <returns></returns> public bool InsertExcelTable(string excelpath, string sourcepath, System.Data.DataTable insertTable) { try { Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook mybook; #region 为了解决在进行汇总表导入过程中，不进行提示是否以只读方式打开,赖鸿祥 //mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); #endregion Microsoft.Office.Interop.Excel.Worksheet mysheet; mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]; mysheet.Columns.AutoFit(); int sum = 0; double dsum = 0.0; excelapp.Cells[4, 1] = "合计"; for (int k = 5; k <= insertTable.Rows.Count + 4; k++) //第一列日期 { excelapp.Cells[k, 1] = insertTable.Rows[k - 5][0].ToString(); } for (int j = 2; j <= mysheet.UsedRange.Columns.Count; j++) //数值 { sum = 0; dsum = 0.0; for (int i = 5; i <= insertTable.Rows.Count + 4; i++) { if (j <= 9) { sum += int.Parse(insertTable.Rows[i - 5][j - 1].ToString()); } else { dsum += double.Parse(insertTable.Rows[i - 5][j - 1].ToString()); } excelapp.Cells[i, j] = insertTable.Rows[i - 5][j - 1].ToString(); } if (j <= 9) { excelapp.Cells[4, j] = sum; } else { excelapp.Cells[4, j] = dsum; } } mybook.SaveCopyAs(excelpath); mybook.Close(false, Missing.Value, false); excelapp.Workbooks.Close(); excelapp.Quit(); return true; } catch (Exception ex) { LogHelper.Error.Append(ex); return false; } } /// <summary> /// 插入图片 /// </summary> /// <param name="excelpath"></param> /// <param name="excelPar"></param> /// <returns></returns> public static bool InsertPicture(string excelpath, ExcelStatPara excelPar) { Application excelapp = new ApplicationClass(); Workbook mybook; mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Worksheet mysheet = null; for (int i = 1; i <= mybook.Worksheets.Count; i++) { mysheet = (Worksheet)mybook.Worksheets[i]; if (mysheet.Name == excelPar.SheetName) break; } Range range = mysheet.get_Range(excelPar.PicPosition, Missing.Value); range.Select(); Pictures pics = (Pictures)mysheet.Pictures(Missing.Value); pics.Insert(excelPar.PicturePath, Missing.Value); mybook.Save(); mybook.Close(false, Missing.Value, false); excelapp.Workbooks.Close(); excelapp.Quit(); return true; } public static string SelectSheet(List<string> _strSheetName) { DevExpress.XtraEditors.XtraForm Form = new DevExpress.XtraEditors.XtraForm(); DevExpress.XtraEditors.ComboBoxEdit cmbSelect = new DevExpress.XtraEditors.ComboBoxEdit(); cmbSelect.Location = new System.Drawing.Point(29, 28); cmbSelect.Size = new System.Drawing.Size(223, 21); DevExpress.XtraEditors.BaseButton btnOK = new DevExpress.XtraEditors.BaseButton(); btnOK.Location = new System.Drawing.Point(205, 56); btnOK.Size = new System.Drawing.Size(47, 23); btnOK.Text = "选择"; btnOK.Click += new EventHandler(delegate(object sender, EventArgs e) { Form.Close(); }); Form.AutoScaleDimensions = new System.Drawing.SizeF(7F, 14F); Form.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; Form.ClientSize = new System.Drawing.Size(284, 90); Form.Controls.Add(cmbSelect); Form.Controls.Add(btnOK); Form.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle; Form.MaximizeBox = false; Form.MinimizeBox = false; Form.ShowIcon = false; Form.ShowInTaskbar = false; Form.ControlBox = false; Form.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; Form.Text = "选择Sheet页"; Form.TopMost = true; cmbSelect.Properties.Items.AddRange(_strSheetName); Form.ShowDialog(); return cmbSelect.SelectedItem.ToString(); } }/// <summary> /// Excel结构类 /// </summary> public class ExcelStatPara { public ExcelStatPara() { } string _sheetName = ""; //Sheet名称 public string SheetName { get { return _sheetName; } set { _sheetName = value; } } int _dateX;//日期位置第几行 ：I3为(3,9) public int DateX { set { _dateX = value; } get { return _dateX; } } int _dateY;//日期位置第几列 public int DateY { set { _dateY = value; } get { return _dateY; } } int _startLine; //表格内容开始行 public int StartLine { set { _startLine = value; } get { return _startLine; } } string _title = "";//标题 public string Title { set { _title = value; } get { return _title; } } string _statDate = "";//日期 public string StatDate { set { _statDate = value; } get { return _statDate; } } string _strFilter = "";//表头范围 eg"A5:J6"; public string StrFilter { set { _strFilter = value; } get { return _strFilter; } } System.Data.DataTable _resultTable = null;//查询结果 public System.Data.DataTable ResultTable { set { _resultTable = value; } get { return _resultTable; } } string _picturePath = ""; //有图片的话 存储位置 public string PicturePath { get { return _picturePath; } set { _picturePath = value; } } string _picPosition = "";//图片位置，eg:A2 public string PicPosition { get { return _picPosition; } set { _picPosition = value; } } } 0 0
- php操作excel类
- Excel 操作类
- Excel操作类
- Excel文件操作类
- Excel文件操作类
- Excel操作类C#
- C#Excel操作类
- Excel操作基类
- C# Excel操作类
- Qt操作excel类
- Excel操作类
- .net excel操作类
- Excel操作类
- asp操作Excel类
- Excel操作类备份
- Excel操作类备份
- Excel操作类备份
- Excel操作类备份
- 【PHP】编译安装 PHP5.6.13遇到问题以及解决方案
- checkbox全选和取消全选
- 说说Makefile那些事儿
- 有名管道
- HDU 5441 Travel(最短路径变形）
- excel操作类
- POJ 3614 Sun Bathing (贪心)
- leetcode_Move Zeroes
- TCP协议疑难杂症全景解析
- 使用Jedis连接集群Redis(支持Redis复杂操作)
- 关于编译报错“dereferencing pointer to incomplete type...
- Windows传感器开发之请求用户许可
- nasm : test eflags ZF
- 在美做开发多年，写给国内iphone新手，转自http://bbs.feng.com/read-htm-tid-768284.html
