NPOI 操作Excel
来源:互联网 发布:知乎 保银投资 编辑:程序博客网 时间:2024/05/17 01:30
#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];
{
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
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];
}
{
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
0 0
- NPOI 进行EXCEL操作
- 用NPOI操作Excel
- C# NPOI 操作excel
- NPOI操作EXCEL
- NPOI操作Excel
- C# NPOI 操作excel
- NPOI 操作Excel
- NPOI 操作Excel
- NPOI操作Excel
- NPOI操作EXCEL
- NPOI操作Excel表格
- Npoi操作excel
- Npoi操作excel
- 使用NPOI操作excel
- Npoi操作excel
- NPOI操作excel异常
- npoi 操作excel类
- Npoi操作excel
- Sed学习笔记
- 关于避开轮询方法的方法
- oracle中rollup和cube使用
- Spring JNDI之Tomcat配置
- Zookeeper源码分析(7)- SyncRequestProcessor
- NPOI 操作Excel
- JAVA内存泄露、溢出的检查方法、工具介绍
- Flex 中给LineChart的节点上显示数值
- JDK源码阅读之List和AbstractSequentialList
- 这几天写的连连看的思路和算法总结
- linux杂七杂八
- leetcode 总结
- 鸟哥的Linux私房菜 基础学习篇读书笔记(2):鸟哥的Linux学习建议
- 中值滤波