/// <summary>
/// use me for 2003 and 2007
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public DataTable ReadExcelAsTable(string fileName)
{
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
//chk xls
SS.UserModel.ISheet sheet;
if (fileName.EndsWith("xlsx"))
{
XSSF.UserModel.XSSFWorkbook wb = new XSSF.UserModel.XSSFWorkbook(fs); //for 2007
sheet = wb.GetSheetAt(0); //read first sheet
}
else
{
SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook(fs); //for 2003
sheet = wb.GetSheetAt(0); //read first sheet
}
return(ReadSheet(sheet));
}
}
catch (Exception ex)
{
throw ex;
}
}
private void BtnV1_Click(object sender, EventArgs e)
{
HSSF.UserModel.HSSFWorkbook wb = V1();
System.IO.FileStream file = new System.IO.FileStream(@"D:\cadmen\una_work\sample\NPOI\0_v1.xls", System.IO.FileMode.Create); //1.產生檔案
wb.Write(file); //2.put steam to file
file.Close(); //3.close file
}
private HSSF.UserModel.HSSFWorkbook V1()
{
HSSF.UserModel.HSSFWorkbook wb = new HSSF.UserModel.HSSFWorkbook();
SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0);
row.CreateCell(1);
row.Cells[0].SetCellValue("t");
row.Cells[1].SetCellValue("p");
//row.CreateCell(0).SetCellValue("t");也可以這樣寫
row = sheet.CreateRow(1);
row.CreateCell(0);
row.CreateCell(1);
row.Cells[0].SetCellValue("1");
row.Cells[1].SetCellValue("0");
//不用再add回sheet.
row = sheet.CreateRow(2);
row.CreateCell(0);
row.CreateCell(1);
row.Cells[0].SetCellValue("b");
row.Cells[1].SetCellValue("w");
//不用再add回sheet.
return(wb);
}
//範例二，DataTable轉成Excel檔案的方法
private void write(DataTable dt, string fileName)
{
try
{
//建立Excel 2003檔案
SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook();
SS.UserModel.ISheet sheet;
////建立Excel 2007檔案
//IWorkbook wb = new XSSFWorkbook();
//ISheet ws;
if (dt.TableName != string.Empty)
{
sheet = wb.CreateSheet(dt.TableName);
}
else
{
sheet = wb.CreateSheet("Sheet1");
}
sheet.CreateRow(0);//第一行為欄位名稱
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
System.IO.FileStream file = new System.IO.FileStream(fileName, System.IO.FileMode.Create); //1.產生檔案
wb.Write(file); //2.put steam to file
file.Close(); //3.close file
}
catch (Exception ex)
{
throw ex;
}
}
//範例二，DataTable轉成Excel檔案的方法
private SS.UserModel.IWorkbook V2()
{
DataTable dt = new DT().GetDataTable();
//建立Excel 2003檔案
SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook();
SS.UserModel.ISheet sheet;
////建立Excel 2007檔案
//IWorkbook wb = new XSSFWorkbook();
//ISheet ws;
if (dt.TableName != string.Empty)
{
sheet = wb.CreateSheet(dt.TableName);
}
else
{
sheet = wb.CreateSheet("Sheet1");
}
sheet.CreateRow(0);//第一行為欄位名稱
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
return(wb);
}
