NPOI导出Excel百分比格式

NPOI导出Excel百分比格式

using

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

代码实现

        public int DataTableToExcel(string fileName, DataTable[] datas, bool isColumnWritten)
        {
            ISheet sheet = null;

            FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workbook = null;

            if (fileName.IndexOf(".xlsx") > 0) // 2007版本  
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本  
                workbook = new HSSFWorkbook();

            if (workbook == null)
            {
                return -1;
            }

            //百分比格式
            ICellStyle cellStylePercent = workbook.CreateCellStyle();
            cellStylePercent.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            int step = 0;

            for (int n = 0; n < datas.Length; n++)
            {
                int count = 0;
                DataTable data = datas[n];
                sheet = workbook.CreateSheet(data.TableName);


                if (isColumnWritten == true) //写入DataTable的列名  
                {
                    IRow row = sheet.CreateRow(0);
                    for (int j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count++;
                }

                for (int i = 0; i < data.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(count);
                    for (int j = 0; j < data.Columns.Count; ++j)
                    {
                        if (data.Rows[i][j] is double || data.Rows[i][j] is int)
                        {
                            if (data.Rows[i][j] is int)
                            {
                                row.CreateCell(j).SetCellValue(Convert.ToDouble((int)data.Rows[i][j]));
                            }
                            else
                            {
                                row.CreateCell(j).SetCellValue((double)data.Rows[i][j]);
                            }
                            if (data.Columns[j].ColumnName=="列名")//需要百分比格式的列名
                            {
                                row.Cells[j].CellStyle = cellStylePercent;
                            }
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                    }
                    ++count;
                }
                //调整列宽
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    sheet.AutoSizeColumn(j);

                    if (sheet.GetColumnWidth(j) < 2048)
                    {
                        sheet.SetColumnWidth(j, 2048);
                    }
                }
                Console.WriteLine("写入" + data.TableName + "成功");
            }
            using (fs)
            {
                workbook.Write(fs); //写入到excel  
            }