利用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    {      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;    }