首页 > 代码库 > 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导出