//写入excel
public void Inser()
{
using (FileStream file = new FileStream("3.xls", FileMode.Open, FileAccess.ReadWrite))
{
HSSFWorkbook hssfwork = new HSSFWorkbook(file);
ISheet sheet = new HSSFSheet(hssfwork);
IRow row = sheet.GetRow(1);
if (row == null)//不存在则,创建行
{
IRow row1 = sheet.CreateRow(1);
ICell cell = row1.CreateCell(3);//创建了三个单元格
cell.SetCellValue("123");
}
hssfwork.Write(file);
}
}
/// <summary>
///将Excel转化为 DataTable/// </summary>
/// <returns></returns>
public DataTable ExcelToDataTable()
{
using (FileStream fs = new FileStream("1.xls", FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs); //根据EXCEL文件流初始化工作簿
var sheet1 = workbook.GetSheetAt(0); //获取第一个sheet
DataTable table = new DataTable();//
var row1 = sheet1.GetRow(0);//获取第一行即标头
int cellCount = row1.LastCellNum; //第一行的列数
//把第一行的数据添加到datatable的列名
for (int i = row1.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(row1.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet1.LastRowNum; //总行数
//把每行数据添加到datatable中
for (int i = (sheet1.FirstRowNum + 1); i < sheet1.LastRowNum + 1; i++)
{
IRow row = sheet1.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
workbook = null; //清空工作簿--释放资源
sheet1 = null; //清空sheet
return table;
}
}
/// <summary>
/// 将dataTable存到Excel中
/// </summary>
/// <param name="table"></param>
public void DataTableToExcel(DataTable table)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow dataRow = sheet.CreateRow(0);
//第一行,即表头
dataRow.CreateCell(0).SetCellValue("ID");
dataRow.CreateCell(1).SetCellValue("工作名");
dataRow.CreateCell(2).SetCellValue("姓名");
dataRow.CreateCell(3).SetCellValue("接收任务时间");
dataRow.CreateCell(4).SetCellValue("预期工作时间");
dataRow.CreateCell(5).SetCellValue("工作进度");
//填充内容
for (int i = 0; i < table.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < table.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
}
}
//保存
using (FileStream fs = new FileStream("1.xls", FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}