C#导入导出Excel

 class ReadWriteExcel
    {
        #region 读取Excel
        /// <summary>
        /// 读取Excel文件到DataTable
        /// </summary>
        /// <param name="filepath">Excel文件路径</param>
        /// <param name="sheetindex">需要读取的Sheet 例如要Sheet1$,则此处填写 1</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcel(string filepath, int sheetindex)
        {
            DataTable dt_Result = new DataTable(); ;
            string filetype = filepath.Substring(filepath.Length - 4);
            string strconn = "";
            switch (filetype)
            {
                case ".xls":
                    {
                        strconn = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                        break;
                    }
                case "xlsx":
                    {
                        strconn = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + filepath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                        break;
                    }
                default:
                    {
                        strconn = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                        break;
                    }
            }
            try
            {

                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strconn))
                {
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    conn.Close();
                    string sql = "";
                    if (sheetindex != 1)
                    {
                        sql = "SELECT  * FROM  [Sheet'" + sheetindex + "'$] ";
                    }
                    else
                    {
                        sql = "SELECT  * FROM  [Sheet1$] ";
                    }
                    new System.Data.OleDb.OleDbDataAdapter(new System.Data.OleDb.OleDbCommand(sql, conn)).Fill(dt_Result);
                }
                return dt_Result;
            }
            catch (Exception ex)
            {
                return dt_Result;

                throw new Exception("读取Excel文件失败\n" + ex.ToString());
            }
        }

        /// <summary>
        /// 读取Excel文件到DataTable
        /// </summary>
        /// <param name="filename">Excel文件路径</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcel(string filename)
        {
            return ReadExcel(filename, 1);
        }
        #endregion

        #region 写入Excel

        public static void WriteExcel(DataTable dt)
        {
            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx|Excel文件|*.xls";
            saveDialog.FileName = "Export" + System.DateTime.Now.ToString("yyyyMMddhhmmss");
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            //写入字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
            }
            //写入数值

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//强行销毁
        }
        #endregion

    }

winform基本上够用了吧

 

posted @ 2013-09-12 10:33  水目之痕  阅读(250)  评论(0)    收藏  举报