C#对Excel的读写操作

读取Excel，首先要添加Microsoft.Office.Interop.Excel 12.0 动态库

 

读取工作薄中的sheets

public ArrayList loadExcelSheets(String filename)
        {
            ArrayList sheets = new ArrayList();
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES';"))
            {
                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow dr in dt.Rows)
                {
                    sheets.Add(dr["TABLE_NAME"].ToString());
                }
            }
            return sheets;
        }

读取某个sheet中的内容

public System.Data.DataTable loadExcel(String filename, String sheet)
        {
            string strSource = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES';";
            OleDbConnection conn = new OleDbConnection(strSource);
            string sqlstring = @"SELECT * FROM [" + sheet + "]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(sqlstring, conn);
            DataSet da = new DataSet();
            try
            {
                adapter.Fill(da);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            System.Data.DataTable dt = da.Tables[0];
            return dt;
        }

 

写入excel

public void saveExcel(String excel, ArrayList data)
        {     
            //开始写入workbook
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible = false;  //false 表示程序进行时workbook不被可见地打开，true表示执行时excel被打开，并可视其写入过程。
            Workbook wBook = app.Workbooks.Add(true); //新建Workbook的方式
            //Workbook wBook = app.Workbooks.Open(conf + template, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);//打开Workbook的方式
            
            Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
            //写入数据
             writeDataToSheet(wSheet, data);
            
            dt = null; //清空Datatable
            //设置禁止弹出保存和覆盖的询问提示框
             app.DisplayAlerts = false;
            app.AlertBeforeOverwriting = false;
            try
            {
                wBook.SaveAs(excel, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.
                            Missing, false, false, XlSaveAsAccessMode.xlShared,
                            XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.
                            Missing, Type.Missing);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            wBook.Close(null);  //关闭workbook,防止程序关闭后文件继续被打开
            app.Quit();
            app = null;
            System.GC.Collect();
        }

 

一些单元格样式

Range range1 = (Range)wSheet.get_Range(wSheet.Cells[7, 2], wSheet.Cells[7 + size - 1, 2]);
                    Range range2 = (Range)wSheet.get_Range(wSheet.Cells[7, 3], wSheet.Cells[7 + size - 1, 3]);
                    Range range3 = (Range)wSheet.get_Range(wSheet.Cells[7 + size, 2], wSheet.Cells[7 + size, 4]);

                    range1.ColumnWidth = "10";
                    range1.Merge(null);
                    range1.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    range1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range2.ColumnWidth = "13";
                    range2.ColumnWidth = "13";
                    range3.Merge(null);
                    range3.HorizontalAlignment = XlHAlign.xlHAlignRight;