C# HSSFWorkbook.Dispose方法代码示例

        /*
        * 响应到客户端
        *
        * Param fileName 文件名
        */
        public static void WriteToClient(String fileName, HSSFWorkbook workbook)
        {
            //Write the stream data of workbook to the root directory
            //FileStream file = new FileStream(@"c:/test.xls", FileMode.Create);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.Expires = 0;
            HttpContext.Current.Response.CacheControl = "no-cache";

            HttpContext.Current.Response.ContentType = "application/x-excel";
            //inline
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName));
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            workbook.Write(HttpContext.Current.Response.OutputStream);

            Sheet sheet = workbook.GetSheetAt(0);
            sheet = null;
            workbook.Dispose();
            workbook = null;

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
            //file.Close();
        }

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        if (Extension == ".xls")
        {
            //string notxlsx = ("This is not an xlsx file");
            //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);

            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))

                hssfworkbook = new HSSFWorkbook(file);

            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();

            //Counts the number of cells in a row and determines the columns from that.
            int counter = sheet.GetRow(0).Cells.Count;
            // J < number of columns needs to be exact at this moment
            for (int j = 0; j < counter; j++)
            {

                // set each column to a - ** letters
                // dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());

                //Get first row and set the headers for each cell
                //dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
                //Get each cell value in row 0 and return its string for a column name.
                dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
            }

            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);

            }
            //Hackish way to remove the bad first row made by getting column names
            dt.Rows.RemoveAt(0);
            GridView1.Caption = Path.GetFileName(FilePath);
            GridView1.DataSource = dt;
            //Bind the data
            GridView1.DataBind();
            sheet.Dispose();
            hssfworkbook.Dispose();

        }
        else
        {
            //Create a new epplus package using openxml
            var pck = new OfficeOpenXml.ExcelPackage();

            //load the package with the filepath I got from my fileuploader above on the button
            //pck.Load(new System.IO.FileInfo(FilePath).OpenRead());

            //stream the package
            FileStream stream = new FileStream(FilePath, FileMode.Open);
            pck.Load(stream);

            //So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
            //Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
            var ws = pck.Workbook.Worksheets[1];

            //This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
            if (ws == null)
            {
                ws = pck.Workbook.Worksheets.Add("Sheet1");
                // Obiviously I didn't add anything to the sheet so probably can count on it being blank.
            }

            //I created this datatable for below.
            DataTable tbl = new DataTable();

            //My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
            var hdr = bool.Parse(isHDR);
            Console.WriteLine(hdr);

            //Set the bool value for from above.
            var hasHeader = hdr;

            //Setup the table based on the value from my bool
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
//.........这里部分代码省略.........

        /*
         * 生成文件
         *
         * Param fileName 文件名
         *
         * Return 生成文件的URL
         */
        public static string WriteToFile(String fileName, HSSFWorkbook workbook)
        {
            //临时文件路径
            string tempFilePath = HttpContext.Current.Server.MapPath("~/" + BusinessConstants.TEMP_FILE_PATH);
            string tempFileName = GetRandomFileName(fileName);

            if (!Directory.Exists(tempFilePath))
                Directory.CreateDirectory(tempFilePath);

            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(tempFilePath + tempFileName, FileMode.Create);
            workbook.Write(file);
            file.Flush();
            file.Close();
            file.Dispose();
            file = null;

            Sheet sheet = workbook.GetSheetAt(0);
            sheet = null;
            workbook.Dispose();
            workbook = null;

            return GetShowFileUrl(tempFileName);
        }

        private static void WriteXLS(string pathTemplate, List<TempTransfer> listTransfers, Report reportTo)
        {
            string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),"Targetfile" + DateTime.Now.ToString("yyy_MM_dd_hh_mm") + ".xls");
            int row = reportTo.ResultRow;
            HSSFRow currentRow;
            HSSFCell currentCell;

            using (FileStream fs = new FileStream(pathTemplate, FileMode.Open, FileAccess.Read))
            {
                //getting complete workbook
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

                // Getting the worksheet
                HSSFSheet sheet = templateWorkbook.GetSheet(reportTo.SheetName) as HSSFSheet;

                foreach (TempTransfer item in listTransfers.OrderBy(t => t.RowNumber).ThenBy(t => t.ColIndex))
                {
                    currentRow = sheet.GetRow(Convert.ToInt32(reportTo.ResultRow - 1 + item.RowNumber - 1)) as HSSFRow;

                    //check if row exists
                    if (currentRow == null)
                    {
                        currentRow = sheet.CreateRow(Convert.ToInt32(reportTo.ResultRow - 1 + item.RowNumber - 1)) as HSSFRow;
                    }

                    currentCell = currentRow.GetCell(item.ColIndex - 1) as HSSFCell;

                    if (currentCell == null)
                    {
                        currentCell = currentRow.CreateCell(item.ColIndex - 1) as HSSFCell;
                    }

                    //hardcoded for the LIMS Template
                    //need to make it more generic
                    if (!item.ColIndex.Equals(8))
                    {
                        //Not a UWI
                        currentCell.SetCellValue(item.Value);
                    }
                    else
                    {
                        if (Regex.IsMatch(item.Value.ToUpper(), @"\d{2,3}/\d{2}-\d{2}-\d{3}-\d{2}W\d/\d{2}"))
                        {
                            Dictionary<string, string> uwi = UWI.ParseUWIAlberta(item.Value);

                            currentCell.SetCellValue(uwi["wellIdent"]);
                            currentRow.CreateCell(8).SetCellValue(uwi["wellLegalSub"]);
                            currentRow.CreateCell(9).SetCellValue(uwi["wellSection"]);
                            currentRow.CreateCell(10).SetCellValue(uwi["wellTownShip"]);
                            currentRow.CreateCell(11).SetCellValue(uwi["wellRange"]);
                            currentRow.CreateCell(12).SetCellValue(uwi["wellMeridian"]);
                        }
                    }

                }

                using (FileStream writer = new FileStream(path, FileMode.Create, FileAccess.Write))
                {
                    templateWorkbook.Write(writer);
                }

                sheet.Dispose();
                templateWorkbook.Dispose();
            }
        }

