NOPI 操作 复杂 EXCEl

 NOPI 下载地址 http://npoi.codeplex.com/  
 它是 免费的 NET平台 导出 导入 Excel 组件 

 优点 ：无需安装office ，包含了office 绝大多数功能（单元格样式，数据格式，公式等等）

 1. 创建 工作表 输出Excel

    /// </summary> 带模板导出Excel文件
    /// <param name="fileName">导出Excel文件名</param>
    /// <param name="sheetName">sheet名</param>
    private void ExportDataSetToExcel(string strFileName, string strSheetName)
    {
        //复制模板文件
        string filePath = Server.MapPath("~/UploadExcel/" + Guid.NewGuid().ToString() + DateTime.Now.Millisecond + ".xls");
        File.Copy(Server.MapPath("~/UploadExcel/価格表.xls"), filePath);
        HSSFWorkbook workbook;
        // 以流的形式 打开 模板文件
        using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
        {
            //创建工作表
            workbook = new HSSFWorkbook(file);
            //sheet 名赋值
            workbook.SetSheetName(0, strSheetName)
        }
        //返回流形式的文件
        MemoryStream ms = ExportToExcel(workbook, strSheetName) as MemoryStream;
        File.Delete(filePath); 
        //通知浏览器下载文件
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="
            + System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
        //以数组的形式输出Excel
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.End();
        ms.Close();
        ms = null;
    }

2. Excel数据绑定

 /// <summary>
    /// 设置Excel内容
    /// </summary>
    /// <param name="workbook">工作表</param>
    /// <param name="strSheetName">sheet名</param>
    /// <returns>Excel文件流</returns>
    private Stream ExportToExcel(HSSFWorkbook workbook, string strSheetName)
    {
        MemoryStream ms = new MemoryStream();

        //获取数据
        DataTable dtJAN = new DataTable();
        if (Session["ExcelProductInfo"] != null)
        {
          dtJAN = (DataSet)Session["ExcelProductInfo"].Tables[0];
        }
        // 定义单元格样式
        HSSFCellStyle StyleDataRight = GetCellStyle(workbook, "DataRight");

        //根据sheet名获取sheet
        HSSFSheet sheetCompute = (HSSFSheet)workbook.GetSheet(strSheetName);
        //设定数据开始行
        int intComputeStartRow = 4;
        //获取数据总行数
        int intJANComputeCount = dtJAN.Rows.Count;
        //循环数据
        for (int i = 0; i < intJANComputeCount; i++)
        {
            //创建数据行
            HSSFRow row = sheetCompute.CreateRow(intComputeStartRow);

            //单元格设定公式
            row.CreateCell(1).SetCellFormula(String.Format(ct.GetXmlFormula("Formula1PFee"), intComputeStartRow + 1));
            //
            row.GetCell(1).CellStyle = SetCellStyle(workbook, StyleDataRight, true);
            //1Pその他経費
            row.CreateCell(2).SetCellFormula(String.Format(ct.GetXmlFormula("Formula1PPrice"), intComputeStartRow + 1));
            row.GetCell(2).CellStyle = SetCellStyle(workbook, StyleDataRight, true);
            //1P店着原価
            row.CreateCell(3).SetCellValue（"值设定"）;
            row.GetCell(3).CellStyle = SetCellStyle(workbook, StyleDataRight, true);
        }
        //excel 数据重算
        sheetCompute.ForceFormulaRecalculation = true;
        //返回流
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;
        return ms;
    }

3  单元格样式设置
 

 //设定单元格样式
    private HSSFCellStyle GetCellStyle(HSSFWorkbook workbook, string StyleName)
    {
        //创建字体
        HSSFFont fContent = (HSSFFont)workbook.CreateFont();
        fContent.FontName = "ＭＳ Ｐゴシック";
        fContent.FontHeightInPoints = 11;
        //创建样式 并设定
        HSSFCellStyle bigTitleStyle = workbook.CreateCellStyle();
        bigTitleStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
        bigTitleStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
        bigTitleStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
        bigTitleStyle.BorderRight = HSSFCellStyle.BORDER_THIN;
        bigTitleStyle.BorderTop = HSSFCellStyle.BORDER_THIN;
        switch (StyleName)
        {
            case "DataLeft":
                bigTitleStyle.Alignment = HSSFCellStyle.ALIGN_LEFT;
                bigTitleStyle.SetFont(fContent);
                break;
            case "DataRight":
                bigTitleStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
                bigTitleStyle.SetFont(fContent);
                break;
            default:
                break;
        }
        return bigTitleStyle;
    }
    #endregion
    /// <summary>
    /// 单元格设置统一样式(如背景色等)
    /// </summary>
    /// <param name="workbook">工作表</param>
    /// <param name="CellStyle">原单元格样式</param>
    /// <param name="IsChangeProudct">是否需要统一设置</param>
    /// <returns>返回新样式</returns>
    public HSSFCellStyle SetCellStyle(HSSFWorkbook workbook, HSSFCellStyle CellStyle, bool IsChangeProudct)
    {
        HSSFCellStyle NewCellStyle = null;
        if (IsChangeProudct)
        {
            //创建样式
            NewCellStyle = workbook.CreateCellStyle();
            //复制旧样式
            NewCellStyle.CloneStyleFrom(CellStyle);
            //追加新样式
            NewCellStyle.FillBackgroundColor = HSSFColor.TAN.index;
            NewCellStyle.FillPattern = HSSFCellStyle.BORDER_THIN;
            NewCellStyle.FillForegroundColor = HSSFColor.TAN.index;
        }
        else
        {
            NewCellStyle = CellStyle;
        }
        return NewCellStyle;
    }