欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

利用Aspose.Cells实现万能导出功能

程序员文章站 2022-06-25 08:58:08
最近做了个项目,客户对导出excel功能情有独钟,几乎要求每一个列表数据都支持导出excel功能,为了避免代码重复,万能粉嫩的小码农开发了万能导出qaq. 导出exc...

最近做了个项目,客户对导出excel功能情有独钟,几乎要求每一个列表数据都支持导出excel功能,为了避免代码重复,万能粉嫩的小码农开发了万能导出qaq.

导出excel无非就是取出数据,然后利用aspose.cells插件填充到excel文件中,datatable类型的数据是最适合填充excel不过了.唯一的问题就是datatable数据的列头一般是英文,突然就想出了利用sql server每一列的说明来替换掉英文列头的方法,我太tm机智了.

利用Aspose.Cells实现万能导出功能

/// <summary>
/// 导出
/// </summary>
/// <param name="dt">导出的数据表</param>
/// <param name="dic">字段名称,字段中文名称</param>
/// <param name="title">导出第一行标题</param>
/// <returns></returns>


public workbook exportdata(datatable table, dictionary<string, string> dic, string title = "")
    {
      title = string.isnullorempty(title) ? "导出数据" : title;

      workbook workbook = new workbook();
      workbook.worksheets.removeat(0);//移除第一个sheet

      var tempstrarray = system.activator.createinstance<t>().gettype().fullname.split('.');//
      string tablename = tempstrarray[tempstrarray.count() - 1];//这两句是反射生成要操作的表格名称的,
      var basedic = getcolumnsbytable(tablename, "");

      foreach (datacolumn item in table.columns)
      {
        string chscolumnname = "";
        if (basedic.trygetvalue(item.columnname, out chscolumnname) && !string.isnullorempty(chscolumnname))
          item.columnname = chscolumnname;
        if (dic.trygetvalue(item.columnname, out chscolumnname) && !string.isnullorempty(chscolumnname))
          item.columnname = chscolumnname;
      }

      int colnum = table.columns.count;//表格列数
      int rownum = table.rows.count;//表格行数

      worksheet sheet = workbook.worksheets.add(title);
      cells cells = sheet.cells;//单元格

      //为标题设置样式
      style styletitle = workbook.styles[workbook.styles.add()];//新增样式
      styletitle.horizontalalignment = textalignmenttype.center;//文字居中
      styletitle.font.name = "宋体";//文字字体
      styletitle.font.size = 18;//文字大小
      styletitle.font.isbold = true;//粗体

      //样式2
      style style2 = workbook.styles[workbook.styles.add()];//新增样式
      style2.horizontalalignment = textalignmenttype.center;//文字居中
      style2.font.name = "宋体";//文字字体
      style2.font.size = 13;//文字大小
      style2.font.isbold = true;//粗体
      style2.istextwrapped = true;//单元格内容自动换行
      style2.borders[bordertype.leftborder].linestyle = cellbordertype.thin;
      style2.borders[bordertype.rightborder].linestyle = cellbordertype.thin;
      style2.borders[bordertype.topborder].linestyle = cellbordertype.thin;
      style2.borders[bordertype.bottomborder].linestyle = cellbordertype.thin;

      //样式3
      style style3 = workbook.styles[workbook.styles.add()];//新增样式
      style3.horizontalalignment = textalignmenttype.center;//文字居中
      style3.font.name = "宋体";//文字字体
      style3.font.size = 12;//文字大小
      style3.borders[bordertype.leftborder].linestyle = cellbordertype.thin;
      style3.borders[bordertype.rightborder].linestyle = cellbordertype.thin;
      style3.borders[bordertype.topborder].linestyle = cellbordertype.thin;
      style3.borders[bordertype.bottomborder].linestyle = cellbordertype.thin;

      //生成行1 标题行
      cells.merge(0, 0, 1, colnum);//合并单元格
      cells[0, 0].putvalue(title);//填写内容
      cells[0, 0].setstyle(styletitle);
      cells.setrowheight(0, 38);

      //生成行2 列名行
      for (int i = 0; i < colnum; i++)
      {
        cells[1, i].putvalue(table.columns[i].columnname);
        cells[1, i].setstyle(style2);
        cells.setrowheight(1, 25);
        cells.setcolumnwidth(i, 30);
      }

      //生成数据行
      for (int i = 0; i < rownum; i++)
      {
        for (int k = 0; k < colnum; k++)
        {
          cells[2 + i, k].putvalue(table.rows[i][k].tostring());
          if (k == colnum - 1)
          {
            style3.horizontalalignment = textalignmenttype.left;//文字居中
          }
          else
          {
            style3.horizontalalignment = textalignmenttype.center;//文字居中
          }
          cells[2 + i, k].setstyle(style3);
        }
        cells.setrowheight(2 + i, 24);
      }
      return workbook;
    }

/// <summary>
/// 获取某个表下面的所有列名和说明
/// </summary>
/// <param name="tbname">表名</param>
/// <param name="orderrule">排序规则</param>
/// <returns></returns>
public dictionary<string, string> getcolumnsbytable(string tbname, string orderrule)
    {
      stringbuilder sqlsb = new stringbuilder();
      sqlsb.append("select distinct columnsname = c.name,description = isnull(ex.value,'') ");
      sqlsb.append("from sys.columns c left outer join sys.extended_properties ex ");
      sqlsb.append("on ex.major_id = c.object_id and ex.minor_id = c.column_id and ex.name = 'ms_description' ");
      sqlsb.append("left outer join systypes t on c.system_type_id=t.xtype ");
      sqlsb.append("where objectproperty(c.object_id, 'ismsshipped')=0 and ");
      sqlsb.append("object_name(c.object_id) ='{0}' ");
      if (!string.isnullorempty(orderrule))
      {
        sqlsb.append("order by columnsname {1}");
      }
      else
      {
        sqlsb.append("order by columnsname asc");
      }

      string exsql = string.format(sqlsb.tostring(), tbname, orderrule);

      datatable dt = db.fromsql(exsql).totable() as datatable;//用了mysoft框架qaq

      dictionary<string, string> dic = new dictionary<string, string>();
      if (dt != null && dt.rows.count > 0)
      {
        for (int i = 0; i < dt.rows.count; i++)
        {
          dic.add(dt.rows[i][0].tostring(), dt.rows[i][1].tostring());
        }
      }
      return dic;
    }

两个搞定了,能实现绝大部分的导出业务。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。