//.........这里部分代码省略.........
                        headStyle.SetFont(font);


                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                        }
                        //headerRow.Dispose();
                    }

                    #endregion

                    rowIndex = 2;
                }

                #endregion

                #region 填充内容

                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String": //字符串类型
                            double result;
                            if (isNumeric(drValue, out result))
                            {

                                double.TryParse(drValue, out result);
                                newCell.SetCellValue(result);
                                break;
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                                break;
                            }

                        case "System.DateTime": //日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle; //格式化显示
                            break;
                        case "System.Boolean": //布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16": //整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal": //浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull": //空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet.Dispose();
                workbook.Dispose();

                return ms;
            }
        }

//.........这里部分代码省略.........
            sheet = (HSSFSheet)workbook.CreateSheet(sheetname);
            #region 表头及样式
            {
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(strHeaderText);

                HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = (HSSFFont)workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new Region(0, 0, 0, dt.Columns.Count - 1));
                headerRow = null;
                //headerRow.Dispose();
            }
            #endregion

            #region 列头及样式
            {
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
                HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = (HSSFFont)workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                foreach (DataColumn column in dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable，将datatable的列名赋予sheet
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                    //设置列宽
                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                }
                headerRow = null;
            }
            #endregion

            int index = 2; //表头和列头已经占用一行，所以从2开始
            foreach (DataRow row in dt.Rows)
            {
                HSSFRow datarow = (HSSFRow)sheet.CreateRow(index);

                foreach (DataColumn column in dt.Columns)
                {
                    // row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet

                    HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); //　　实例化cell

                    string drValue = row[column].ToString();
                    if (drValue == null || drValue == "")
                    {
                        newCell.SetCellValue("");
                        continue;
                    }

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                        case "System.DateTime"://日期类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                        case "System.Float":
                        case "System.Single":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;

                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                index++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            //headerrow = null;
            //workbook = null;
            workbook.Dispose();
            return ms;
        }

    /// <summary>
    /// NPOI简单Demo，快速入门代码
    /// </summary>
    /// <param name="dtSource"></param>
    /// <param name="strFileName"></param>
    /// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
    /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
    public static void ExportEasy(DataTable dtSource, string strFileName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.CreateSheet();

        //填充表头
        Row dataRow = sheet.CreateRow(0);
        foreach (DataColumn column in dtSource.Columns)
        {
            dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
        }

        //填充内容
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            dataRow = sheet.CreateRow(i + 1);
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
            }
        }

        //保存
        using (MemoryStream ms = new MemoryStream())
        {
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
        workbook.Dispose();
    }

    public static DataTable Import(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
    {
        HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
        Sheet sheet = workbook.GetSheet(SheetName);

        DataTable table = new DataTable();

        Row headerRow = sheet.GetRow(HeaderRowIndex);
        int cellCount = headerRow.LastCellNum;

        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }

        int rowCount = sheet.LastRowNum;

        for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
        {
            Row row = sheet.GetRow(i);
            DataRow dataRow = table.NewRow();

            for (int j = row.FirstCellNum; j < cellCount; j++)
                dataRow[j] = row.GetCell(j).ToString();
        }

        ExcelFileStream.Close();
        workbook.Dispose();
        return table;
    }

