ASP.NET使用NPOI类库导出Excel

/*
 * 作者: 牛腩
 * 创建时间: 2010-1-4 15:15:05
 * Email: 164423073@qq.com
 * 说明: 导出EXCEL的类,使用说明见:
http://msdn.microsoft.com/zh-tw/ee818993.aspx
 
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;

public class DataTableRenderToExcel
{
    
public static Stream RenderDataTableToExcel(DataTable SourceTable)
    {
        HSSFWorkbook workbook 
= new HSSFWorkbook();
        MemoryStream ms 
= new MemoryStream();
        HSSFSheet sheet 
= workbook.CreateSheet();
        HSSFRow 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)
        {
            HSSFRow 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 void RenderDataTableToExcel(DataTable SourceTable, string FileName)
    {
        MemoryStream ms 
= RenderDataTableToExcel(SourceTable) as MemoryStream;
        FileStream fs 
= new FileStream(FileName, FileMode.Create, FileAccess.Write);
        
byte[] data = ms.ToArray();

        fs.Write(data, 
0, data.Length);
        fs.Flush();
        fs.Close();

        data 
= null;
        ms 
= null;
        fs 
= null;
    }

    
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
    {
        HSSFWorkbook workbook 
= new HSSFWorkbook(ExcelFileStream);
        HSSFSheet sheet 
= workbook.GetSheet(SheetName);

        DataTable table 
= new DataTable();

        HSSFRow 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++)
        {
            HSSFRow row 
= sheet.GetRow(i);
            DataRow dataRow 
= table.NewRow();

            
for (int j = row.FirstCellNum; j < cellCount; j++)
                dataRow[j] 
= row.GetCell(j).ToString();
        }

        ExcelFileStream.Close();
        workbook 
= null;
        sheet 
= null;
        
return table;
    }

    
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
    {
        HSSFWorkbook workbook 
= new HSSFWorkbook(ExcelFileStream);
        HSSFSheet sheet 
= workbook.GetSheetAt(SheetIndex);

        DataTable table 
= new DataTable();

        HSSFRow 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++)
        {
            HSSFRow 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;
    }

    
/// <summary>读取excel
    
/// 默认第一行为标头
    
/// </summary>
    
/// <param name="path">excel文档路径</param>
    
/// <returns></returns>
    public static DataTable RenderDataTableFromExcel(string path) {
        DataTable dt 
= new DataTable();

        HSSFWorkbook hssfworkbook;
        
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
        {
            hssfworkbook 
= new HSSFWorkbook(file);
        }
        HSSFSheet sheet 
= hssfworkbook.GetSheetAt(0);
        System.Collections.IEnumerator rows 
= sheet.GetRowEnumerator();

        HSSFRow headerRow 
= sheet.GetRow(0);
        
int cellCount = headerRow.LastCellNum;

        
for (int j = 0; j < cellCount; j++)
        {
            HSSFCell cell 
= headerRow.GetCell(j);
            dt.Columns.Add(cell.ToString());
        }

        
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            HSSFRow row 
= sheet.GetRow(i);
            DataRow dataRow 
= dt.NewRow();

            
for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                
if (row.GetCell(j) != null)
                    dataRow[j] 
= row.GetCell(j).ToString();
            }

            dt.Rows.Add(dataRow);
        }

        
//while (rows.MoveNext())
        
//{
        
//    HSSFRow row = (HSSFRow)rows.Current;
        
//    DataRow dr = dt.NewRow();

        
//    for (int i = 0; i < row.LastCellNum; i++)
        
//    {
        
//        HSSFCell cell = row.GetCell(i);


        
//        if (cell == null)
        
//        {
        
//            dr[i] = null;
        
//        }
        
//        else
        
//        {
        
//            dr[i] = cell.ToString();
        
//        }
        
//    }
        
//    dt.Rows.Add(dr);
        
//}

        
return dt;
    }
}

 

posted @ 2010-03-30 16:00  牛腩  阅读(5203)  评论(1编辑  收藏  举报