• NPOI datatable导出类


    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    
    namespace LeaRun.Utilities
    {
        public static class NpoiHelper
        {
            public static Stream RenderDataTableToExcel(DataTable SourceTable)
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                MemoryStream ms = new MemoryStream();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);
    
                // handling header. 
                foreach (DataColumn column in SourceTable.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
    
                // handling value. 
                int rowIndex = 1;
    
                foreach (DataRow row in SourceTable.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
    
                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
    
                    rowIndex++;
                }
    
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
    
                sheet = null;
                headerRow = null;
                workbook = null;
    
                return ms;
            }
            public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
            {
                HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
                ISheet sheet = workbook.GetSheetAt(SheetIndex);
    
                DataTable table = new DataTable();
    
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;
    
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
    
                int rowCount = sheet.LastRowNum;
    
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
    
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
    
                            dataRow[j] = row.GetCell(j).ToString();
                    }
    
                    table.Rows.Add(dataRow);
                }
    
                ExcelFileStream.Close();
                workbook = null;
                sheet = null;
                return table;
            }
        }
    }
  • 相关阅读:
    UVA1394 And Then There Was One
    delphi编程CRC算法的实现,以下是全部的代码
    SQLite学习手册(临时文件)
    SQLite学习手册(实例代码<一>)
    SQLite学习手册(锁和并发控制)
    Delphi中比较两个字符串相似性的百分比算法
    SQLite学习手册(内存数据库)
    delphi 读取网卡mac的3种方式
    从内存中加载并运行exe
    Windows消息机制要点
  • 原文地址:https://www.cnblogs.com/lovejunjuan/p/9558128.html
Copyright © 2020-2023  润新知