//.........这里部分代码省略.........
                        cmd = new SqlCommand("STP_ImaFrequencyGet");
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@world_region_id", strRegionID);
                        cmd.Parameters.AddWithValue("@country_id", strCountryID);
                        cmd.Parameters.AddWithValue("@wowi_tech_id", liTechnology.Value);
                        dtFreq = SQLUtil.QueryDS(cmd).Tables[0];
                        ICell cell;
                        if (dtFreq.Rows.Count > 0)
                        {
                            int intTechnologyCategoryID = Convert.ToInt32(dtFreq.Rows[0]["TechnologyCategoryID"]);
                            int intShowColumn = 0;
                            int intCalcRow = 0;//記錄dtFreq跑迴圈到第幾筆資料
                            string[] strFreColumn = { "IsAllowedN", "PowerLimit", "DoorAllowed", "HT", "TPCDFS" };
                            //strTitle3 = dtFreq.Rows[0]["Title3"].ToString().Split(';');
                            intShowColumn = strTitle3.Length;
                            foreach (DataRow dr in dtFreq.Rows)
                            {
                                for (int l = strColumn.Length + 1 + intShowColumn * intCalcRow; l <= strColumn.Length + intShowColumn + intShowColumn * intCalcRow; l++)
                                {
                                    string strFreqValue = "";
                                    for (int intK = 0; intK <= intShowColumn - 1; intK++)
                                    {
                                        cell = row.CreateCell(l);
                                        strFreqValue = dr[strFreColumn.GetValue(intK).ToString()].ToString().Trim();
                                        if (strFreqValue.Contains("#br#"))
                                        {
                                            if (strFreqValue.Substring(0, 4) == "#br#")
                                            {
                                                strFreqValue = strFreqValue.Remove(0, 4);
                                                strFreqValue = strFreqValue.Replace("#br#", "\n");
                                            }
                                            else { strFreqValue = strFreqValue.Replace("#br#", "\n"); }
                                        }
                                        cell.SetCellValue(strFreqValue);
                                        cell.CellStyle = icsTxt;
                                        l++;
                                    }
                                }
                                intCalcRow++;
                            }
                            //判斷是否有Remark備註資料
                            if (Convert.ToBoolean(dtFreq.Rows[0]["IsRemark"]))
                            {
                                cell = row.CreateCell(strColumn.Length + intShowColumn * intCalcRow + 1);
                                cell.SetCellValue(dtFreq.Rows[0]["Remark"].ToString());
                                cell.CellStyle = icsTxt;
                            }
                        }
                        else
                        {
                            for (int l = strColumn.Length + 1; l <= intTitleIndex - 1; l++)
                            {
                                cell = row.CreateCell(l);
                                cell.CellStyle = icsTxt;
                            }
                        }
                    }
                    intRowIndex++;
                }
            }
        }

        //Add Export Flow by Adams 2012/11/12======================================================
        string ExportPathNoEncrypt =
          System.Configuration.ConfigurationManager.AppSettings["IMAExportPathNoEncrypt"].ToString();
        string ExportPathWithEncrypt =
          System.Configuration.ConfigurationManager.AppSettings["IMAExportPathWithEncrypt"].ToString();
        FileStream fs = new FileStream(ExportPathNoEncrypt + strFile, FileMode.Create);
        workbook.Write(fs);
        //workbook.Write(ms);
        //Response.BinaryWrite(ms.ToArray());
        sheet = null;
        workbook.Dispose();
        workbook = null;
        ms.Flush();
        ms.Position = 0;
        ms.Close();
        ms.Dispose();
        //base.Response.End();
        fs.Close();

        //產生加密的Excel檔
        string openPassword = ExcelEncrypt.GenerateRandomCode();
        string writePassword = ExcelEncrypt.GenerateRandomCode();
        ExcelEncrypt.EncryptExcelByPassword(ExportPathNoEncrypt + strFile,
          ExportPathWithEncrypt + strFile,
          openPassword,
          openPassword);
        //傳送MAIL
        string mailfrom = "[email protected]";
        string mailTo = System.Configuration.ConfigurationManager.AppSettings["IMAExportApprovor"].ToString();
        string mailSubject = "[IMA Notice]" + User.Identity.Name + " had Export IMA Document at " + DateTime.Now.ToString();
        string mailBody =
          "<br/> Dear Approver:" +
          "<br/> The password of IMA Export Excel File：" + openPassword +
          "<br/> Please refer to the attachment file";
        MailUtil.SendMailWithAttachment(mailfrom, mailTo, mailSubject, mailBody, ExportPathWithEncrypt + strFile);
        //Add Export Flow by Adams 2012/11/12======================================================
        Message.Text = "檔案 : " + strFile + " 匯出完成，請待主管審核確認!";
    }

