使用EPPLUS操作EXcel

原文:使用EPPLUS操作EXcel

1 下載Epplus最新版：http://epplus.codeplex.com/

2 在專案中新增引用 EEPULS.dll（測試環境Win7+V2010旗艦）

3  新增using引用

　　using OfficeOpenXml;
　　using OfficeOpenXml.Drawing;
　　using OfficeOpenXml.Drawing.Chart;
　　using OfficeOpenXml.Style;

4 read Excel檔案

        private void button2_Click(object sender, EventArgs e)
        {
            string pfilePath = "";
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "*.xls|*.xlsx";
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                pfilePath = ofd.FileName;
            }
            if (string.IsNullOrEmpty(pfilePath))
            {
                MessageBox.Show("沒有選中檔案");
                return;
            }

            FileInfo existingFile = new FileInfo(pfilePath);
            try
            {
                ExcelPackage package = new ExcelPackage(existingFile);
                int vSheetCount = package.Workbook.Worksheets.Count; //獲取總Sheet頁

                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//選定 指定頁

                int maxColumnNum = worksheet.Dimension.End.Column;//最大列
                int minColumnNum = worksheet.Dimension.Start.Column;//最小列

                int maxRowNum = worksheet.Dimension.End.Row;//最小行
                int minRowNum = worksheet.Dimension.Start.Row;//最大行

                DataTable vTable = new DataTable();
                DataColumn vC;
                for (int j = 1; j <= maxColumnNum; j++)
                {
                    vC = new DataColumn("A_" + j, typeof(string));
                    vTable.Columns.Add(vC);
                }
                if (maxRowNum > 200)
                {
                    maxRowNum = 200;
                }
                for (int n = 1; n <= maxRowNum; n++)
                {
                    DataRow vRow = vTable.NewRow();
                    for (int m = 1; m <= maxColumnNum; m++)
                    {
                        vRow[m - 1] = worksheet.Cells[n, m].Value;
                    }
                    vTable.Rows.Add(vRow);
                }
                this.dataGridView1.DataSource = vTable;
            }
            catch (Exception vErr)
            {
                MessageBox.Show(vErr.Message);
            }
        }

5 write Excel檔案

        private void button1_Click(object sender, EventArgs e)
        {
                 
            OfficeOpenXml.ExcelPackage ep = new OfficeOpenXml.ExcelPackage();
            OfficeOpenXml.ExcelWorkbook wb = ep.Workbook;
            OfficeOpenXml.ExcelWorksheet ws = wb.Worksheets.Add("我的工作表");
            //配置檔案屬性  
            wb.Properties.Category = "類別";
            wb.Properties.Author = "作者";
            wb.Properties.Comments = "備註";
            wb.Properties.Company = "公司";
            wb.Properties.Keywords = "關鍵字";
            wb.Properties.Manager = "管理者";
            wb.Properties.Status = "內容狀態";
            wb.Properties.Subject = "主題";
            wb.Properties.Title = "標題";
            wb.Properties.LastModifiedBy = "最後一次儲存者";
            //寫資料  
            ws.Cells[1, 1].Value = "Hello";

            ws.Column(1).Width=40;//修改列寬  
            ws.Cells["B1"].Value = "World";
            ws.Cells[3, 3, 3, 5].Merge = true;
            ws.Cells[3, 3].Value = "Cells[3, 3, 3, 5]合併";
            ws.Cells["A4:D5"].Merge = true;
            ws.Cells["A4"].Value = "Cells[\"A4:D5\"]合併";

            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "*.xls|*.xlsx";
            if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                FileInfo file = new FileInfo(sfd.FileName); 
                ep.File = file;
                ep.Save();
                MessageBox.Show("儲存成功");
            }
            else
            {
                MessageBox.Show("取消儲存");
            }
        }

　　上述程式碼大部分來源網路，只進行過小改動，下面是原文和資料地址：

      http://blog.csdn.net/jimmy2248/article/details/8363697

      http://blog.csdn.net/zhoufoxcn/article/details/14112473

      http://www.cnblogs.com/liudeyun/p/3535740.html

      http://blog.163.com/china__xuhua/blog/static/19972316920136335721285/