引用一下dll
using org.in2bits.MyXls;
1 public void DaoChuExeclWenTi(string strwhere, string workSheetName, string strSortExpression = "") 2 { 3 if (strSortExpression == "") 4 strSortExpression = "Row_ID asc"; 5 strwhere = strwhere.Replace("where", ""); 6 string sqldv = string.Format(@" select row_number() over (order by Row_ID asc) as binahao,DanWeiName,to_char(BanJDate,'yyyy-MM-dd') as BanJDate,BanJUserName 7 from DJ_DanWeiRYPBQK where {0} order by {1}", strwhere, strSortExpression); 8 DataView dv = Epoint.MisBizLogic2.DB.ExecuteDataView(sqldv); 9 XlsDocument doc = new XlsDocument(); 10 ExcelExport(workSheetName, dv.ToTable(), doc, 4, 6); 11 string filename = workSheetName + DateTime.Now.ToString() + ".xls"; 12 doc.FileName = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8); 13 doc.Send(); 14 } 15 16 private void ExcelExport(string workSheetName, DataTable dt, XlsDocument xls, int startrow, int dynamicCol) 17 { 18 //创建一个工作页为Dome 19 Worksheet ws = xls.Workbook.Worksheets.Add(workSheetName); 20 #region 设置xls文档的指定工作页的行属性 21 //设置行高 22 RowInfo rol1 = new RowInfo(); 23 rol1.RowHeight = 32 * 20; 24 rol1.RowIndexStart = 0; 25 rol1.RowIndexEnd = 1; 26 ws.AddRowInfo(rol1); 27 //设置行高 28 RowInfo rol2 = new RowInfo(); 29 rol2.RowHeight = 16 * 20; 30 rol2.RowIndexStart = 1; 31 rol2.RowIndexEnd = 2; 32 ws.AddRowInfo(rol2); 33 //设置行高 34 RowInfo rol3 = new RowInfo(); 35 rol3.RowHeight = 32 * 20; 36 rol3.RowIndexStart = 2; 37 rol3.RowIndexEnd = (ushort)(dt.Rows.Count + 5); 38 ws.AddRowInfo(rol3); 39 ColumnInfo colInfo = new ColumnInfo(xls, ws); 40 #endregion 41 42 #region 设置xls文档的指定工作页的列属性 43 //序号 0 44 colInfo.ColumnIndexStart = 0; 45 colInfo.ColumnIndexEnd = 1; 46 colInfo.Width = 50 * 50; 47 ws.AddColumnInfo(colInfo); 48 // 单位名称 49 ColumnInfo colInfoL = new ColumnInfo(xls, ws); 50 colInfoL.ColumnIndexStart = 1; 51 colInfoL.ColumnIndexEnd = 2; 52 colInfoL.Width = 50 * 200; 53 ws.AddColumnInfo(colInfoL); 54 // 办件时间 55 ColumnInfo colInfo1 = new ColumnInfo(xls, ws); 56 colInfo1.ColumnIndexStart = 2; 57 colInfo1.ColumnIndexEnd = 3; 58 colInfo1.Width = 20 * 200; 59 ws.AddColumnInfo(colInfo1); 60 //办件人姓名 61 ColumnInfo colInfoX = new ColumnInfo(xls, ws); 62 colInfoX.ColumnIndexStart = 3; 63 colInfoX.ColumnIndexEnd = 4; 64 colInfoX.Width = 20 * 200; 65 ws.AddColumnInfo(colInfoX); 66 67 #endregion 68 69 #region 创建单元格样式 70 XF xfhead = xls.NewXF(); 71 xfhead.HorizontalAlignment = HorizontalAlignments.Centered; 72 xfhead.VerticalAlignment = VerticalAlignments.Centered; 73 xfhead.Font.Bold = false; 74 xfhead.Font.FontName = "宋体"; 75 xfhead.Font.Height = 26 * 20; 76 //自动换行 77 xfhead.TextWrapRight = true; 78 XF xfsec = xfhead; 79 xfsec.HorizontalAlignment = HorizontalAlignments.Centered; 80 81 XF xf = xls.NewXF(); 82 xf.HorizontalAlignment = HorizontalAlignments.Centered; 83 xf.VerticalAlignment = VerticalAlignments.Centered; 84 xf.Pattern = 0; 85 xf.PatternColor = Colors.White; 86 xf.UseBorder = false; 87 xf.TopLineStyle = 1; 88 xf.TopLineColor = Colors.Black; 89 xf.BottomLineStyle = 1; 90 xf.BottomLineColor = Colors.Black; 91 xf.LeftLineStyle = 1; 92 xf.LeftLineColor = Colors.Black; 93 xf.RightLineStyle = 1; 94 xf.RightLineColor = Colors.Black; 95 xf.Font.Bold = false; 96 xf.Font.FontName = "宋体"; 97 xf.Font.Height = 12 * 20; 98 //自动换行 99 xf.TextWrapRight = true; 100 101 #endregion 102 int ViewStatestartrow = startrow; 103 //Worksheet,单元格样式，列名，开始行，开始列，结束行，结束列 104 MergeRegion(ref ws, xfhead, workSheetName, 1, 1, 1, dt.Columns.Count); 105 MergeRegion(ref ws, xf, "编号", ViewStatestartrow - 2, 1, ViewStatestartrow - 1, 1); 106 MergeRegion(ref ws, xf, "单位名称", ViewStatestartrow - 2, 2, ViewStatestartrow - 1, 2); 107 MergeRegion(ref ws, xf, "办件时间", ViewStatestartrow - 2, 3, ViewStatestartrow - 1, 3); 108 MergeRegion(ref ws, xf, "办件人姓名", ViewStatestartrow - 2, 4, ViewStatestartrow - 1, 4); 109 110 int ViewStateEndrow = startrow + 1; 111 //插入数据是区分表头控件变量重新定义为xf1 112 XF xf1 = xls.NewXF(); 113 xf1 = xf; 114 xf1.Font.Bold = false; 115 xf1.Font.Height = 12 * 20; 116 int HeBingStat = 0; 117 for (int i = 0; i < dt.Rows.Count; i++) 118 { 119 int statRow = i + ViewStatestartrow; 120 for (int k = 0; k < dt.Columns.Count; k++) 121 { 122 ws.Cells.Add(statRow, k + 1, dt.Rows[i][k].ToString(), xf1); 123 } 124 ViewStateEndrow = startrow; 125 } 126 127 } 128 public static void MergeRegion(ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol) 129 { 130 for (int i = startCol; i <= endCol; i++) 131 { 132 for (int j = startRow; j <= endRow; j++) 133 { 134 //行，列，列值，单元格 135 ws.Cells.Add(j, i, title, xf); 136 } 137 } 138 ws.Cells.Merge(startRow, endRow, startCol, endCol); 139 }