//.........这里部分代码省略.........
                NPOI.SS.UserModel.ICell cell = row.CreateCell(count++);
                cell.SetCellValue(ds.Tables[0].Columns[i].ColumnName.ToString());
            }
            //将数据导入到excel表中
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rows = sheet.CreateRow(i + 22);
                count = 0;
                for (int j = 4; j < ds.Tables[0].Columns.Count; j++)
                {
                    if (ds.Tables[0].Columns[j].ColumnName != null)
                    {
                        NPOI.SS.UserModel.ICell cell = rows.CreateCell(count++);
                        Type type = ds.Tables[0].Rows[i][j].GetType();

                        if (type == typeof(int) || type == typeof(Int16)
                            || type == typeof(Int32) || type == typeof(Int64))
                        {
                            cell.SetCellValue((int)ds.Tables[0].Rows[i][j]);
                        }
                        else
                        {
                            if (type == typeof(float) || type == typeof(double) || type == typeof(Double))
                            {
                                cell.SetCellValue((Double)ds.Tables[0].Rows[i][j]);
                            }
                            else
                            {
                                if (type == typeof(DateTime))
                                {
                                    cell.SetCellValue(((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd HH:mm"));
                                }
                                else
                                {
                                    if (type == typeof(bool) || type == typeof(Boolean))
                                    {
                                        cell.SetCellValue((bool)ds.Tables[0].Rows[i][j]);
                                    }
                                    else
                                    {
                                        cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString());
                                    }
                                }
                            }
                        }
                    }
                }
            }

            NPOI.SS.UserModel.IRow totalInfo1Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 1);
            cellValue = totalInfo1Row.CreateCell(0);
            cellValue.SetCellValue("Total：");
            cellValue = totalInfo1Row.CreateCell(5);
            cellValue.SetCellValue(ds.Tables[0].Compute("SUM(间夜)", "").ToString());
            cellValue = totalInfo1Row.CreateCell(6);
            cellValue.SetCellValue(ds.Tables[0].Compute("SUM(卖价)", "").ToString());
            cellValue = totalInfo1Row.CreateCell(7);
            cellValue.SetCellValue(ds.Tables[0].Compute("SUM(底价)", "").ToString());
            cellValue = totalInfo1Row.CreateCell(8);
            cellValue.SetCellValue(ds.Tables[0].Compute("SUM(佣金)", "").ToString());

            //结算总价
            NPOI.SS.UserModel.IRow companyinfoRow = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 5);
            cellKey = companyinfoRow.CreateCell(0);
            cellKey.SetCellValue("我司汇款资料");

            NPOI.SS.UserModel.IRow companyinfo1Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 6);
            cellValue = companyinfo1Row.CreateCell(0);
            cellValue.SetCellValue("银行账号：");
            cellValue = companyinfo1Row.CreateCell(1);
            cellValue.SetCellValue("31001515100050024076");

            NPOI.SS.UserModel.IRow companyinfo2Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 7);
            cellValue = companyinfo2Row.CreateCell(0);
            cellValue.SetCellValue("开户银行：");
            cellValue = companyinfo2Row.CreateCell(1);
            cellValue.SetCellValue("中国建设银行股份有限公司上海长宁支行");

            NPOI.SS.UserModel.IRow companyinfo3Row = sheet.CreateRow(21 + ds.Tables[0].Rows.Count + 8);
            cellValue = companyinfo3Row.CreateCell(0);
            cellValue.SetCellValue("公司全称：");
            cellValue = companyinfo3Row.CreateCell(1);
            cellValue.SetCellValue("上海千栈网络信息科技有限公司");

            //保存excel文档
            sheet.ForceFormulaRecalculation = true;
            using (Stream filstream = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(filstream);
            }

            workbook.Dispose();

            return stream;
        }
        catch (Exception ex)
        {
            return new MemoryStream();
        }
    }

