开放原子开发者工作坊

/// <summary>
        /// 应用开源NPOI，导出Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnNPOIExport_Click(object sender, EventArgs e)
        {
            if (!File.Exists(sExePath + "NPOI.dll"))
            {
                MessageBox.Show("导出所需动态链接库NPOI.dll不存在，不支持导出。", "提示");
                return;
            }
            if (!File.Exists(sExePath + sExcelName))
            {
                MessageBox.Show("DataUsageReportingFileSample.xls模板文件不存在，请确认与.EXE同路径下包含此文件。", "提示");
                return;
            }

            // 填充数据
            using (SaveFileDialog saveExcel = new SaveFileDialog())
            {
                saveExcel.Filter = "Excel文件 (*.xls)|*.xls";
                string sNewFileName = string.Empty;
                if (saveExcel.ShowDialog() == DialogResult.OK)
                {
                    sNewFileName = saveExcel.FileName;
                    // 文件已被打开，则提示关闭
                    if (CFileHasOpened.FileHasOpen(sNewFileName))
                    {
                        MessageBox.Show("文件已被打开，请关闭后再重试保存。", "提示");
                        return;
                    }
                    // 复制模板，以后的操作都在复制的文件上进行
                    File.Copy(sExePath + sExcelName, sNewFileName, true);

                    InitializeWorkbook(sNewFileName);
                    if (null == hssfworkbook)
                    { return; }
                    ISheet modelSheet = hssfworkbook.GetSheet("Market Data Usage");
                    // 单元格格式
                    ICellStyle CenterStyle = hssfworkbook.CreateCellStyle();
                    CenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                    if (null == modelSheet)
                    { return; }
                    if (null == QueriedPermissions)
                    { return; }

                    for (Int32 rowIndex = 0; rowIndex < QueriedPermissions.Count; rowIndex++)
                    {
                        IRow tmpRow = modelSheet.GetRow(rowIndex + 5);
                        if (null == tmpRow)
                        { continue; }
                        for (Int32 colIndex = 0; colIndex < QueriedPermissions[rowIndex].Count; colIndex++)
                        {
                            ICell tmpCell = tmpRow.GetCell(colIndex);
                            if (null == tmpCell)
                            { continue; }
                            if (colIndex < 7)
                            {
                                tmpCell.SetCellValue(QueriedPermissions[rowIndex][colIndex].ToString().Trim());
                            }
                            else
                            {
                                tmpCell.SetCellValue(Convert.ToInt32(QueriedPermissions[rowIndex][colIndex].ToString().Trim()));
                                //tmpCell.CellStyle = CenterStyle;
                            }
                        }
                    }

                    //Force <a href="http://www.it165.net/edu/ebg/" target="_blank" class="keylink">excel</a> to recalculate all the formula while open
                    modelSheet.ForceFormulaRecalculation = true;

                    WriteToExcelWithNPOI(sNewFileName);
                }
            }
        }

        private static HSSFWorkbook hssfworkbook;
        /// <summary>
        /// 初始化工作簿
        /// </summary>
        private void InitializeWorkbook(string sNewFileName)
        {
            FileStream file = new FileStream(sNewFileName, FileMode.Open, FileAccess.Read);
            if (null == file)
            { return; }
            hssfworkbook = new HSSFWorkbook(file);
            if (null == hssfworkbook)
            { return; }
            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "test";
            hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "test";
            hssfworkbook.SummaryInformation = si;
        }

        /// <summary>
        /// 把工作簿写到本地文件
        /// </summary>
        private void WriteToExcelWithNPOI(string sNewFileName)
        {
            FileStream file = new FileStream(sNewFileName, FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }