前台:
{ id: 'print', iconCls: 'icon-print', text: '导出', handler: function () { var filterRules = $('#dg').datagrid('options').filterRules; var filters = ""; if (filterRules.length > 0) { filters = "["; for (var i = 0; i < filterRules.length; i++) { if (i > 0) { filters += ","; } filters += "{\"field\":\"" + filterRules[i].field + "\",\"op\":\"" + filterRules[i].op + "\",\"value\":\"" + filterRules[i].value + "\"}"; } filters += "]"; } window.open("/Test/Test/TestDataQuery?export=y&filterRules=" + filters); } }
后台:
public ActionResult TestDataQuery() {
private Entitie db = new Entitie(); //查询语句 string sql = "select t.ID,t.TEST1,t.TEST2,t.TEST3,t.TEST4,t.TEST5 from test t"; //----导出参数 string excelname = "测试表"; string[] excelcol = { "测试1", "测试2", "测试3", "测试4", "测试5" };
DataTable dt = DbHelperOrcl.GetDTQuery(db.Database.Connection.ConnectionString, sql);/
if (Request["export"] == "y")
{
GridExport(dt, excelname, excelcol);
}
string jsonData = GetJeJsonData(db.Database.Connection.ConnectionString, sql, Request, excelname, excelcol); return Content(jsonData); }
导出:
public static void GridExport(DataTable dt, string excelname, string[] excelcol) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICell cells; //-------------------------------------------------------------------- ICellStyle styles = GetStyles(workbook);//普通样式 ICellStyle styles_zd = GetzdStyles(workbook);//表头字段样式 //----------------------标题----------------------------------------------- //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count-1)); //----------------------表头----------------------------------------------- cells = sheet.CreateRow(0).CreateCell(0); cells.SetCellValue(excelname); cells.CellStyle = styles_zd; int col_head = 0; for (int j = 0; j < dt.Columns.Count; j++) { string name = dt.Columns[j].ColumnName; if (col_head == 0) { cells = sheet.CreateRow(1).CreateCell(col_head); } else { cells = sheet.GetRow(1).CreateCell(col_head); } cells.SetCellValue(name); cells.CellStyle = styles_zd; col_head++; if (name == "ID" || name == "id") { sheet.SetColumnHidden(0, true); } } //----------------------数据----------------------------------------------- int rowno = 1; foreach (DataRow dr in dt.Rows) { int cellno = 0; for (int i = 0; i < dt.Columns.Count; i++) { object value = dr[i]; //如果非空,则赋给对象的属性 if (cellno == 0) { cells = sheet.CreateRow(rowno).CreateCell(cellno); } else { cells = sheet.GetRow(rowno).CreateCell(cellno); } if (value != DBNull.Value) { cells.SetCellValue(value.ToString()); } cells.CellStyle = styles; cellno += 1; } rowno += 1; } HttpResponse response= System.Web.HttpContext.Current.Response; string filename = excelname!=null? excelname+".xls" : "数据.xls"; response.ContentType = "application/vnd.ms-excel"; response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); response.Clear(); response.BinaryWrite(WriteToStream(workbook).GetBuffer()); response.End(); }
不喜勿喷!!!纯属个人经验