首页 > 代码库 > excel导出 按dataset里的datatable导出一个excel多个sheet 和 datatable导出
excel导出 按dataset里的datatable导出一个excel多个sheet 和 datatable导出
//datatable导出public static void DataTableToExcel(string filename, string sheetName, DataTable table) { //增加序号列 //rui.dbHelper.insert序号(table, 0); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); //设置单元格的样式:水平垂直居中 ICellStyle style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; //写标题 IRow header = sheet.CreateRow(0); for (int i = 0; i < table.Columns.Count; i++) { ICell cell = header.CreateCell(i); string value = http://www.mamicode.com/table.Columns[i].ColumnName;//标题转换"application/vnd.ms-excel"; HttpContext.Current.Response.Charset = "UTF8"; //根据不同的浏览器设置对应的文件名 string attachFilename = ""; { string enCodeFilename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8); string userAgent = HttpContext.Current.Request.Browser.Browser; userAgent = userAgent.ToLower(); //rui.logTools.log("浏览器类型:" + userAgent); //IE浏览器 if (userAgent.IndexOf("ie") != -1 || userAgent.IndexOf("mozilla") != -1) { attachFilename = @"filename=" + enCodeFilename; } //Opera浏览器只能采用filename* else if (userAgent.IndexOf("opera") != -1) { attachFilename = @"filename*=UTF-8‘‘" + enCodeFilename; } //FireFox浏览器 else if (userAgent.IndexOf("firefox") != -1) { attachFilename = @"filename*=" + enCodeFilename; } //遨游 else if (userAgent.IndexOf("chrome") != -1) { attachFilename = @"filename=" + enCodeFilename; } else { attachFilename = @"filename=" + enCodeFilename; } } HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;{0}.xls", attachFilename)); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); workbook = null; ms.Close(); ms.Dispose(); }
public static void DataSetToExcel(string filename, DataSet ds) { IWorkbook workbook = new HSSFWorkbook(); for (int i = 0; i < ds.Tables.Count; i++) { string sheetName = ds.Tables[i].TableName; //增加序号列 //rui.dbHelper.insert序号(table, 0); ISheet sheet = workbook.CreateSheet(sheetName); //设置单元格的样式:水平垂直居中 ICellStyle style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; //写标题 IRow header = sheet.CreateRow(0); for (int j = 0; j < ds.Tables[i].Columns.Count; j++) { ICell cell = header.CreateCell(j); string value = http://www.mamicode.com/ds.Tables[i].Columns[j].ColumnName;//标题转换 cell.SetCellValue(value); cell.CellStyle = style; //sheet.SetColumnWidth(i, 30 * 240); //header.CreateCell(i).SetCellValue(table.Columns[i].ColumnName); } //写数据 for (int j = 0; j < ds.Tables[i].Rows.Count; j++) { IRow row = sheet.CreateRow(j + 1);// for (int k = 0; k < ds.Tables[i].Columns.Count; k++) { string celltext = ds.Tables[i].Rows[i][k].ToString(); ICell cell = row.CreateCell(k); cell.SetCellValue(celltext); cell.CellStyle = style; } } //宽度自适应 for (int columnNum = 0; columnNum < ds.Tables[i].Columns.Count; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度 for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行 { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度 if (columnWidth < length + 1) { columnWidth = length + 1; }//若当前单元格内容宽度大于列宽,则调整列宽为当前单元格宽度,后面的+1是我人为的将宽度增加一个字符 } sheet.SetColumnWidth(columnNum, columnWidth * 256); } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.Charset = "UTF8"; //根据不同的浏览器设置对应的文件名 string attachFilename = ""; { string enCodeFilename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8); string userAgent = HttpContext.Current.Request.Browser.Browser; userAgent = userAgent.ToLower(); //rui.logTools.log("浏览器类型:" + userAgent); //IE浏览器 if (userAgent.IndexOf("ie") != -1 || userAgent.IndexOf("mozilla") != -1) { attachFilename = @"filename=" + enCodeFilename; } //Opera浏览器只能采用filename* else if (userAgent.IndexOf("opera") != -1) { attachFilename = @"filename*=UTF-8‘‘" + enCodeFilename; } //FireFox浏览器 else if (userAgent.IndexOf("firefox") != -1) { attachFilename = @"filename*=" + enCodeFilename; } //遨游 else if (userAgent.IndexOf("chrome") != -1) { attachFilename = @"filename=" + enCodeFilename; } else { attachFilename = @"filename=" + enCodeFilename; } } HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;{0}.xls", attachFilename)); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); workbook = null; ms.Close(); ms.Dispose(); }
excel导出 按dataset里的datatable导出一个excel多个sheet 和 datatable导出
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。