//.........这里部分代码省略.........
                        //cell = row3.CreateCell(0);
                        //cell.CellStyle = projectstyle;
                        //cell = row4.CreateCell(0);
                        //cell.CellStyle = projectstyle;
                        cell = row1.CreateCell(1);
                        cell.SetCellValue(user.UserName);
                        cell.CellStyle = nameStyle;
                        cell = row2.CreateCell(1);
                        cell.CellStyle = nameStyle;
                        cell = row3.CreateCell(1);
                        cell.CellStyle = nameStyle;
                        cell = row4.CreateCell(1);
                        cell.CellStyle = nameStyle;
                        cell = row1.CreateCell(2);
                        cell.SetCellValue("Work Done");
                        cell.CellStyle = itemStyle;
                        cell = row2.CreateCell(2);
                        cell.SetCellValue("Bug Created");
                        cell.CellStyle = itemStyle;
                        cell = row3.CreateCell(2);
                        cell.SetCellValue("Work Quality");
                        cell.CellStyle = itemStyle;
                        cell = row4.CreateCell(2);
                        cell.SetCellValue("Daily Performance");
                        cell.CellStyle = itemStyle;
                        range = new CellRangeAddress(count, count + 3, 1, 1);
                        sheet.AddMergedRegion(range);
                        int num = 0;
                        foreach (var item in user.MonthlyAppraisal)
                        {

                            cell = row1.CreateCell(num + 3);
                            if (item.WorkDone != 0)
                            {
                                cell.SetCellValue((Double)item.WorkDone);
                                cell.CellStyle = datestyle;
                            }
                            else
                            {
                                cell.CellStyle = emptyStyle;
                            }
                            cell = row2.CreateCell(num + 3);
                            if (item.BugCreated != 0)
                            {
                                cell.SetCellValue((Double)item.BugCreated);
                                cell.CellStyle = datestyle;
                            }
                            else
                            {
                                cell.CellStyle = emptyStyle;
                            }
                            cell = row3.CreateCell(num + 3);
                            if (item.WorkQuality != 0)
                            {
                                cell.SetCellValue((Double)item.WorkQuality);
                                cell.CellStyle = datestyle;
                            }
                            else
                            {
                                cell.CellStyle = emptyStyle;
                            }
                            cell = row4.CreateCell(num + 3);
                            if (item.DailyPerformance != 0)
                            {
                                cell.SetCellValue((Double)item.DailyPerformance);
                                cell.CellStyle = datestyle;
                            }
                            else
                            {
                                cell.CellStyle = emptyStyle;
                            }
                            num++;
                        }
                        count += 4;
                    }
                    range = new CellRangeAddress(startCount, count - 1, 0, 0);
                    sheet.AddMergedRegion(range);
                }

                row = sheet.CreateRow(count);
                int m = date.Count;
                for (int i = 0; i < m + 3; i++)
                {
                    cell = row.CreateCell(i);
                    cell.CellStyle = lastLineStyle;
                }

                //保存excel文档
                sheet.ForceFormulaRecalculation = true;

                workbook.Write(stream);
                workbook.Dispose();

                return stream;
            }
            catch
            {
                return new MemoryStream();
            }
        }

        public static MemoryStream ExportDatasetToExcel(List<Tuple<string, List<Tuple<string, string>>>> list)
        {
            try
            {
                //File stream object
                MemoryStream stream = new MemoryStream();

                //Open Excel object
                HSSFWorkbook workbook = new HSSFWorkbook();
                //Head Style
                CellStyle headstyle = workbook.CreateCellStyle();
                //Alignment
                headstyle.Alignment = HorizontalAlignment.CENTER;
                headstyle.VerticalAlignment = VerticalAlignment.CENTER;

                //Font
                Font headfont = workbook.CreateFont();
                headfont.FontHeightInPoints = 12;
                headfont.Boldweight = short.MaxValue;
                headstyle.SetFont(headfont);
                //Background color
                headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                headstyle.FillPattern = FillPatternType.SQUARES;
                headstyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                //Border
                headstyle.BorderTop = CellBorderType.THIN;
                headstyle.BorderRight = CellBorderType.THIN;
                headstyle.BorderBottom = CellBorderType.THIN;
                headstyle.BorderLeft = CellBorderType.THIN;

                //Body Style
                CellStyle bodystyle = workbook.CreateCellStyle();
                //Border
                bodystyle.BorderTop = CellBorderType.THIN;
                bodystyle.BorderRight = CellBorderType.THIN;
                bodystyle.BorderBottom = CellBorderType.THIN;
                bodystyle.BorderLeft = CellBorderType.THIN;
                //Line Feed
                bodystyle.WrapText = true;
                //Sheet's object of Excel
                Sheet sheet = workbook.CreateSheet("sheet1");
                sheet.SetColumnWidth(0, 5000);
                sheet.SetColumnWidth(1, 100000);
                ////set date format
                //CellStyle cellStyleDate = workbook.CreateCellStyle();
                //DataFormat format = workbook.CreateDataFormat();
                //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");

                //Export to Excel
                Row row;
                Cell cell;
                //cell.SetCellType();
                int count = 0;
                foreach (var l in list)
                {
                    if (l.Item2.Count != 0)
                    {
                        row = sheet.CreateRow(count);
                        row.HeightInPoints = 20;
                        cell = row.CreateCell(0);
                        cell.CellStyle = headstyle;
                        cell.SetCellValue(l.Item1);
                        cell = row.CreateCell(1);
                        cell.CellStyle = headstyle;
                        CellRangeAddress range = new CellRangeAddress(count, count, 0, 1);
                        sheet.AddMergedRegion(range);
                        count++;
                        foreach (var item in l.Item2)
                        {
                            row = sheet.CreateRow(count);
                            cell = row.CreateCell(0);
                            cell.SetCellValue(item.Item1);
                            cell.CellStyle = bodystyle;
                            cell = row.CreateCell(1);
                            cell.SetCellValue(item.Item2);
                            cell.CellStyle = bodystyle;
                            count++;
                        }
                    }
                }

                //Save excel
                sheet.ForceFormulaRecalculation = true;

                workbook.Write(stream);
                workbook.Dispose();

                return stream;
            }
            catch
            {
                return new MemoryStream();
            }
        }

