C# 操作EXCEL文件

来源：互联网发布：销售订单软件编辑：程序博客网时间：2024/06/06 00:26

using System.Data.OleDb;using Microsoft.Office.Interop.Excel;//读取 public DataSet ExcelToDS(string Path)        {            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;";            OleDbConnection conn = new OleDbConnection(strConn);            conn.Open();            string strExcel = "";            OleDbDataAdapter myCommand = null;            DataSet ds = null;            DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);            string tableName = schemaTable.Rows[0][2].ToString().Trim();            string tableName2 = schemaTable.Rows[1][2].ToString().Trim();            strExcel = "select * from [" + tableName + "]";            myCommand = new OleDbDataAdapter(strExcel, strConn);            ds = new DataSet();            myCommand.Fill(ds, "table1");            return ds;        }         //写入        public void DSToExcel(string Path,DataSet oldds)         {             //先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构             string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;";             OleDbConnection myConn = new OleDbConnection(strCon) ;            myConn.Open ( ) ;            DataTable schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);            string tableName = schemaTable.Rows[0][2].ToString().Trim();            string strCom = "select * from [" + tableName + "]";             OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ;            System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);             //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。             builder.QuotePrefix="[";     //获取insert语句中保留字符（起始位置）             builder.QuoteSuffix="]"; //获取insert语句中保留字符（结束位置）             DataSet newds=new DataSet();             myCommand.Fill(newds ,"Table1");             for(int i=0;i<oldds.Tables[0].Rows.Count;i++)             {             //在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。                //  在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added                 DataRow nrow = newds.Tables["Table1"].NewRow();                 for(int j=0;j<newds.Tables[0].Columns.Count;j++)                 {                     nrow[j]=oldds.Tables[0].Rows[i][j];                 }                     newds.Tables["Table1"].Rows.Add(nrow);             }             myCommand.Update(newds,"Table1");             myConn.Close();         }

//修改excel表内容 public static void SaveDataToExcel(string filePath, int col, string[] number, string str)        {            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象            object missing = System.Reflection.Missing.Value;//获取缺少的object类型值            //打开指定的Excel文件             excel.Application.DisplayAlerts = false;//不显示提示对话框              Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(filePath,            Type.Missing, Type.Missing, Type.Missing, Type.Missing,            Type.Missing, Type.Missing, Type.Missing, Type.Missing,            Type.Missing, Type.Missing, Type.Missing, Type.Missing,            Type.Missing, Type.Missing);//打开Excel             Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;//实例表格            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[1];//第一个表格            foreach (string rows in number)            {                int row = int.Parse(rows);                worksheet.Cells[row, col].value = str;                Console.WriteLine(worksheet.Cells[row, col].value + "：" + str);            }            workbook.Save();//保存工作表            workbook.Close(false, missing, missing);//关闭工作表            sheets = null;            workbook = null;            excel.Quit();            excel = null;            GC.Collect();        }                /// <summary>        /// 加载sheet        /// </summary>        /// <param name="Path"></param>        /// <returns></returns>        public List<string> LoadSheet(string Path)        {            List<string> list = new List<string>();            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;";            OleDbConnection conn = new OleDbConnection(strConn);            conn.Open();            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);            string tableName = schemaTable.Rows[0][2].ToString().Trim();            string tableName2 = schemaTable.Rows[1][2].ToString().Trim();            for (int i = 0; i < schemaTable.Rows.Count; i++)            {                list.Add(schemaTable.Rows[i][2].ToString().Trim());            }            conn.Close();            return list;        }

0 0