Winform中通过NPOI导出Excel时通过ICellStyle和IDataFormat格式化日期显示格式

桃扇骨 2023-10-06 10:01 34阅读 0赞

## 场景 ##

Winform中通过NPOI导出Excel的三种方式(HSSFWorkbook，XSSFWorkbook，SXSSFWorkbook)附代码下载：

[https://blog.csdn.net/BADAO\_LIUMANG\_QIZHI/article/details/106423452][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_106423452]

在上面介绍了NPOI的三种导出Excel的方式后，如果想在导出的Excel中添加照片，该怎样实现。

注：

博客主页：  
[https://blog.csdn.net/badao\_liumang\_qizhi ][https_blog.csdn.net_badao_liumang_qizhi]  
关注公众号  
霸道的程序猿  
获取编程相关电子书、教程推送与免费下载。

## 实现 ##

为了构建导出数据，新建一个类

public class DataItem
        {
    
            public int Age { get; set; }
    
    
            public string Name { get; set; }
    
    
            public string Address { get; set; }
    
            public int Sex { get; set; }
    
            public DateTime Birth { get; set; }
    
        }

然后接着上面进行导出的逻辑

首先构建一个要导出的数据

List<DataItem> ItemList = new List<DataItem>()
            {
                new DataItem() {Name = "霸道",Age = 24,Address = "中国",Sex = 1,Birth = DateTime.Now},
                new DataItem() {Name = "流氓",Age = 25,Address = "北京",Sex = 0,Birth = DateTime.Now},
                new DataItem() {Name = "气质",Age = 26,Address = "上海",Sex = 0,Birth = DateTime.Now},
                new DataItem() {Name = "程序猿",Age = 27,Address = "青岛",Sex = 1,Birth = DateTime.Now},
            };

主要是构建Birth这个属性

通过上面的博客添加了NPOI的引用后，拖拽一个按钮，在按钮的点击事件中

private void button5_Click(object sender, EventArgs e)
            {
                //创建XSSFWorkbook对象
                XSSFWorkbook wb = new XSSFWorkbook();
                //创建sheet　并指定sheet的名字g
                ISheet sheet1 = wb.CreateSheet("详细数据");
                //新建style对象并设置样式属性
                ICellStyle style1 = wb.CreateCellStyle();//样式
                IFont font1 = wb.CreateFont();//字体
                font1.FontName = "宋体";
                font1.FontHeightInPoints = 11;
                font1.Boldweight = (short)FontBoldWeight.Bold;
                style1.SetFont(font1);//样式里的字体设置具体的字体样式
    
                ICellStyle dateStyle = wb.CreateCellStyle();//样式
                dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
                dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
                //设置数据显示格式
                IDataFormat dataFormatCustom = wb.CreateDataFormat();
                dateStyle.SetFont(font1);
                dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
    
                //创建第一行
                IRow row0 = sheet1.CreateRow(0);
                //创建第一行第一列并设置值
                row0.CreateCell(0).SetCellValue("姓名");
                //获取第一行第一列并设置样式
                row0.GetCell(0).CellStyle = style1;
                row0.CreateCell(1).SetCellValue("年龄");
                row0.GetCell(1).CellStyle = style1;
                row0.CreateCell(2).SetCellValue("地址");
                row0.GetCell(2).CellStyle = style1;
                row0.CreateCell(3).SetCellValue("性别");
                row0.GetCell(3).CellStyle = style1;
                row0.CreateCell(4).SetCellValue("生日");
                row0.GetCell(4).CellStyle = style1;
    
                //循环添加数据
                foreach (DataItem item in ItemList)
                {
                    int item_index = ItemList.IndexOf(item);
                    //从第二行开始
                    IRow rowi = sheet1.CreateRow(item_index + 1);
                    rowi.CreateCell(0).SetCellValue(item.Name);
                    rowi.CreateCell(1).SetCellValue(item.Age);
                    rowi.CreateCell(2).SetCellValue(item.Address);
                    rowi.CreateCell(3).SetCellValue(item.Sex);
                    rowi.CreateCell(4).SetCellValue(item.Birth);
                    rowi.GetCell(4).CellStyle = dateStyle;
                   
    
                    //设置列宽度，256*字符数，因为单位是1/256个字符
                    sheet1.SetColumnWidth(item_index, 256 * item.Address.Length * 4);
                }
                try
                {
                    //将内存中的数据写入磁盘
                    using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "liumang.xlsx"), FileMode.Create))
                    {
                        wb.Write(filestream);
                        filestream.Close();
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex);
                }
                MessageBox.Show("导出完成");
            }

主要是通过新建一个CellStyle样式

ICellStyle dateStyle = wb.CreateCellStyle();//样式
                dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
                dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
                //设置数据显示格式
                IDataFormat dataFormatCustom = wb.CreateDataFormat();
                dateStyle.SetFont(font1);
                dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");

然后在循环添加每一行是通过

rowi.GetCell(4).CellStyle = dateStyle;

来设置日期的显示格式

首先对象属性里面的时间是这样显示的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

首先测试下如果不加设置CellStyle的这一行代码

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

如果加上这句设置的代码后

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2][]

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_106423452]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/106423452
[https_blog.csdn.net_badao_liumang_qizhi]: https://blog.csdn.net/badao_liumang_qizhi
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020052914232451.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020052914233193.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200529142338535.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70