//下载NPOI类库并添加引用 using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.SS.Util;
public static void MergeCell(ISheet tb, ICell cl, int firstrow, int lastrow, int firstcol, int lastcol, string name) { tb.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol)); cl.SetCellValue(name); } public static ICellStyle SetCellStyle(IWorkbook wk, int number) { ICellStyle cellStyle = wk.CreateCellStyle(); if (number == 0)//标题栏不设置边框样式 { //对齐 cellStyle.VerticalAlignment = VerticalAlignment.CENTER; cellStyle.Alignment = HorizontalAlignment.CENTER; //自动换行 cellStyle.WrapText = true; } else { cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; //对齐 cellStyle.VerticalAlignment = VerticalAlignment.CENTER; cellStyle.Alignment = HorizontalAlignment.CENTER; //自动换行 cellStyle.WrapText = true; } return cellStyle; }
public static string ExportDLAnalyse2Excel(string excleTitle,string area) { string[] dlDataArr = area.Split(','); int rowlength = dlDataArr.Length / 3;//数据表格的行数 //创建工作薄 HSSFWorkbook wk = new HSSFWorkbook(); //创建一个名称为Sheet1的表 ISheet tb = wk.CreateSheet(); wk.SetSheetName(0, "Sheet1"); for (int i = 0; i < rowlength+2; i++) { ICellStyle cellStyle = SetCellStyle(wk, i); IRow row = tb.CreateRow(i); for (int j = 0; j < 8; j++)//数据表格的列数 { ICell cell = row.CreateCell(j); cell.CellStyle = cellStyle; } } MergeCell(tb, tb.GetRow(0).GetCell(2), 0, 0, 2, 5, excleTitle);//设置excel标题 MergeCell(tb, tb.GetRow(1).GetCell(0), 1, 1, 0, 1, "编码");//设置表格标题栏 MergeCell(tb, tb.GetRow(1).GetCell(2), 1, 1, 2, 5, "名称"); MergeCell(tb, tb.GetRow(1).GetCell(6), 1, 1, 6, 7, "面积(/公顷)"); int rownum = 2; for (int i = 0; i < dlDataArr.Length; i+=3) { MergeCell(tb, tb.GetRow(rownum).GetCell(0), rownum, rownum, 0, 1, dlDataArr[i]); MergeCell(tb, tb.GetRow(rownum).GetCell(2), rownum, rownum, 2, 5, dlDataArr[i + 1]); MergeCell(tb, tb.GetRow(rownum).GetCell(6), rownum, rownum, 6, 7, dlDataArr[i + 2]); rownum++; } MemoryStream mstream = new MemoryStream(); wk.Write(mstream); DownloadFile(mstream, excleTitle, "xls"); return null; }
/// <summary> /// 从服务器下载Excel到客户端 /// </summary> /// <param name="fs"></param> /// <param name="filename"></param> /// <returns></returns> public static string DownloadFile(MemoryStream fs, string filename,string extenname) { string fileName = filename + "."+extenname;//客户端保存的文件名 //以字符流的形式下载文件 byte[] bytes = fs.ToArray(); fs.Read(bytes, 0, bytes.Length); fs.Close(); System.Web.HttpContext.Current.Response.Clear(); System.Web.HttpContext.Current.Response.ClearContent(); System.Web.HttpContext.Current.Response.ClearHeaders(); System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream"; //通知浏览器下载文件而不是打开 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); System.Web.HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary"); System.Web.HttpContext.Current.Response.BinaryWrite(bytes); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.End(); return null; }