using System;
|
using System.Data;
|
using System.IO;
|
using HH.WMS.Utils.ExcelLibrary.SpreadSheet;
|
|
namespace HH.WMS.Utils.ExcelLibrary
|
{
|
/// <summary>
|
/// Provides simple way to convert Excel workbook into DataSet
|
/// </summary>
|
public sealed class DataSetHelper
|
{
|
/// <summary>
|
/// Populate all data (all converted into String) in all worksheets
|
/// from a given Excel workbook.
|
/// </summary>
|
/// <param name="filePath">File path of the Excel workbook</param>
|
/// <returns>DataSet with all worksheet populate into DataTable</returns>
|
public static DataSet CreateDataSet(String filePath)
|
{
|
DataSet ds = new DataSet();
|
Workbook workbook = Workbook.Load(filePath);
|
foreach (Worksheet ws in workbook.Worksheets)
|
{
|
DataTable dt = PopulateDataTable(ws);
|
ds.Tables.Add(dt);
|
}
|
return ds;
|
}
|
|
/// <summary>
|
/// Populate data (all converted into String) from a given Excel
|
/// workbook and also work sheet name into a new instance of DataTable.
|
/// Returns null if given work sheet is not found.
|
/// </summary>
|
/// <param name="filePath">File path of the Excel workbook</param>
|
/// <param name="sheetName">Worksheet name in workbook</param>
|
/// <returns>DataTable with populate data</returns>
|
public static DataTable CreateDataTable(String filePath, String sheetName)
|
{
|
Workbook workbook = Workbook.Load(filePath);
|
foreach (Worksheet ws in workbook.Worksheets)
|
{
|
if (ws.Name.Equals(sheetName))
|
return PopulateDataTable(ws);
|
}
|
return null;
|
}
|
|
private static DataTable PopulateDataTable(Worksheet ws)
|
{
|
CellCollection Cells = ws.Cells;
|
|
// Creates DataTable from a Worksheet
|
// All values will be treated as Strings
|
DataTable dt = new DataTable(ws.Name);
|
|
// Extract columns
|
for (int i = 0; i <= Cells.LastColIndex; i++)
|
dt.Columns.Add(Cells[0, i].StringValue, typeof(String));
|
|
// Extract data
|
for (int currentRowIndex = 1; currentRowIndex <= Cells.LastRowIndex; currentRowIndex++)
|
{
|
DataRow dr = dt.NewRow();
|
for (int currentColumnIndex = 0; currentColumnIndex <= Cells.LastColIndex; currentColumnIndex++)
|
dr[currentColumnIndex] = Cells[currentRowIndex, currentColumnIndex].StringValue;
|
dt.Rows.Add(dr);
|
}
|
|
return dt;
|
}
|
|
/// <summary>
|
/// Populate all data from the given DataSet into a new Excel workbook
|
/// </summary>
|
/// <param name="filePath">File path to new Excel workbook to be created</param>
|
/// <param name="dataset">Source DataSet</param>
|
public static void CreateWorkbook(String filePath, DataSet dataset)
|
{
|
if (dataset.Tables.Count == 0)
|
throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");
|
|
Workbook workbook = new Workbook();
|
foreach (DataTable dt in dataset.Tables)
|
{
|
Worksheet worksheet = new Worksheet(dt.TableName);
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
// Add column header
|
worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);
|
|
// Populate row data
|
for (int j = 0; j < dt.Rows.Count; j++)
|
worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]);
|
}
|
workbook.Worksheets.Add(worksheet);
|
}
|
workbook.Save(filePath);
|
}
|
|
public static void CreateWorkbook(Stream stream, DataSet dataset)
|
{
|
if (dataset.Tables.Count == 0)
|
throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");
|
|
Workbook workbook = new Workbook();
|
foreach (DataTable dt in dataset.Tables)
|
{
|
Worksheet worksheet = new Worksheet(dt.TableName);
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
// Add column header
|
worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);
|
|
// Populate row data
|
for (int j = 0; j < dt.Rows.Count; j++)
|
worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]);
|
}
|
workbook.Worksheets.Add(worksheet);
|
}
|
workbook.SaveToStream(stream);
|
}
|
}
|
}
|
