NPOI新建和读取EXCEL

时间：2021-05-09 18:52:32
        //基本NPOI 1.2.5.0

        static void Main(string[] args)

        {

            string path = string.Format("E:\\export{0}.xls", DateTime.Now.ToString("yyyyMMddhhmmss"));

            WriteAExcel(path);

            ReadAExcel(path);

            Console.ReadKey();

        }
        /// <summary>

        /// 创建测试

        /// </summary>

        /// <param name="path">路径</param>

        static void WriteAExcel(string path)

        {

            //创建工作薄

            HSSFWorkbook workbook = new HSSFWorkbook();

            //创建一个名称为"排班表"的表

            ISheet sheet = workbook.CreateSheet("排班表");

            int rowCount = ;

            int colCount = ;

            //创建一行, 此行为标题行

            IRow title = sheet.CreateRow(rowCount);

            title.CreateCell().SetCellValue(string.Format("{0}({1})", "消化内科", "珠海市人民医院"));

            //合并单元格

            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount, , colCount - );

            sheet.AddMergedRegion(cellRangeAddress);

            rowCount++;

            //创建一行, 空行

            sheet.CreateRow(rowCount);

            rowCount++;

            //创建一行，此行为第二行

            IRow headerRow = sheet.CreateRow(rowCount);

            rowCount++;

            //固定区域, 用于header

            sheet.CreateFreezePane(, );

            string[] headerArray = new[] { "医生", "日期", "时间", "预约数", "挂号费", "状态" };

            //表头行

            for (int i = ; i < headerArray.Length; i++)

            {

                headerRow.CreateCell(i).SetCellValue(headerArray[i]);

            }

            List<MyDataItem> dataList = new List<MyDataItem>();

            #region 测试数据

            dataList.Add(new MyDataItem() { ID = , DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "停诊" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "停诊" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "停诊" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = , Fee = , Status = "停诊" });

            dataList.Add(new MyDataItem() { ID = , DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = , Fee = , Status = "排班" });

            #endregion

            //添加下拉选项(序列)

            AddDropdownList1(sheet, rowCount);

            //添加下拉选项(指定数据)

            AddDropdownList2(workbook, sheet, rowCount);

            //插入数据

            for (int i = ; i < dataList.Count; i++)

            {

                MyDataItem item = dataList[i];

                IRow dataRow = sheet.CreateRow(rowCount);

                dataRow.CreateCell().SetCellValue(string.Format("{0}({1})", item.DoctorName, item.ID));

                dataRow.CreateCell().SetCellValue(item.Date.ToString("yyyy/MM/dd"));

                dataRow.CreateCell().SetCellValue(item.Time);

                dataRow.CreateCell().SetCellValue(item.Place);

                dataRow.CreateCell().SetCellValue(item.Fee.ToString("N2"));

                dataRow.CreateCell();//.SetCellValue(item.Status);

                rowCount++;

            }

            //写入文件

            using (MemoryStream ms = new MemoryStream())

            {

                workbook.Write(ms);

                using (FileStream fs = new FileStream(path, FileMode.Create))

                {

                    byte[] data = ms.ToArray();

                    fs.Write(data, , data.Count());

                }

            }

        }
        //读测试

        static void ReadAExcel(string path)

        {

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

            {

                IWorkbook workbook = new HSSFWorkbook(file);

                ISheet sheet = workbook.GetSheet("排班表");

                IRow headerRow = sheet.GetRow();

                //一行最后一个方格的编号 即总的列数

                int cellCount = headerRow.LastCellNum;

                //有多少列

                int rowCount = sheet.LastRowNum;

                //读

                for (int i = ; i < rowCount; i++)

                {

                    IRow row = sheet.GetRow(i);

                    for (int j = row.FirstCellNum; j < cellCount; j++)

                    {

                        Console.Write(" " + row.GetCell(j).ToString());

                    }

                    Console.WriteLine();

                }

            }

        }
        /// <summary>

        /// 添加下拉框(序列)

        /// </summary>

        /// <param name="sheet"></param>

        /// <param name="start"></param>

        static void AddDropdownList1(ISheet sheet, int start)

        {

            CellRangeAddressList regions = new CellRangeAddressList(start, , , );

            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "就诊", "停诊" });

            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

            sheet.AddValidationData(dataValidate);

        }

        /// <summary>

        /// 添加下拉框(单元格)

        /// </summary>

        /// <param name="workbook"></param>

        /// <param name="sheet"></param>

        /// <param name="start"></param>

        static void AddDropdownList2(HSSFWorkbook workbook, ISheet sheet, int start)

        {

            ISheet sheet2 = workbook.CreateSheet("a");

            sheet2.CreateRow().CreateCell().SetCellValue("上午");

            sheet2.CreateRow().CreateCell().SetCellValue("中午");

            sheet2.CreateRow().CreateCell().SetCellValue("下午");

            sheet2.CreateRow().CreateCell().SetCellValue("晚上");

            IName range = workbook.CreateName();

            range.RefersToFormula = "a!$A$1:$A$4";

            range.NameName = "timeDic";

            CellRangeAddressList regions = new CellRangeAddressList(start, , , );

            DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("timeDic");

            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

            //添加约束警告

            dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");

            sheet.AddValidationData(dataValidate);

        }
        /// <summary>

        /// 测试数据类型

        /// </summary>

        class MyDataItem

        {

            public int ID { get; set; }

            public string DoctorName { get; set; }

            public DateTime Date { get; set; }

            public string Time { get; set; }

            public int Place { get; set; }

            public decimal Fee { get; set; }

            public string Status { get; set; }

        }

