如何添加数据集采用Excellibrary到工作表 [英] how to add dataset to worksheet using Excellibrary
本文介绍了如何添加数据集采用Excellibrary到工作表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我要补充的多个工作表中的单个文件..我无法找到任何方式添加数据集到工作表,但我知道如何添加工作表
I have to add multiple worksheets in single file ..i can not find any way to add dataset to worksheet but i know how to add worksheet
推荐答案
试试这个。
using System;
using System.Data;
using System.IO;
using ExcelLibrary.SpreadSheet;
namespace 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);
}
}
}
从<一个href=\"http://$c$c.google.com/p/excellibrary/source/browse/trunk/src/ExcelLibrary/DataSetHelper.cs?r=52\"相对=nofollow> code.google.com
对于样式和颜色
From code.google.com For Style and color
HSSFCellStyle style1 = hssfworkbook.CreateCellStyle();
// cell background
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index;
style1.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
// font color
HSSFFont font1 = hssfworkbook.CreateFont();
font1.Color = NPOI.HSSF.Util.HSSFColor.YELLOW.index;
style1.SetFont(font1);
cell.CellStyle = style1;
有关更详细的看到这个帖子<一个href=\"http://stackoverflow.com/questions/3117227/how-can-i-change-cell-style-in-an-excel-file-with-excellibrary\">How我可以改变单元格样式在Excel与ExcelLibrary文件?
For more detail see this post How can I change cell style in an Excel file with ExcelLibrary?
这篇关于如何添加数据集采用Excellibrary到工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文