c#操作excel方式四-Aspose控件

c# 专栏收录该内容
33 篇文章 0 订阅

 

参考资料:http://www.cnblogs.com/wuhuacong/archive/2011/02/23/1962147.html 

这位大大写得不错,还有复杂报表的生成,有兴趣的可以看看

http://www.cnblogs.com/kenblove/archive/2009/01/07/1371104.html

这里还有一篇Aspose基础知识的

 

方法1:读取excel内容到datable

 DataTable _dt_Data;

初始化表的列

 private void initDataTable()
        {
            _dt_Data = new DataTable();
            _dt_Data.Columns.Add(new DataColumn("姓名"));
            _dt_Data.Columns.Add(new DataColumn("地址"));
            _dt_Data.Columns.Add(new DataColumn("年龄"));
            _dt_Data.Columns.Add(new DataColumn("性别"));
            _dt_Data.Columns.Add(new DataColumn("专业"));
            _dt_Data.Columns.Add(new DataColumn("班级"));
            _dt_Data.Columns.Add(new DataColumn("学校"));

       
         

        }

遍历excel读取

 Workbook wb = new Workbook();
                    wb.Open(fileName);
                    Worksheet ws = wb.Worksheets[0];
                    int iRow = 0;
                    DataRow dr = _dt_Data.NewRow();

                    foreach (Cell c in ws.Cells)
                    {
                        if (c.Column > _dt_Data.Columns.Count - 1)
                        {
                            continue;
                        }
                        if (iRow != c.Row)
                        {
                            _dt_Data.Rows.Add(dr);
                            dr = _dt_Data.NewRow();
                        }

                        dr[c.Column] = c.StringValue;
                        iRow = c.Row;
                    }
                    _dt_Data.Rows.Add(dr);


写入excel:

  private void AddExcelSheet(Aspose.Cells.Workbook wk, DataTable dt)
        {
            if (dt == null) return;

            for (int iColumnsCount = 0; iColumnsCount < dt.Columns.Count; iColumnsCount++)
            {
                wk.Worksheets[0].Cells[0, iColumnsCount].PutValue(dt.Columns[iColumnsCount].ColumnName);
            }
            for (int iRowsCount = 0; iRowsCount < dt.Rows.Count; iRowsCount++)
            {
                for (int iColumnsCount = 0; iColumnsCount < dt.Columns.Count; iColumnsCount++)
                {
                    wk.Worksheets[0].Cells[iRowsCount + 1, iColumnsCount].PutValue(dt.Rows[iRowsCount][iColumnsCount].ToString());
                }
            }
        }


  Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
                AddExcelSheet(wk, _dt_Data);
                wk.Save(strFileName);




方法2:我们直接用这位大大封装的类来实现写入excel和读取excel

 

 

1.下载引用:

aspose.cell.dll

 http://download.csdn.net/detail/q383965374/5264908

 

2.添加引用:

 

 

 

3.创建类

using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Drawing;

namespace excel4
{
    public class AsposeExcelTools
    {

        public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
        {
            error = "";
           
                if (datatable == null)
                {
                    error = "DataTableToExcel:datatable 为空";
                    return false;
                }

                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
                Aspose.Cells.Cells cells = sheet.Cells;

                int nRow = 0;
                foreach (DataRow row in datatable.Rows)
                {
                    nRow++;
                   
                        for (int i = 0; i < datatable.Columns.Count; i++)
                        {
                            if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
                            {
                                //------插入图片数据-------
                                System.Drawing.Image image = (System.Drawing.Image)row[i];
                                MemoryStream mstream = new MemoryStream();
                                image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
                                sheet.Pictures.Add(nRow, i, mstream);
                            }
                            else
                            {
                                cells[nRow, i].PutValue(row[i]);
                            }
                        }
                 
                }

                workbook.Save(filepath);
                return true;
            }
        
      
      

        /// 
        /// Excel文件转换为DataTable.
        /// 
        /// Excel文件的全路径
        /// DataTable:返回值
        /// 错误信息:返回错误信息,没有错误返回""
        /// 
  
   true:函数正确执行 false:函数执行错误
  
        public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
        {
            error = "";
            datatable = null;
            try
            {
                if (File.Exists(filepath) == false)
                {
                    error = "文件不存在";
                    datatable = null;
                    return false;
                }
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                workbook.Open(filepath);
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
                //-------------图片处理-------------
                Aspose.Cells.Pictures pictures = worksheet.Pictures;
                if (pictures.Count > 0)
                {
                    string error2 = "";
                    //if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false)
                    //{
                    //    error = error + error2;
                    //}
                }
                return true;
            }
            catch (System.Exception e)
            {
                error = e.Message;
                return false;
            }

        }


          }
      
    }


 添加一个opendiog用来选择写入的excel

 

 写入按钮代码:

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable("test");
            dt.Columns.Add("ID");

            dt.Columns.Add("number");

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
            
                for (int i = 0; i < 100; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        dr[j] = i.ToString();
                    }
                    dt.Rows.Add(dr);
                }

                string outError = "";

                AsposeExcelTools.DataTableToExcel(dt, openFileDialog1.FileName, out outError);


                if (!string.IsNullOrEmpty(outError))
                {
                    MessageBox.Show(outError);
                }
                else
                {
                    Process.Start(openFileDialog1.FileName);
                }


            }
        }

 

结果:


 

 

添加一个gridview用来显示读出的数据

 

 读取按钮代码:

  private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable("test");

            string outError = "";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                AsposeExcelTools.ExcelFileToDataTable(openFileDialog1.FileName,out dt,out outError);

                this.dataGridView1.DataSource = dt;
                this.dataGridView1.DataMember = dt.TableName;
            }


        }


结果:

 


C# 表格输出 关于引用 Aspose.Cells. 求解答疑惑

 

警告1未能解析引用的程序集“Aspose.Cells”,因为它对不在当前目标框架“.NETFramework,Version=v4.0,Profile=Client”中的“System.Web, 


Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”具有依赖关系。请删除对不在目标框架中的程序集的引用,或考虑重新


确定项目的目标。HandHygiene.UI


解决方法:

出现这个问题是在一用log4net 动态库是出现的


 


原因在于程序的目标是.net 4 framework client profile


 


在项目的属性中 选择.net 4 framework 就OK了。

  • 0
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页

打赏

张小凡vip

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值