用NPOI、C#操作Excel表格生成班级成绩单

转：https://blog.csdn.net/dcrmg/article/details/52374194#commentBox

在C#中利用NPOI操作Excel表格非常方便，几乎上支持所有的Excel表格本身所有的功能，如字体设置、颜色设置、单元格合并、数值计算、页眉页脚等等。

这里准备使用NPOI生成一个班级成绩单Excel表格，表格中包含的信息包括学号、姓名、各科成绩、平均成绩、排名等。

实现原理很简单，主要是NPOI的一些操作，具体实现的功能包括下边几个：

 

• 单元格合并
• 字体大小、颜色设置
• 背景颜色设置
• 边框粗细设置
• 多个单元格SUM求和
• 数据写入和读取

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
 
namespace Score_Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();//声明工作簿对象，可以创建xls或xlsx Excel文件
            ISheet sheet1 = workbook.CreateSheet("Score Record"); //创建工作表
            ICell sheet1Title = sheet1.CreateRow(0).CreateCell(0); //创建第一行第一个单元格
            sheet1Title.SetCellValue("国家体育总局附属二小幼儿园小一班体育成绩表"); //表头
            sheet1Title.CellStyle = GetTitleCellStyle(workbook);
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));  //合并单元格
            DataTable dt = GetData();
            IRow row;
            ICell cell;
            ICellStyle cellStyle1 = GetCellStyle(workbook, 2);
            ICellStyle cellStyle2 = GetCellStyle(workbook, 0);
            double[] aveScore = new double[8]; //平均成绩数组
            int[] rankNum = new int[8];  //名次数组
 
            //表头数据
            row = sheet1.CreateRow(1);
            cell = row.CreateCell(0);
            cell.SetCellValue("学号");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(1);
            cell.SetCellValue("姓名");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(2);
            cell.SetCellValue("排球");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(3);
            cell.SetCellValue("乒乓球");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(4);
            cell.SetCellValue("跳水");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(5);
            cell.SetCellValue("举重");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(6);
            cell.SetCellValue("自由泳");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(7);
            cell.SetCellValue("体操");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(8);
            cell.SetCellValue("平均成绩");
            cell.CellStyle = cellStyle1;
 
            cell = row.CreateCell(9);
            cell.SetCellValue("名次");
            cell.CellStyle = cellStyle1;
 
            // 写入数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dataR = dt.Rows[i];
                row = sheet1.CreateRow(i + 2);
 
                cell = row.CreateCell(0);
                cell.SetCellValue(dataR["学号"].ToString());
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(1);
                cell.SetCellValue(dataR["姓名"].ToString());
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(2);
                cell.SetCellValue((Double)dataR["排球"]);
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(3);
                cell.SetCellValue((Double)dataR["乒乓球"]);
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(4);
                cell.SetCellValue((Double)dataR["跳水"]);
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(5);
                cell.SetCellValue((Double)dataR["举重"]);
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(6);
                cell.SetCellValue((Double)dataR["自由泳"]);
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(7);
                cell.SetCellValue((Double)dataR["体操"]);
                cell.CellStyle = cellStyle2;
 
                cell = row.CreateCell(8);
                cell.SetCellFormula(String.Format("SUM($C{0}:$H{0})/6", i + 3));
                cell.CellStyle = cellStyle2;
 
                for (int j = 2; j < 8; j++)
                {
                    aveScore[i] += row.Cells[j].NumericCellValue;
                }
                aveScore[i] /= 6;  //每个人平均成绩
            }
 
            //以下for循环实现对每个人的成绩进行排名
            for (int i = 0; i < 8; i++)
            {
                rankNum[i] = 1;
                for (int j = 0; j < 8; j++)
                {
                    if (aveScore[i] < aveScore[j])
                    {
                        rankNum[i]++;
                    }
                }
            }
 
            //排名写入“名次”列
            for (int i = 0; i < 8; i++)
            {
                row = sheet1.GetRow(i + 2);
                cell = row.CreateCell(9);
                cell.SetCellValue(rankNum[i]);
                cell.CellStyle = cellStyle2;
            }
 
            if (!Directory.Exists(@"E:\Score Excel"))  //检查是否存在文件夹，不存在则新建
            {
                Directory.CreateDirectory(@"E:\Score Excel");
            }
 
            FileStream file = new FileStream(@"E:\Score Excel\Score Record.xls", FileMode.Create);
            workbook.Write(file);
            file.Close();
            workbook.Close();
        }
 
 
        static DataTable GetData()   //原始数据
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("学号", typeof(System.Int32));
            dt.Columns.Add("姓名", typeof(System.String));
            dt.Columns.Add("排球", typeof(System.Double));
            dt.Columns.Add("乒乓球", typeof(System.Double));
            dt.Columns.Add("跳水", typeof(System.Double));
            dt.Columns.Add("举重", typeof(System.Double));
            dt.Columns.Add("自由泳", typeof(System.Double));
            dt.Columns.Add("体操", typeof(System.Double));
 
            dt.Rows.Add("231603001", "张继科", 100, 83, 69.5, 90, 61, 92);
            dt.Rows.Add("231603002", "傅园慧", 99, 100, 99.9, 100, 100, 99.5);
            dt.Rows.Add("231603003", "孙杨", 92, 64, 78.5, 64, 69, 90.5);
            dt.Rows.Add("231603004", "福原爱", 76, 93.5, 69.5, 85, 87, 61);
            dt.Rows.Add("231603005", "大魔王", 99, 102, 92, 78, 96.5, 89.5);
            dt.Rows.Add("231603006", "林丹", 87, 98.5, 78.5, 69.5, 97, 89);
            dt.Rows.Add("231603007", "丁宁", 85, 93, 87.5, 90.5, 69, 79.5);
            dt.Rows.Add("231603008", "宁泽涛", 79, 62.5, 87.5, 98, 78, 93.5);
            return dt;
        }
 
        //设置单元格格式函数，边框粗细3个可选
        static ICellStyle GetCellStyle(IWorkbook workbook, int borderThickness)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            NPOI.SS.UserModel.BorderStyle borderType;
            switch (borderThickness)
            {
                case 0:
                    borderType = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
                case 1:
                    borderType = NPOI.SS.UserModel.BorderStyle.Medium;
                    break;
                case 2:
                    borderType = NPOI.SS.UserModel.BorderStyle.Thick;
                    break;
                default:
                    borderType = NPOI.SS.UserModel.BorderStyle.Thin;
                    break;
            }
            cellStyle.BorderBottom = borderType;
            cellStyle.BorderTop = borderType;
            cellStyle.BorderLeft = borderType;
            cellStyle.BorderRight = borderType;
 
            IFont font = workbook.CreateFont();//设置字体大小和颜色
            font.FontName = "宋体";
            font.FontHeightInPoints = 13;
            cellStyle.SetFont(font);
            return cellStyle;
        }
 
        //设置表头格式函数
        static ICellStyle GetTitleCellStyle(IWorkbook workbook)
        {
            ICellStyle cell1Style = workbook.CreateCellStyle();
            cell1Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cell1Style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cell1Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cell1Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cell1Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cell1Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
 
            IFont font = workbook.CreateFont(); //设置字体大小和颜色
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 13;
            font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            cell1Style.SetFont(font);
            cell1Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
            cell1Style.FillPattern = FillPattern.SolidForeground;
            return cell1Style;
        }
    }
}

　　

 

 

 

