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

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

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

/// <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;
    }

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

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