使用NPOI导出数据到Excel

最新推荐文章于 2025-01-09 10:24:55 发布
danjiewu
最新推荐文章于 2025-01-09 10:24:55 发布
阅读量1.6k
点赞数 1
分类专栏： .Net 文章标签： stream properties object list excel float
本文链接：https://blog.youkuaiyun.com/danjiewu/article/details/7230633
版权
.Net 专栏收录该内容
16 篇文章
订阅专栏
本文介绍了如何利用NPOI库将数据表格导出为Excel文件，包括创建工作簿、设置文档和汇总信息、填充表头和内容。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
 public static class XlsExport
    {
        public static void ExportTo(DataTable table, Stream stream)
        {
            using (HSSFWorkbook workbook = new HSSFWorkbook())
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Title = "NPOI";
                si.Author = "NPOI";
                si.Comments = "由NPOI生成";
                workbook.SummaryInformation = si;

                Sheet sheet = workbook.CreateSheet(table.TableName);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                headStyle.BorderLeft = CellBorderType.THIN;
                headStyle.LeftBorderColor = HSSFColor.WHITE.index;
                headStyle.FillForegroundColor = HSSFColor.ROYAL_BLUE.index;
                headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                Font font = workbook.CreateFont();
                font.Boldweight = 700;
                font.Color = HSSFColor.WHITE.index;
                headStyle.SetFont(font);

                CellStyle dateTimeStyle = workbook.CreateCellStyle();
                dateTimeStyle.DataFormat = 22;

                CellStyle timeSpanStyle = workbook.CreateCellStyle();
                timeSpanStyle.DataFormat = 21;

                //填充表头  
                Row dataRow = sheet.CreateRow(0);
                int columnIndex = 0;
                foreach (DataColumn column in table.Columns)
                {
                    Cell cell = dataRow.CreateCell(columnIndex);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = headStyle;
                    if (column.DataType == typeof(DateTime) || column.DataType == typeof(DateTime?))
                    {
                        sheet.SetDefaultColumnStyle(columnIndex, dateTimeStyle);
                    }
                    else if (column.DataType == typeof(TimeSpan) || column.DataType == typeof(TimeSpan?))
                    {
                        sheet.SetDefaultColumnStyle(columnIndex, timeSpanStyle);
                    }
                    columnIndex++;
                }


                //填充内容  
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow row = table.Rows[i];
                    dataRow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        DataColumn column = table.Columns[j];
                        object value = row[column];
                        Cell cell = dataRow.CreateCell(j);
                        SetCellValue(cell, value);

                    }
                }

                workbook.Write(stream);
            }
        }

        public static void ExportTo(Type objectType, IList list, Stream stream)
        {
            using (HSSFWorkbook workbook = new HSSFWorkbook())
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Title = "NPOI";
                si.Author = "NPOI";
                si.Comments = "由NPOI生成";
                workbook.SummaryInformation = si;

                Sheet sheet = workbook.CreateSheet(objectType.Name);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                headStyle.BorderLeft = CellBorderType.THIN;
                headStyle.LeftBorderColor = HSSFColor.WHITE.index;
                headStyle.FillForegroundColor = HSSFColor.ROYAL_BLUE.index;
                headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                Font font = workbook.CreateFont();
                font.Boldweight = 700;
                font.Color = HSSFColor.WHITE.index;
                headStyle.SetFont(font);

                CellStyle dateTimeStyle = workbook.CreateCellStyle();
                dateTimeStyle.DataFormat = 22;

                CellStyle timeSpanStyle = workbook.CreateCellStyle();
                timeSpanStyle.DataFormat = 21;

                //填充表头  
                Row dataRow = sheet.CreateRow(0);
                PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(objectType);
                int columnIndex = 0;
                foreach (PropertyDescriptor property in properties)
                {
                    Cell cell = dataRow.CreateCell(columnIndex);
                    cell.SetCellValue(property.DisplayName);
                    cell.CellStyle = headStyle;
                    if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
                    {
                        sheet.SetDefaultColumnStyle(columnIndex, dateTimeStyle);
                    }
                    else if (property.PropertyType == typeof(TimeSpan) || property.PropertyType == typeof(TimeSpan?))
                    {
                        sheet.SetDefaultColumnStyle(columnIndex, timeSpanStyle);
                    }
                    columnIndex++;
                }


                //填充内容  
                for (int i = 0; i < list.Count; i++)
                {
                    object o = list[i];
                    dataRow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < properties.Count; j++)
                    {
                        PropertyDescriptor property = properties[j];
                        object value = o is IIndexedProperty ? ((IIndexedProperty)o)[property.Name] : property.GetValue(o);
                        Cell cell = dataRow.CreateCell(j);
                        SetCellValue(cell, value);
                        
                    }
                }

                workbook.Write(stream);
            }
        }

        public static void SetCellValue(Cell cell, object value)
        {
            try
            {
                if (value is DateTime)
                    cell.SetCellValue((DateTime)value);
                else if (value is TimeSpan)
                    cell.SetCellValue(new DateTime(1900, 1, 1) + (TimeSpan)value);
                else if (value is int || value is short || value is float || value is double)
                    cell.SetCellValue(Convert.ToDouble(value));
                else
                    cell.SetCellValue(ExamUtil.ToDisplayString(value));
            }
            catch { }
        }
    }