1  /// <summary>
  2         /// 导出DataTable数据
  3         /// </summary>
  4         /// <param name="dt"></param>
  5         private void ExprotDataByDataTable(DataTable dt)
  6         {
  7             try
  8             {
  9                 if(dt.Rows.Count <= 0 )
 10                     throw new Exception("没有可导出的数据");
 11                 const int rowCount = 60000;
 12                 //if (dt.Rows.Count > 10 * rowCount)
 13                 //{
 14                 //    throw new Exception("数据量已超过60万，请清理部分数据再导出！");
 15                 //}
 16                 HSSFWorkbook hssfworkbook = new HSSFWorkbook();
 17                 int iNum = 0;
 18                 int i = 0;
 19                 while (true)
 20                 {
 21                     if (iNum >= i * rowCount)
 22                     {
 23                         iNum = ExprotDataCreateSheet("Sheet" + i, hssfworkbook, dt, iNum, (i + 1) * rowCount);
 24                         i++;
 25                     }
 26 
 27                     if (iNum >= dt.Rows.Count - 1)
 28                         break;
 29                 }
 30 
 31                 string fileName = "清单" + DateTime.Now.ToString("yyyyMMddmmss") + ".xls";
 32                 SaveFileDialog saveFileDialog = new SaveFileDialog();
 33                 saveFileDialog.Filter = "Execl files(*.xls)|*.xls";
 34                 saveFileDialog.FilterIndex = 0;
 35                 saveFileDialog.RestoreDirectory = true; //保存对话框是否记忆上次打开的目录 
 36                 //saveFileDialog.CreatePrompt = true;
 37                 saveFileDialog.Title = "导出Excel文件到";
 38                 saveFileDialog.FileName = fileName;
 39 
 40                 //点了保存按钮进入   
 41                 if (saveFileDialog.ShowDialog() == DialogResult.OK)
 42                 {
 43                     if (saveFileDialog.FileName.Trim() == "")
 44                     {
 45                         MessageBox.Show("请输入要保存的文件名", "提示");
 46                         return;
 47                     }
 48                     using (FileStream file = new FileStream(saveFileDialog.FileName, FileMode.Create))
 49                     {
 50                         hssfworkbook.Write(file); //创建xls文件。
 51                         file.Close();
 52                     }
 53                 }
 54             }
 55             catch (Exception ex)
 56             {
 57                 throw ex;
 58             }
 59         }
 60 
 61         /// <summary>
 62         /// 分页添加数据
 63         /// </summary>
 64         /// <param name="sheetName"></param>
 65         /// <param name="hssfworkbook"></param>
 66         /// <param name="dt"></param>
 67         /// <param name="dtNum"></param>
 68         /// <param name="constNum"></param>
 69         /// <returns></returns>
 70         private int ExprotDataCreateSheet(string sheetName, HSSFWorkbook hssfworkbook, DataTable dt, int dtNum, int constNum)
 71         {
 72             int iNum = 0;
 73             HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet(sheetName); //建立一个SHEET
 74             sheet.PrintSetup.PaperSize = 9; //A4纸
 75             sheet.PrintSetup.Landscape = false; //true:横向   false:竖向
 76             //设置页边距
 77             sheet.SetMargin(MarginType.RightMargin, (double)0.2);
 78             sheet.SetMargin(MarginType.TopMargin, (double)0.2);
 79             sheet.SetMargin(MarginType.LeftMargin, (double)0.2);
 80             sheet.SetMargin(MarginType.BottomMargin, (double)0.2);
 81             //sheet.PrintSetup.Scale = (short)72;//缩放72%
 82 
 83             //第一个样式,抬头
 84             HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
 85             HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
 86             font.FontName = "新宋体";
 87             font.FontHeight = 14 * 14;  //设置字体大小
 88             font.Boldweight = short.MaxValue;
 89             style.SetFont(font); //样式中加载字体样式
 90             style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
 91             style.VerticalAlignment = VerticalAlignment.Center; //居中
 92             style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //设置单元格边框
 93             style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
 94             style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
 95             style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
 96 
 97             int rowNmu = 0;
 98             HSSFRow row = (HSSFRow)sheet.CreateRow(rowNmu); //建立一行
 99             string[] strColumn = excelColumn.Split(new char[] { ',' });
100             int iNo = 0;
101             foreach (string s in strColumn)
102             {
103                 if (!string.IsNullOrEmpty(s))
104                 {
105                     //设定每一列的宽度，第一个参数是第N列，第二个是列宽
106                     sheet.SetColumnWidth(iNo, 20 * 256);
107 
108                     ExcelSetValue(row, iNo, style, s.Trim());
109                 }
110                 iNo++;
111             }
112 
113             for (int i = dtNum; i < dt.Rows.Count; i++)
114             {
115                 iNum = i;
116                 if (constNum == i)
117                     break;
118                 rowNmu++;
119                 row = (HSSFRow)sheet.CreateRow(rowNmu); //建立新的行
120 
121                 iNo = 0;
122                 foreach (string s in strColumn)
123                 {
124                     if (!string.IsNullOrEmpty(s))
125                     {
126                         ExcelSetValue(row, iNo, style, dt.Rows[i][s.Trim()].ToString());
127                         iNo++;
128                     }
129                 }
130             }
131             return iNum;
132         }
133 
134         /// <summary>
135         /// 新建单元格并赋值
136         /// </summary>
137         /// <param name="row">行</param>
138         /// <param name="cellNum">列数</param>
139         /// <param name="style">单元格样式</param>
140         /// <param name="strValue">单元格的值</param>
141         private void ExcelSetValue(HSSFRow row,
142             int cellNum, HSSFCellStyle style, string strValue)
143         {
144             HSSFCell cell = (HSSFCell)row.CreateCell(cellNum);
145             cell.CellStyle = style;
146             cell.SetCellValue(strValue);
147         }

 1 /// <summary>
 2         /// 单页导出
 3         /// </summary>
 4         /// <param name="sender"></param>
 5         /// <param name="e"></param>
 6         private void sbtnExport_Click(object sender, EventArgs e)
 7         {
 8             try
 9             {
10                 if (!string.IsNullOrEmpty(excelColumn))
11                 {
12                     string sql = string.Empty;
13                     string strSort = string.Empty;
14                     if (!string.IsNullOrEmpty(sqlSort))
15                         strSort = (" order by " + sqlSort);
16                     string[] strCondition = sqlSort.Split(new char[] { ',' });
17                     sql = @"SELECT * FROM (
18                         SELECT row_number() OVER(ORDER BY  " + strCondition[0] + @") AS 序号,*
19                          FROM ( SELECT  " + sqlColumn + " from " + sqlTable +
20                           "  WHERE 1=1 ";
21                     if (!string.IsNullOrEmpty(sqlCondition))
22                         sql += sqlCondition;
23                     sql += ")a)a ";
24 
25                     sql += (" WHERE 序号 BETWEEN " + ((pageCurrent - 1) * pageSize + 1) + " AND " +
26                             pageCurrent * pageSize);
27                     sql += strSort;
28                     ExprotDataByDataTable(BaseDataAccess.ExecuteQuery(sql));
29                 }
30             }
31             catch (Exception ex)
32             {
33                 MessageBox.Show("导出Excel出错:" + ex.Message);
34             }
35         }
36         /// <summary>
37         /// 导出查询出来的所有数据
38         /// </summary>
39         /// <param name="sender"></param>
40         /// <param name="e"></param>
41         private void sbtnExprotAll_Click(object sender, EventArgs e)
42         {
43             try
44             {
45                 if (!string.IsNullOrEmpty(excelColumn))
46                 {
47                     string sql = string.Empty;
48                     string strSort = string.Empty;
49                     if (!string.IsNullOrEmpty(sqlSort))
50                         strSort = (" order by " + sqlSort);
51                     string[] strCondition = sqlSort.Split(new char[] { ',' });
52                     sql = @"SELECT * 
53                          FROM ( SELECT  " + sqlColumn + " from " + sqlTable +
54                           "  WHERE 1=1 ";
55                     if (!string.IsNullOrEmpty(sqlCondition))
56                         sql += sqlCondition;
57                     sql += ")a ";
58 
59                     sql += strSort;
60                     ExprotDataByDataTable(BaseDataAccess.ExecuteQuery(sql));
61                 }
62             }
63             catch (Exception ex)
64             {
65                 MessageBox.Show("导出Excel出错:" + ex.Message);
66             }
67         }