NPOI操作EXCEL 添加报文头（AddHeader）出现下载框

来源：互联网发布：巴宝莉格子侵权淘宝编辑：程序博客网时间：2024/04/30 07:41

NPOI官网下载：http://npoi.codeplex.com/releases

优点：不需要安装EXCEL

引用dll，使用方法：

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("mySheet");            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);            row.CreateCell(0).SetCellValue("1");            NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);            row2.CreateCell(0).SetCellValue("2");            System.IO.MemoryStream ms = new System.IO.MemoryStream();            book.Write(ms);            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=abc.xls"));            Response.BinaryWrite(ms.ToArray());            book = null;            ms.Close();            ms.Dispose();

本地保存：

string tempPath = @"C:\Users\Administrator\Desktop\1.xls";FileStream file = new FileStream(tempPath, FileMode.OpenOrCreate);book.Write(file);file.Flush();file.Close();

Response.AddHeader("Content-Disposition", string.Format("attachment; filename=abc.xls"));一开始我以为只能在aspx中使用这段代码，结果发现在html+ashx中也可以，只是不能用ajax的方式请求，要在前台： href="../dzxswebservice/exportTest.ashx" 使用a标签的方式请求后台。

这里:

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();            System.IO.MemoryStream ms = new System.IO.MemoryStream();            book.Write(ms);            context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));            context.Response.BinaryWrite(ms.ToArray());

这段代码就是提供了下载的弹出框

导入：这里的HSSFWorkbook是操作的2003版本的EXCEL，如果要操作更高级的版本需要引用NPOI.OOXML。

然后把这里所有有关HSSFWorkbook类的改成XSSFWorkbook，HSSFRow改成XSSFRow

HSSFWorkbook hssfworkbook;  #region  public DataTable ImportExcelFile(string filePath)  {      #region//初始化信息      try      {          using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))          {              hssfworkbook = new HSSFWorkbook(file);          }      }      catch (Exception e)      {          throw e;      }      #endregion        NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);      System.Collections.IEnumerator rows = sheet.GetRowEnumerator();      DataTable dt = new DataTable();      for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)      {          dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());     //标题    }      while (rows.MoveNext())      {          HSSFRow row = (HSSFRow)rows.Current;          DataRow dr = dt.NewRow();          for (int i = 0; i < row.LastCellNum; i++)          {              NPOI.SS.UserModel.ICell cell = row.GetCell(i);              if (cell == null)              {                  dr[i] = null;              }              else              {                  dr[i] = cell.ToString();              }          }          dt.Rows.Add(dr);      }      return dt;  }  #endregion

LastCellNum根据最后一列有值列判断的。如果第九列有值则LastCellNum的值为9。

上面代码是把EXCEL内容转成datatable类型，下面是怎么从客户端读取EXCEL。

思路：本地上传EXCEL到服务器，再从服务器读取：

            HttpFileCollection files = context.Request.Files;            if (files.Count > 0)            {                TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);                string name = Convert.ToInt64(ts.TotalSeconds).ToString() + ".xlsx";                string path = context.Request.MapPath("../../../excel/" + name);                files[0].SaveAs(path);                DataTable dt = ImportExcelFile(path);                new SFInfoManageBLL().InserSF(dt);            }

设置单元格的宽：sheet.DefaultColumnWidth = 15;

设置字体：

NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
style1.SetFont(font);
NPOI.SS.UserModel.IRow r = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell c0 = r.CreateCell(0);
c0.CellStyle = style1;

普通导出：需要安装EXCEL

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();            try            {                Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(Missing.Value); // 添加一个工作簿                Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;// 获取当前工作表                sheet.Name = "mysheet";                for (int i = 0; i < dt.Rows.Count; i++)                {                    for (int j = 0; j < dt.Columns.Count; j++)                    {                        sheet.Cells[i + 1, j + 1] = dt.Rows[i][j];                    }                }                excel.Visible = false;                excel.DisplayAlerts = true;                sheet.SaveAs("D:\\1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);            }            catch (Exception ex)            {            }            finally            {                excel.Quit();                excel = null;            }

0 0