NPOI 操作Excel

#region 1.最初模型数据加载（未优化代码）
private void Read2007()
{


dataSet.Tables.Clear();
tableCellTypeList.Clear();
sheetNameList.Clear();
FileStream fs = new FileStream(txtPath.Text.Trim(), FileMode.OpenOrCreate);


XSSFWorkbook xssfWorkBook = new XSSFWorkbook(fs);
for (int i = 0; i < xssfWorkBook.NumberOfSheets; i++)//循环所有工作簿的表
{
XSSFSheet xssfSheet = (XSSFSheet)xssfWorkBook.GetSheetAt(i);//某张表


DataTable dataTable = new DataTable();
TableCellType tableCType = new TableCellType();
for (int j = 0; j <= xssfSheet.LastRowNum; j++)
{
DataRow dataRow = dataTable.NewRow();
XSSFRow xssfRow = (XSSFRow)xssfSheet.GetRow(j);//某行
RowCellType rowCType = new RowCellType();


for (int y = 0; y < xssfRow.LastCellNum; y++)
{
XSSFCell xssfCell = (XSSFCell)xssfRow.GetCell(y);//某单元格
if (j == 0)//首次循环，是列名
{
if (GetObjectByCellType(xssfCell) != null)
{
dataTable.Columns.Add(GetObjectByCellType(xssfCell).ToString());
}
else
{
MessageBox.Show("----列名不能为空");
}


}
else
{//数据加载
if (GetObjectByCellType(xssfCell) != null)
{
dataRow[y] = GetObjectByCellType(xssfCell);
}
else
{
dataRow[y] = "";
}
}


rowCType.CellTypeList.Add(xssfCell.CellType);
}


if (j > 0)//不是列名时，才添加行数据
{
dataTable.Rows.Add(dataRow);
}
tableCType.RowCellTypeList.Add(rowCType);
}


dataSet.Tables.Add(dataTable);
sheetNameList.Add(xssfWorkBook.GetSheetName(i));
tableCellTypeList.Add(tableCType);




}


// dgvMainName.DataSource = dataSet.Tables[0];
// dgvMainName2.DataSource = dataSet.Tables[1];
// dgvMainName3.DataSource = dataSet.Tables[2];

}

private void Save2007()
{
XSSFWorkbook xssfWorkBook = new XSSFWorkbook();




for (int i = 0; i < dataSet.Tables.Count; i++)
{
XSSFSheet iSheet = (XSSFSheet)xssfWorkBook.CreateSheet();//创建一个表
DataTable dt = dataSet.Tables[i];
xssfWorkBook.SetSheetName(i, sheetNameList[i]);//设置sheet的名字


TableCellType tableCType = tableCellTypeList[i];
//创建列名
IRow row = iSheet.CreateRow(0);
for (int r = 0; r < dt.Columns.Count; r++)
{
row.CreateCell(r).SetCellValue(dt.Columns[r].ColumnName);
}


//创建数据行
for (int j = 1; j <= dt.Rows.Count; j++)
{
RowCellType rowCType = tableCType.RowCellTypeList[j];
IRow iRow = iSheet.CreateRow(j);
for (int y = 0; y < dt.Columns.Count; y++)
{
ICell iCell = iRow.CreateCell(y);
// iCell.SetCellValue(dt.Rows[j-1][y].ToString());
// iCell.SetCellValue(ConvertToType(rowCType.CellTypeList[y],dt.Rows[j-1][y]));
// iCell.SetCellType(rowCType.CellTypeList[y]);


// ConvertToType(rowCType.CellTypeList[y], iCell, dt.Rows[j - 1][y]);
}
}
}
FileStream fs = new FileStream("C:\\11.xlsx", FileMode.Create);
xssfWorkBook.Write(fs);
fs.Close();


}


private void Read2003()
{
dataSet.Tables.Clear();
tableCellTypeList.Clear();
sheetNameList.Clear();
FileStream fs = new FileStream(txtPath.Text.Trim(), FileMode.OpenOrCreate);
HSSFWorkbook hssfWorkBook = new HSSFWorkbook(fs);
for (int i = 0; i < hssfWorkBook.NumberOfSheets; i++)//获取表
{
HSSFSheet hssfSheet = (HSSFSheet)hssfWorkBook.GetSheetAt(i);
DataTable dt = new DataTable();
TableCellType tableCType = new TableCellType();
for (int j = 0; j <= hssfSheet.LastRowNum; j++)//获取行
{
HSSFRow hssfRow = (HSSFRow)hssfSheet.GetRow(j);
DataRow dr = dt.NewRow();
RowCellType rowCType = new RowCellType();
for (int x = 0; x < hssfRow.LastCellNum; x++)//获取单元格
{
HSSFCell hssfCell = (HSSFCell)hssfRow.GetCell(x);
if (j == 0)//第一次加载，添加列名
{
if (GetObjectByCellType(hssfCell) != null)
{
dt.Columns.Add(GetObjectByCellType(hssfCell).ToString());
}
else
{


dt.Columns.Add("");
}


}
else
{ //加载数据
if (GetObjectByCellType(hssfCell) != null)
{
if (hssfRow.LastCellNum > dt.Columns.Count)
{
MessageBox.Show("------" + j + "行有问题");
break;
}
dr[x] = GetObjectByCellType(hssfCell);


}
else
{
dr[x] = "";
}




}


rowCType.CellTypeList.Add(hssfCell.CellType);


}
if (j > 0)//从第二行开始加载数据
{
dt.Rows.Add(dr);
}
tableCType.RowCellTypeList.Add(rowCType);


}




dataSet.Tables.Add(dt);
sheetNameList.Add(hssfWorkBook.GetSheetName(i));
tableCellTypeList.Add(tableCType);


}


// dgvMainName.DataSource = dataSet.Tables[0];


}
private void Save2003()
{
HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
for (int i = 0; i < dataSet.Tables.Count; i++)
{
HSSFSheet iSheet = (HSSFSheet)hssfWorkBook.CreateSheet();//创建一个表
DataTable dt = dataSet.Tables[i];
hssfWorkBook.SetSheetName(i, sheetNameList[i]);//设置sheet的名字


TableCellType tableCType = tableCellTypeList[i];
//创建列名
IRow row = iSheet.CreateRow(0);
for (int r = 0; r < dt.Columns.Count; r++)
{
row.CreateCell(r).SetCellValue(dt.Columns[r].ColumnName);
}


//创建数据行
for (int j = 1; j <= dt.Rows.Count; j++)
{
RowCellType rowCType = tableCType.RowCellTypeList[j];
IRow iRow = iSheet.CreateRow(j);
for (int y = 0; y < dt.Columns.Count; y++)
{
ICell iCell = iRow.CreateCell(y);
// iCell.SetCellValue(dt.Rows[j-1][y].ToString());
// iCell.SetCellValue(ConvertToType(rowCType.CellTypeList[y],dt.Rows[j-1][y]));
// iCell.SetCellType(rowCType.CellTypeList[y]);


// ConvertToType(rowCType.CellTypeList[y], iCell, dt.Rows[j - 1][y]);
}
}
}
FileStream fs = new FileStream("C:\\11.xls", FileMode.CreateNew);
hssfWorkBook.Write(fs);
fs.Close();
}
#endregion