//.........这里部分代码省略.........

                //Name Style
                CellStyle namestyle = workbook.CreateCellStyle();
                //Alignment
                namestyle.Alignment = HorizontalAlignment.CENTER;
                namestyle.VerticalAlignment = VerticalAlignment.CENTER;
                //Font
                Font namefont = workbook.CreateFont();
                namefont.FontHeightInPoints = 11;
                namefont.FontName = "Calibri";
                namefont.Boldweight = (short)FontBoldWeight.BOLD;
                namestyle.SetFont(namefont);
                //Border
                namestyle.BorderTop = CellBorderType.THIN;
                namestyle.BorderRight = CellBorderType.THIN;
                namestyle.BorderBottom = CellBorderType.THIN;
                namestyle.BorderLeft = CellBorderType.THIN;

                //Body Style
                CellStyle bodystyle = workbook.CreateCellStyle();
                //Alignment
                bodystyle.VerticalAlignment = VerticalAlignment.CENTER;
                //Font
                Font bodyfont = workbook.CreateFont();
                bodyfont.FontHeightInPoints = 12;
                bodyfont.FontName = "Times New Roman";
                bodystyle.SetFont(bodyfont);
                //Border
                bodystyle.BorderTop = CellBorderType.THIN;
                bodystyle.BorderRight = CellBorderType.THIN;
                bodystyle.BorderBottom = CellBorderType.THIN;
                bodystyle.BorderLeft = CellBorderType.THIN;
                //Line Feed
                bodystyle.WrapText = true;
                //Sheet's object of Excel
                Sheet sheet = workbook.CreateSheet("sheet1");
                sheet.SetColumnWidth(0, (short)(35.7 * 160));
                sheet.SetColumnWidth(1, (short)(35.7 * 400));
                sheet.SetColumnWidth(2, (short)(35.7 * 600));

                //Export to Excel
                Row row;
                Cell cell;
                //cell.SetCellType();
                int count = 2;

                row = sheet.CreateRow(0);
                row.HeightInPoints = 20;
                cell = row.CreateCell(0);
                cell.CellStyle = titlestyle;
                cell.SetCellValue("Staff Report");
                cell = row.CreateCell(1);
                cell.CellStyle = titlestyle;
                cell = row.CreateCell(2);
                cell.CellStyle = titlestyle;
                CellRangeAddress range = new CellRangeAddress(0, 0, 0, 2);
                sheet.AddMergedRegion(range);

                row = sheet.CreateRow(1);
                row.HeightInPoints = 20;
                cell = row.CreateCell(0);
                cell.CellStyle = headstyle;
                cell = row.CreateCell(1);
                cell.CellStyle = headstyle;
                cell.SetCellValue(" What you worked on today?");
                cell = row.CreateCell(2);
                cell.CellStyle = headstyle;
                cell.SetCellValue(" What you will be working on tomorrow?");

                foreach (var item in list)
                {
                    row = sheet.CreateRow(count);

                    cell = row.CreateCell(0);
                    cell.SetCellValue(item.Item1);
                    cell.CellStyle = namestyle;

                    cell = row.CreateCell(1);
                    cell.SetCellValue(item.Item2);
                    cell.CellStyle = bodystyle;
                    cell = row.CreateCell(2);
                    cell.SetCellValue(item.Item3);
                    cell.CellStyle = bodystyle;

                    count++;
                }

                //Save excel
                sheet.ForceFormulaRecalculation = true;

                workbook.Write(stream);
                workbook.Dispose();

                return stream;
            }
            catch
            {
                return new MemoryStream();
            }
        }