备忘:

//2014/10/08

//加粗,15字

IFont Bold15Font = workbook.CreateFont();

Bold15Font.Boldweight = (short)FontBoldWeight.BOLD;

Bold15Font.FontHeightInPoints = ;

//黄色底,横向居中,纵向居中,加粗,11字, 边框(单个)

ICellStyle YellowCenterBold11Cell = workbook.CreateCellStyle();

YellowCenterBold11Cell.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index;

YellowCenterBold11Cell.FillPattern = FillPatternType.SOLID_FOREGROUND;

YellowCenterBold11Cell.Alignment = HorizontalAlignment.CENTER;

YellowCenterBold11Cell.VerticalAlignment = VerticalAlignment.CENTER;

YellowCenterBold11Cell.SetFont(Bold11Font);

YellowCenterBold11Cell.BorderBottom = BorderStyle.THIN;

YellowCenterBold11Cell.BorderLeft = BorderStyle.THIN;

YellowCenterBold11Cell.BorderRight = BorderStyle.THIN;

YellowCenterBold11Cell.BorderTop = BorderStyle.THIN;

//给合并单元格加边框

CellRangeAddress cellRangeAddress0 = new CellRangeAddress(, , , );

sheet.AddMergedRegion(cellRangeAddress0);

 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRangeAddress0, BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

//单元格宽

sheet.SetColumnWidth(,  * );

//行高

row0.HeightInPoints = Height25;
//2014/10/09
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

IRow row1 = sheet1.CreateRow();

ICell cel1 = row1.CreateCell();

ICell cel2 = row1.CreateCell();

ICellStyle unlocked = hssfworkbook.CreateCellStyle();

unlocked.IsLocked = false;

ICellStyle locked = hssfworkbook.CreateCellStyle();

locked.IsLocked = true;//确定当前单元格被设置保护

cel1.SetCellValue("没被锁定");

cel1.CellStyle = unlocked;

cel2.SetCellValue("被锁定");

cel2.CellStyle = locked;

sheet1.ProtectSheet("password");//设置密码保护
sheet1.AutoSizeColumn(i);//自动宽度(不支持中文)




自动宽度( 支持中文)




　　　　 /// <summary>

        /// 自动宽度支持中文

        /// </summary>

        public static void AutoSizeColumnExtension(this ISheet sheet, int maxColumn)

        {

            for (int i = ; i < maxColumn; i++)

            {

                sheet.AutoSizeColumn(i);

            }

            //获取当前列的宽度，然后对比本列的长度，取最大值

            for (int columnNum = ; columnNum < maxColumn; columnNum++)

            {

                int columnWidth = sheet.GetColumnWidth(columnNum) / ;

                for (int rowNum = ; rowNum <= sheet.LastRowNum; rowNum++)

                {

                    IRow currentRow;

                    //当前行未被使用过

                    if (sheet.GetRow(rowNum) == null)

                    {

                        currentRow = sheet.CreateRow(rowNum);

                    }

                    else

                    {

                        currentRow = sheet.GetRow(rowNum);

                    }

                    if (currentRow.GetCell(columnNum) != null)

                    {

                        ICell currentCell = currentRow.GetCell(columnNum);

                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;

                        if (columnWidth < length)

                        {

                            columnWidth = length;

                        }

                    }

                }

                sheet.SetColumnWidth(columnNum, (columnWidth >  ?  : columnWidth) * );

            }

        }




更多可以查看官方手册:http://tonyqus.sinaapp.com/
相关文章