• 使用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文件

        readExcel文件到Datatable

    复制代码
            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文件

    5.1 直接生成EXcel示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    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("取消保存");
        }
    }

    5.2 从DataGridView导出至Excel

          将DataGridView显示内容导出至Excel(DataTable只需小改动即可)

         

    复制代码
                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 = "最后一次保存者";
    
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    for (int j = 1; j < dataGridView1.Columns.Count; j++)
                    {
                        if (dataGridView1.Columns[j-1].Visible == true)
                        {
                            if (dataGridView1.Rows[i].Cells[j-1].Value == null)
                            {
                                ws.Cells[i+1, j].Value = " ";
                            }
                            else
                            {
                                string rowstr = dataGridView1.Rows[i].Cells[j-1].Value.ToString();
                                if (rowstr.IndexOf("
    ") > 0)
                                    rowstr = rowstr.Replace("
    ", " ");
                                if (rowstr.IndexOf("	") > 0)
                                    rowstr = rowstr.Replace("	", " ");
                                ws.Cells[i+1, j].Value = rowstr;
                            }
                        }
                    }
                } 
    
                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("取消保存");
                }
    复制代码
    /// <summary>
            /// 读取sheet 内的数据进入实体
            /// </summary>
            /// <param name="worksheet"></param>
            /// <returns></returns>
            public List<Person> GetSheetValues(string filepath)
            {
                FileInfo file = new FileInfo(filepath);
                if (file != null)
                {
                    using (ExcelPackage package = new ExcelPackage(file))
                    {
    
                        ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                        //获取表格的列数和行数
                        int rowCount = worksheet.Dimension.Rows;
                        int ColCount = worksheet.Dimension.Columns;
                        var persons = new List<Person>();
                        for (int row = 1; row <= rowCount; row++)
                        {
                            Person person = new Person();
                            person.Name = worksheet.Cells[row, 1].Value.ToString();
                            person.Sex = worksheet.Cells[row, 2].Value.ToString();
                            persons.Add(person);
                        }
                        return persons;
                    }
                }
                 return null;
            }

    上述代码大部分来源网络,只进行过小改动,下面是原文和资料地址:

          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/

          http://www.cnblogs.com/xiaotan68/archive/2008/03/20/1114796.html

  • 相关阅读:
    软件工程课程总结
    团队-Forward团队一阶段互评
    《Forward团队-爬虫豆瓣top250项目-开发文档》
    《结对-结对编程项目作业名称-结对项目总结》
    《结对-结对编项目作业名称-最终程序》
    《结对-结对编项目作业名称-测试过程》
    《20171125-构建之法:现代软件工程-阅读笔记》
    《软件工程课程总结》
    团队编程项目作业6-程序维护
    团队编程项目作业5-小组评分
  • 原文地址:https://www.cnblogs.com/itjeff/p/12055847.html
Copyright © 2020-2023  润新知