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 }
要添加的引用：
