public class NPOIExcelHelper : ISpreadsheetHelper
{
#region ISpreadsheetHelper Members
public void SaveDataSetToExcel(System.Data.DataSet dsSource, string filePath, CallBackEvent.UpdateProgressCallBack callBack)
{
//建立一個工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//為每table建立一個活頁簿WorkSheet
for (int i = 0; i < dsSource.Tables.Count; i++)
{
//callback部份用來判斷目前讀到的table百分比，供顯示進度表之用
if (callBack != null)
callBack((int)(((float)i / (float)dsSource.Tables.Count) * (float)100));
System.Data.DataTable dt = dsSource.Tables[i];
//建立活頁簿
HSSFSheet sheet = workbook.CreateSheet(dt.TableName);
//為避免日期格式被Excel自動換掉，所以設定 format 為 『@』 表示一率當成text來看
HSSFCellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//用column name 當成標題列
List<String> columns = new List<string>();
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
string name = dt.Columns[colIndex].ColumnName;
HSSFCell cell = sheet.CreateRow(0).CreateCell(colIndex);
cell.SetCellValue(name);
cell.CellStyle = textStyle;
columns.Add(name);
}
//建立資料列
for (int row = 0; row < dt.Rows.Count; row++)
{
DataRow dr = dt.Rows[row];
for (int col = 0; col < columns.Count; col++)
{
string data = dr[columns[col]].ToString();
HSSFCell cell = sheet.CreateRow(row+1).CreateCell(col);
cell.SetCellValue(data);
cell.CellStyle = textStyle;
}
}
}
if (callBack != null) callBack(100);
//寫入檔案
FileStream file = new FileStream(filePath, FileMode.OpenOrCreate);
workbook.Write(file);
file.Close();
}
public System.Data.DataSet ReadExcelToDataSet(string filePath, CallBackEvent.UpdateProgressCallBack callBack)
{
//開啟要讀取的Excel檔案
FileStream file = new FileStream(filePath, FileMode.Open);
//讀入Excel檔
HSSFWorkbook workbook = new HSSFWorkbook(file);
file.Close();
DataSet dsSource = new DataSet();
//為每個WorkSeeh建立出一個table
for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
{
HSSFSheet sheet = workbook.GetSheetAt(sheetIndex);
//建立一個新的table
DataTable dtNew = dsSource.Tables.Add(workbook.GetSheetName(sheetIndex));
HSSFRow row = sheet.GetRow(0);
//讀取第0列當作column name
for (int columnIndex = 0; columnIndex < row.LastCellNum; columnIndex++)
{
DataColumn dc = new DataColumn(row.GetCell(columnIndex).ToString());
dtNew.Columns.Add(dc);
}
int rowId = 1;
//第一列以後為資料，一直讀到最後一行
while (rowId <= sheet.LastRowNum)
{
DataRow newRow = dtNew.NewRow();
//讀取所有column
for (int colIndex = 0; colIndex < dtNew.Columns.Count; colIndex++)
{
newRow[dtNew.Columns[colIndex]] = sheet.GetRow(rowId).GetCell(colIndex).ToString();
}
dtNew.Rows.Add(newRow);
rowId++;
}
//dsSource.Tables.Add(dtNew);
}
return dsSource;
}
#endregion
}
修改完成後，重新啟動即可。
