02/25:Convert Excel To DataTable And Vice Versa

 

• Will Use NOIP (http://npoi.codeplex.com/)
• Add a class with the following code

using System;

using System.Collections.Generic;

using System.Data;

using System.IO;

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 ); 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;

    }
}

• Test the code for RenderDataTableFromEXcel

FileStream fileStream = new FileStream("test.xls", FileMode.Open);
           try
           {
               // read from file or write to file
               DataTable table = DataTableRenderToExcel.RenderDataTableFromExcel(fileStream, 0, 0);

           }
           finally
           {
               fileStream.Close();
           }