CreateRow GetRow 的坑
//同一行，只能用1次CreateRow
sheet.CreateRow(startrow).CreateCell(0).SetCellValue(1);
sheet.GetRow(startrow).CreateCell(1).SetCellValue(1);
sheet.GetRow(startrow).CreateCell(2).SetCellValue(1);
输入值和公式
//值
sheet.GetRow(startrow).CreateCell(2).SetCellValue(1);
//公式
string kaohe = "O" + realrow + "-" + "I" + realrow + "+" + "R" + realrow;
string shenchanjieyou = "IF( ISBLANK(R" + realrow + " ), \"\" , \"生产借油\")";
sheet.GetRow(startrow).CreateCell(16).SetCellFormula(kaohe);
sheet.GetRow(startrow).CreateCell(18).SetCellFormula(shenchanjieyou);
将excel中的数据导入到DataTable中
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="isFirstRowColumn">是否有列名</param>
/// <param name="whichrow">excel中哪行是列名</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn, int whichrow)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
IWorkbook workbook = null; //新建IWorkbook对象
try
{
var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
IRow nameRow = sheet.GetRow(whichrow - 1);
int cellCount = nameRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = nameRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = nameRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = whichrow;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
//日期cell 则读取日期格式
if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue.ToShortDateString().ToString();
}
//公式cell 则仅读取其Cell单元格的显示值 而不是读取公式
else if (row.GetCell(j).CellType == CellType.Formula)
{
row.GetCell(j).SetCellType(CellType.String);
dataRow[j] = row.GetCell(j).StringCellValue;
}
//直接读取文本
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
将excel中某工作表的数据导入到DataTable中
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="isFirstRowColumn">首行是否是列名</param>
/// <param name="sheetsname">工作表名</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn, string sheetsname)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
IWorkbook workbook = null; //新建IWorkbook对象
try
{
var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheet(sheetsname);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
//日期cell 则读取日期格式
if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue.ToShortDateString().ToString();
}
//公式cell 则仅读取其Cell单元格的显示值 而不是读取公式
else if (row.GetCell(j).CellType == CellType.Formula)
{
row.GetCell(j).SetCellType(CellType.String);
dataRow[j] = row.GetCell(j).StringCellValue;
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
按模板excel创建并输出excel
/// <summary>
/// 按单项考核模板输出运输车单项考核
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="dt">导入datatable</param>
/// <param name="yunshudinge">计算参数</param>
/// <param name="chehao">输出文件名参数</param>
public static void CreateTempExcel_danxiangkaohe(string fileName, DataTable dt, double yunshudinge, string chehao)
{
ISheet sheet = null;
DataTable data = new DataTable();
IWorkbook workbook = null; //新建IWorkbook对象
try
{
var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheet("单项考核运输车");
if (dt.Rows.Count > 0)
{
//!!!需要写入的内容!!!
for (int i = 0; i < dt.Rows.Count; i++)
{
//string youpi = Convert.ToString(dt.Rows[i]["油品名称"]);
string riqi = Convert.ToString(dt.Rows[i]["加油日期"]);
double jiayoushuliang = Convert.ToDouble(dt.Rows[i]["加油数量(L)"]);
string sijiname = Convert.ToString(dt.Rows[i]["司机姓名"]);
int row = i + 3;
int row1 = row + 1;
//！！！注意CreateRow 只需1次，其他为GetRow
sheet.CreateRow(row).CreateCell(0).SetCellValue(riqi);//直接输入
sheet.GetRow(row).CreateCell(4).SetCellValue(jiayoushuliang);
sheet.GetRow(row).CreateCell(6).SetCellValue(sijiname);
//sheet.GetRow(row).CreateCell(11).SetCellValue(youpi);
string yunshuyouhao = "C" + row1 + "*" + yunshudinge / 100;
sheet.GetRow(row).CreateCell(3).SetCellFormula(yunshuyouhao);//公式
}
//heji
sheet.GetRow(3).CreateCell(8).SetCellFormula("SUM(D:D)");//公式
sheet.GetRow(3).CreateCell(9).SetCellFormula("SUM(E:E)");//公式
sheet.GetRow(3).CreateCell(10).SetCellFormula("SUM(C:C)");//公式
}
else
{
MessageBox.Show("输出为空");
}
//Force excel to recalculate all the formula while open
sheet.ForceFormulaRecalculation = true;
//创建表的sheet数组
int sheetcount = workbook.NumberOfSheets;//获取表的数量
string[] sheetname = new string[sheetcount];//保存表的名称
for (int i = 0; i < sheetcount; i++)
sheetname[i] = workbook.GetSheetName(i);
//获得指定名称的索引
string thename = "单项考核运输车";
int myindex = Array.IndexOf(sheetname, thename);
//删除多余sheet只剩有用的那个sheet
int num1 = sheetcount - 1 - myindex;
for (int i = 0; i < sheetcount - 1; i++)
{
if (i < num1)
{
workbook.RemoveSheetAt(myindex + 1);
}
else
{
workbook.RemoveSheetAt(0);
}
}
//创建文件
var mydate = DateTime.Now.ToString("yyyy-MM-dd");
string currPath = fileName.Substring(0, fileName.LastIndexOf("\\"));
string subPath = currPath + "/" + mydate + "单项考核/";
string youpi = Convert.ToString(dt.Rows[0]["油品名称"]);
if (false == Directory.Exists(subPath))
{
Directory.CreateDirectory(subPath);
}
string newfile = subPath + youpi + "-运输车-" + chehao + ".xlsx";
using (FileStream file = new FileStream(newfile, FileMode.Create)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
workbook.Write(file); //文件IO 创建EXCEL
MessageBox.Show("提示：" + chehao + " 创建成功！");
file.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}
删除按模板excel创建excel的多余的sheet
/// <summary>
/// 删除按模板创建表的多余的sheet
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="thename">需要留的sheet名</param>
public static void RemoveOtherSheets(IWorkbook workbook, string thename)
{
//创建表的sheet数组
int sheetcount = workbook.NumberOfSheets;//获取表的数量
string[] sheetname = new string[sheetcount];//保存表的名称
for (int i = 0; i < sheetcount; i++)
sheetname[i] = workbook.GetSheetName(i);
//获得指定名称的索引
int myindex = Array.IndexOf(sheetname, thename);
//删除多余sheet只剩有用的那个sheet
int num1 = sheetcount - 1 - myindex;
for (int i = 0; i < sheetcount - 1; i++)
{
if (i < num1)
{
workbook.RemoveSheetAt(myindex + 1);
}
else
{
workbook.RemoveSheetAt(0);
}
}
}
创建excel
/// <summary>
/// 创建excel
/// </summary>
/// <param name="fileName">参考文件名称</param>
/// <param name="newfoldName">新建文件夹名</param>
/// <param name="excelName">新建excel名</param>
/// <param name="workbook">输出内容</param>
public static void CreateNewFoldandExcel(string fileName,string newfoldName,string excelName,IWorkbook workbook)
{
//创建文件
var mydate = DateTime.Now.ToString("yyyy-MM-dd");
string currPath = fileName.Substring(0, fileName.LastIndexOf("\\"));
string subPath = currPath + "/" + mydate + newfoldName + "/";
if (false == Directory.Exists(subPath))
{
Directory.CreateDirectory(subPath);
}
string newfile = subPath + excelName + ".xlsx";
using (FileStream file = new FileStream(newfile, FileMode.Create)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
workbook.Write(file); //文件IO 创建EXCEL
MessageBox.Show("提示：" + excelName + " 创建成功！");
file.Close();
}
}
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。