执行后，在E盘指定目录下生成了名字是“Score Excel”的表格：

 

 

“名次”列的排名实现：

先声明了一个大小为8的Int数组，默认值设为1，依次拿当前的平均成绩和其他7个的平均成绩对比，有几个大于当前平均成绩的元素，就在当前数组值上加上几，最后得到的就是每个人的排名，实现如下：

//以下for循环实现对每个人的成绩进行排名
            for (int i = 0; i < 8; i++)
            {
                rankNum[i] = 1;
                for (int j = 0; j < 8; j++)
                {
                    if (aveScore[i] < aveScore[j])
                    {
                        rankNum[i]++;
                    }
                }
            }

　　

 

// NPOI excel 流导出

public void ExportMNewReportExcel(int org_info_id, string user_name, string declare_month, string customer_name, string ufsoft_account)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            //Excel文件的摘要信息  
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "blog.csdn.net";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Export Excel";
            hssfworkbook.SummaryInformation = si;

            //列名
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

            IRow row0 = sheet1.CreateRow(0);
            row0.CreateCell(0).SetCellValue("客户名称");  
            row0.CreateCell(4).SetCellValue("基本税种");
            row0.CreateCell(5).SetCellValue("征收方式"); 
            row0.CreateCell(11).SetCellValue("税号");
            row0.CreateCell(12).SetCellValue("税局");
            row0.CreateCell(13).SetCellValue("专管员");
            row0.CreateCell(14).SetCellValue("业务员");

            //list = rep.GetList(org_info_id, "", pageIndex, pageSize, out total,param);

            //    var am = new AmArchStockRepository().GetList().Where(x => x.location_no == user_name).ToList();

            //    resultList = list.Select(r => new
            //    {
            //        data = r,
            //        flag = am.Where(x => x.customer_info_id == r.customer_info_id).Count() > 0 ? "是" : "否"
            //    });

            IQueryable<CmCustomerInfo> queryable = null;

            var cm_rep = new CmCustomerInfoRepository();
            if (!string.IsNullOrEmpty(declare_month))
            {
                queryable = cm_rep.GetList(x => x.org_info_id == org_info_id && x.declare_month == declare_month, includeProperties: "SmUser2");
            }
            if (!string.IsNullOrEmpty(customer_name))
            {
                queryable = queryable.Where(x => x.customer_name.Contains(customer_name));
            }
            if (!string.IsNullOrEmpty(ufsoft_account))
            {
                queryable = queryable.Where(x => x.ufsoft_account.Contains(ufsoft_account));
            }

            var list = queryable.ToList();

            var am_list = new AmArchStockRepository().GetList().Where(x => x.location_no == user_name ).ToList();

            if (list != null && list.Count > 0)
            {
                int index = 0;
                foreach (var item in list)
                {
                    index++;
                    IRow excelRow = sheet1.CreateRow(index);
                    excelRow.CreateCell(0).SetCellValue(item.customer_name);
                    string isOrfalse = am_list.Where(x => x.customer_info_id == item.customer_info_id).Count() > 0 ? "是" : "否";
                    excelRow.CreateCell(1).SetCellValue(isOrfalse);
                    excelRow.CreateCell(2).SetCellValue(item.ufsoft_account);
                    excelRow.CreateCell(3).SetCellValue(item.declarer_machine);
                    excelRow.CreateCell(4).SetCellValue(item.tax_type);
                    excelRow.CreateCell(5).SetCellValue(item.tax_levy_method);
                    excelRow.CreateCell(6).SetCellValue(item.accounting_category);
                    excelRow.CreateCell(7).SetCellValue(item.enterprise_type);
                    excelRow.CreateCell(8).SetCellValue(item.tax_invoice_method);
                    excelRow.CreateCell(9).SetCellValue(item.bank_bill_method);
                    excelRow.CreateCell(10).SetCellValue(item.personnel_agency);
                    excelRow.CreateCell(11).SetCellValue(item.tax_code);
                    excelRow.CreateCell(12).SetCellValue(item.tax_bureau);
                    excelRow.CreateCell(13).SetCellValue(item.tax_contact);
                    excelRow.CreateCell(14).SetCellValue(item.sales_name);
                }

                //设置excel单元格的宽度 1*256代表一个字符
                sheet1.SetColumnWidth(0, 30 * 256);
                sheet1.SetColumnWidth(1, 12 * 256);
                sheet1.SetColumnWidth(2, 13 * 256);
                sheet1.SetColumnWidth(3, 10 * 256);
                sheet1.SetColumnWidth(4, 15 * 256);
                sheet1.SetColumnWidth(5, 10 * 256);
                sheet1.SetColumnWidth(6, 10 * 256);
                sheet1.SetColumnWidth(7, 10 * 256);
                sheet1.SetColumnWidth(8, 20 * 256);
                sheet1.SetColumnWidth(9, 20 * 256);
                sheet1.SetColumnWidth(10, 20 * 256);
                sheet1.SetColumnWidth(11, 20 * 256);
                sheet1.SetColumnWidth(12, 20 * 256);
                sheet1.SetColumnWidth(13, 20 * 256);
                sheet1.SetColumnWidth(14, 20 * 256);
                MemoryStream ms = new MemoryStream();
                hssfworkbook.Write(ms);

                //asp.net输出的Excel文件名  
                //如果文件名是中文的话，需要进行编码转换，否则浏览器看到的下载文件是乱码。  
                string fileName = HttpUtility.UrlEncode(string.Format("{0}_{1}"
                    , "月新报表"
                    , DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"));

                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                //Response.ContentType = "application/download"; //也可以设置成download  
                HttpContext.Current.Response.Charset = Encoding.UTF8.BodyName;
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
                HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
                HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
                HttpContext.Current.Response.End();
            }
        }