public class NPOIExcel
{
public const string pattern = @"^\d+(\.\d+)?$";//判断是否是数字
public static void CreateFile(string filePath, string sheetName)
{
if (!File.Exists(filePath))
{
//文件不存在 创建新文件写入
if (filePath.Contains(".xlsx") || filePath.Contains(".ett") || filePath.Contains(".xlsm"))
{
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.CreateSheet(sheetName);
FileStream fs = new FileStream(filePath, FileMode.Create);
workbook.Write(fs);
fs.Close();
}
else if (filePath.Contains(".xls") || filePath.Contains(".et"))
{
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.CreateSheet(sheetName);
FileStream fs = new FileStream(filePath, FileMode.Create);
workbook.Write(fs);
fs.Close();
}
}
}
/// <summary>
/// 把DataTable的数据写入到指定的excel文件中
/// </summary>
/// <param name="TargetFileNamePath">目标文件excel的路径</param>
/// <param name="sourceData">要写入的数据</param>
/// <param name="sheetName">excel表中的sheet的名称，可以根据情况自己起</param>
/// <param name="IsWriteColumnName">是否写入DataTable的列名称</param>
/// <returns>返回写入的行数</returns>
public static int DataTableToExcel(string TargetFileNamePath, System.Data.DataTable sourceData, string TemplateSheetName, string sheetName, bool IsWriteColumnName, bool Overwrite, string startCell, Dictionary<string, object> ConvertColumnTypes = null)
{
bool status = false;
//数据验证
if (!File.Exists(TargetFileNamePath))
{
//excel文件的路径不存在
throw new ArgumentException(ConfigStringHelper.ExcelError_NotArgumentExceptionFilePath);
}
FileStream fs = new FileStream(TargetFileNamePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流
////根据Excel文件的后缀名创建对应的workbook
IWorkbook workbook = null;
if (TargetFileNamePath.IndexOf(".xlsx") > 0 || TargetFileNamePath.IndexOf(".ett") > 0 || TargetFileNamePath.IndexOf(".xlsm") > 0)
{ //2007版本的excel
workbook = new XSSFWorkbook(fs);
}
else if (TargetFileNamePath.IndexOf(".xls") > 0 || TargetFileNamePath.IndexOf(".et") > 0) //2003版本的excel
{
workbook = new HSSFWorkbook(fs);
}
else
{
return -1; //都不匹配或者传入的文件根本就不是excel文件，直接返回
}
ISheet sheet = workbook.GetSheetAt(0);//获取工作表
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
//excel表的sheet名
//ISheet sheet = null;
if (!string.IsNullOrEmpty(TemplateSheetName))
{
int idex = workbook.GetSheetIndex(TemplateSheetName);
if (string.IsNullOrEmpty(sheetName))
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheetTemplate);
}
//修改sheet名字
workbook.SetSheetName(idex, sheetName);
}
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
//获取sheet的索引
}
if (SheetName.Contains(sheetName.ToLower()) && Overwrite == false)
{
sheet = workbook.GetSheet(sheetName);
status = false;
}
else if (SheetName.Contains(sheetName.ToLower()) && Overwrite == true)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
sheet = workbook.CreateSheet(sheetName);
status = true;
}
if (!SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.CreateSheet(sheetName);
}
if (sheet == null) return -1; //无法创建sheet，则直接返回
//写入Excel的行数
int WriteRowCount = 0;
int colunmIndex = 0;
if (!string.IsNullOrEmpty(startCell))
{
CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(startCell);
WriteRowCount = cellRangeAddress.FirstRow;
colunmIndex = cellRangeAddress.FirstColumn;
}
//这里获取源数据的总行和列
var SourceRows = sourceData.Rows.Count;//行
var SourceColumns = sourceData.Columns.Count;//列
if (IsWriteColumnName == false)
{
IRow ColumnNameRow = null;
//sheet表创建新的一行,即第一行
if (status == true && string.IsNullOrEmpty(startCell))
{
//表示覆盖
ColumnNameRow = sheet.CreateRow(0);
}
else if (status == false && string.IsNullOrEmpty(startCell))
{
//表示不覆盖,此处需要判断范围内是否存在数据
for (var i = WriteRowCount; i < SourceRows + WriteRowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
}
}
}
ColumnNameRow = sheet.CreateRow(0);
}
if (!string.IsNullOrEmpty(startCell) && status == false)
{
//表示不覆盖，从指定的位置处写入,需要判断指定位置是否有数据
for (var i = WriteRowCount; i < SourceRows + WriteRowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
}
var firsLine = row.GetCell(j).ToString();
}
}
ColumnNameRow = sheet.CreateRow(WriteRowCount);
}
else if (!string.IsNullOrEmpty(startCell) && status == true)
{
//表示覆盖，从指定的位置处写入
ColumnNameRow = sheet.CreateRow(WriteRowCount);
}
//IRow ColumnNameRow = sheet.CreateRow(0); //0下标代表第一行
int ColumnCount = colunmIndex;//开始列
//进行写入DataTable的列名
for (int colunmNameIndex = 0; colunmNameIndex < sourceData.Columns.Count; colunmNameIndex++)
{
ColumnNameRow.CreateCell(ColumnCount).SetCellValue(sourceData.Columns[colunmNameIndex].ColumnName);
ColumnCount++;
}
WriteRowCount++;
}
bool endStatus = false;
int count = 0;
count = (WriteRowCount) + (sheet.LastRowNum);
//写入数据
for (int row = 0; row < sourceData.Rows.Count; row++)
{
IRow newRow = null;
//sheet表创建新的一行
if (status == false && string.IsNullOrEmpty(startCell))
{
if (endStatus == false)
{
if (IsWriteColumnName == false)
{
}
else
{
for (var i = WriteRowCount + 1; i < SourceRows + WriteRowCount + 1; i++)
{
IRow row_a = sheet.GetRow(i);
if (row_a == null)
{
row_a = sheet.CreateRow(i);
}
for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
{
ICell cell = row_a.GetCell(j);
if (cell == null)
{
cell = row_a.CreateCell(j);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
}
var firsLine = row_a.GetCell(j).ToString();
}
}
}
}
newRow = sheet.CreateRow(WriteRowCount);
}
else if (status == true && string.IsNullOrEmpty(startCell))
{
//表示覆盖
newRow = sheet.CreateRow(WriteRowCount);
}
if (!string.IsNullOrEmpty(startCell) && status == true)
{
//表示覆盖，从指定的位置处写入
newRow = sheet.CreateRow(WriteRowCount);
}
else if (!string.IsNullOrEmpty(startCell) && status == false)
{
if (endStatus == false)
{
//表示不覆盖，从指定的位置处写入,需要判断指定位置是否有数据
if (IsWriteColumnName == false)
{
}
else
{
for (var i = WriteRowCount + 1; i < SourceRows + WriteRowCount + 1; i++)
{
IRow row_a = sheet.GetRow(i);
if (row_a == null)
{
row_a = sheet.CreateRow(i);
}
for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
{
ICell cell = row_a.GetCell(j);
if (cell == null)
{
cell = row_a.CreateCell(j);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
}
var firsLine = row_a.GetCell(j).ToString();
}
}
}
}
newRow = sheet.CreateRow(WriteRowCount);
}
int Count = colunmIndex;//开始列
for (int column = 0; column < sourceData.Columns.Count; column++)
{
string columnName = sourceData.Columns[Convert.ToInt32(column)].ColumnName;
string text = sourceData.Rows[row][column].ToString();
Type type = sourceData.Columns[columnName].DataType;
if (!string.IsNullOrEmpty(text))
{
if (type.Name.Contains("Int32") || type.Name.Contains("Int16") || type.Name.Contains("Int64") || type.Name.Contains("SByte") || type.Name.Contains("Single") || type.Name.Contains("Double") || type.Name.Contains("Decimal"))
{
newRow.CreateCell(Count).SetCellValue(Convert.ToDouble(text));
}
else if (type.Name == "Boolean")
{
newRow.CreateCell(Count).SetCellValue(Convert.ToBoolean(text));
}
else if (type.Name == "DateTime")
{
newRow.CreateCell(Count).SetCellValue(Convert.ToDateTime(text));
}
else if (type.Name == "String")
{
if (ConvertColumnTypes != null)
{
if (ConvertColumnTypes.ContainsKey(columnName))
{
double d;
bool b = double.TryParse(text, out d);
if (b)
{
newRow.CreateCell(Count).SetCellValue(d);
}
else
{
newRow.CreateCell(Count).SetCellValue(text);
}
}
else
{
newRow.CreateCell(Count).SetCellValue(text);
}
}
else
{
newRow.CreateCell(Count).SetCellValue(text);
}
}
}
else
{
if (ConvertColumnTypes != null)
{
if (ConvertColumnTypes.ContainsKey(columnName))
{
if (ConvertColumnTypes[columnName] != null)
{
newRow.CreateCell(Count).SetCellValue(Convert.ToDouble(ConvertColumnTypes[columnName]));
}
}
}
}
Count++;
}
WriteRowCount++;
endStatus = true;
}
//写入到excel中
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs1 = new FileStream(TargetFileNamePath, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs1.Write(_data, 0, _data.Length);
fs1.Flush();
_data = null;
}
}
return WriteRowCount;
}
/// <summary>
/// 获取excel中的Sheet
/// </summary>
public static List<string> GetMultipleSheets(string fileName)
{
List<string> list = new List<string>();
Dictionary<int, string> t = new Dictionary<int, string>();
ISheet sheet = null;
IWorkbook workbook = null;
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
int count = workbook.NumberOfSheets; //获取所有SheetName
for (int i = 0; i < count; i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet.LastRowNum > 0)
{
t.Add(i, workbook.GetSheetAt(i).SheetName);
list.Add(workbook.GetSheetAt(i).SheetName);
}
}
return list;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fs != null)
fs.Dispose();
}
}
/// <summary>
/// Excel读取Sheets
/// </summary>
/// <param name="file">导入路径</param>
/// <returns></returns>
public static List<string> GetSheetNames(string file)
{
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook;
if (fileExt == ".xlsx" || fileExt == ".ett" || fileExt == ".xlsm")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls" || fileExt == ".et")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null)
{
return null;
}
int sheetCount = workbook.NumberOfSheets;//获取表的数量
List<string> sheetNames = new List<string>();//保存表的名称
for (int i = 0; i < sheetCount; i++)
{
sheetNames.Add(workbook.GetSheetName(i));
}
return sheetNames;
}
}
/*
/// <summary>
/// Excel读取Sheets
/// </summary>
/// <param name="file">导入路径</param>
/// <returns></returns>
public static List<string> GetSheetNames(IWorkbook workbook)
{
int sheetCount = workbook.NumberOfSheets;//获取表的数量
List<string> sheetNames = new List<string>();//保存表的名称
for (int i = 0; i < sheetCount; i++)
{
sheetNames.Add(workbook.GetSheetName(i));
}
return sheetNames;
}
*/
/// <summary>
/// Excel读取Datable
/// </summary>
/// <param name="file">导入路径</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file, bool isFirstRowColumn, string sheetName = "")
{
DataTable dt = new DataTable();
IWorkbook workbook;
ISheet sheet = null;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xlsx" || fileExt == ".ett" || fileExt == ".xlsm")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls" || fileExt == ".et")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null)
{
return null;
}
sheet = workbook.GetSheetAt(0);
if (!string.IsNullOrEmpty(sheetName))
{
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
}
if (SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
var rowCount = sheet.LastRowNum + 1;//得到总行数
//z这里遍历得到最大列数，因为存在第一行残缺为空的情况
List<int> array = new List<int>();
for (var i = 0; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
//array.Add(row.Cells.Count);//获取有效列
array.Add(row.LastCellNum);//获取所有列(包含空列以及残缺)
}
var maxValue = array.Max();//得到最大列数
int Index = 0;//用来判断是否使用列头
List<int> columns = new List<int>();
//创建列头
if (isFirstRowColumn == false)
{
//第一行不作为DataTable的列名
Index = sheet.FirstRowNum;
for (int i = 0; i < maxValue; i++)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
columns.Add(i);
}
}
else
{
//判断第一行是否是合并单元格
IRow row = sheet.GetRow(0);
for (int i = 0; i < row.Cells.Count; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
cell = row.CreateCell(i);
}
var cellfirst = cell.IsMergedCell;
if (cellfirst == true)
{
//说明第一行有合并单元格无法作为列,此时需要提醒用户
throw new Exception(ConfigStringHelper.ExcelError_IncludeHeaders);
}
}
Index = sheet.FirstRowNum + 1;
//第一行作为DataTable列名
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
if (header == null)
{
for (int i = 0; i < maxValue; i++)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
columns.Add(i);
}
}
else
{
//这里用来检测第一行数据是否合法，可以用来做列头
List<string> lst = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null)
{
lst.Add("Columns" + i.ToString());
}
else
{
lst.Add(obj.ToString());
}
}
bool status = lst.GroupBy(n => n).Any(c => c.Count() > 1);
if (status == true)
{
//说明第一行存在重复数据无法作为列头
throw new Exception(ConfigStringHelper.ExcelError_IncludeHeadersData);
}
for (int i = 0; i < maxValue; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
}
}
//数据
for (int i = Index; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank:
return null;
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
return Convert.ToDateTime(cell.DateCellValue);
}
else//其他数字类型
{
return Convert.ToDouble(cell.NumericCellValue);
}
case CellType.String:
return cell.StringCellValue;
case CellType.Error:
XSSFCell xSSFCell = cell as XSSFCell;
if (null != xSSFCell)
{
return xSSFCell.ErrorCellString;
}
else
{
return cell.ToString();
}
case CellType.Formula:
try
{
return cell.NumericCellValue.ToString();
}
catch
{
try
{
#region 这种方式处理可以保证100%无错误，但是效率太慢，一个文本公式读取需要3秒
//IFormulaEvaluator formulaEvaluator;
//if (_excelVersion == "2007")
//{
// formulaEvaluator = new XSSFFormulaEvaluator(workbook);
//}
//else
//{
// formulaEvaluator = new HSSFFormulaEvaluator(workbook);
//}
//formulaEvaluator.EvaluateInCell(cell);
//var FormulaValue = cell.ToString();
#endregion
var FormulaValue = cell.StringCellValue.ToString();
return FormulaValue;
}
catch
{
return "";
}
}
default:
return "=" + cell.CellFormula;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public static DataTable ReadTableFromExcel(string fileName, string sheetName = "", bool isFirstRowColumn = false)
{
//FileStream fs = null;
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
IWorkbook workbook = null;
if (fileName.Contains(".xlsx")) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.Contains(".xls")) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheetAt(0);
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i);
}
if (SheetName.Contains(sheetName))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(0);
}
//if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
//{
//sheet = workbook.GetSheet(sheetName);
//}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
if (firstRow == null)
{
return data;
}
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = null;
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
{
for (int column = 0; column < cellCount; column++)
{
data.Columns.Add($"Column{column}");
}
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
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
}
finally
{
//if (fs != null)
//{
// fs.Dispose();
//}
}
}
/// <summary>
/// 读取单元格
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="cell">单元格坐标</param>
/// <returns>返回的DataTable</returns>
public static string ReadCell(string fileName, string sheetName = "", string cell = "")
{
FileStream fs = null;
try
{
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
IWorkbook workbook = null;
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
if (sheet != null)
{
CellReference cf = new CellReference(cell);
var irow = sheet.GetRow(cf.Row);
if (irow == null)
{
irow = sheet.CreateRow(cf.Row); ;
}
try
{
var cel = irow.GetCell(cf.Col);
if (cel == null)
{
cel = irow.CreateCell(cf.Col);
}
if (cel != null)
{
if (cel.CellType == CellType.Formula)
{
cel.SetCellType(CellType.String);
}
return cel.ToString();
}
}
catch
{
throw new ArgumentException(ConfigStringHelper.ExcelError_CellExist);
}
}
return null;
}
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
/// <summary>
/// 读取列
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="column">列</param>
/// <returns>返回的DataTable</returns>
public static string[] ReadColumn(string fileName, string sheetName, string column)
{
FileStream fs = null;
try
{
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
List<string> resultList = new List<string>();
ISheet sheet = null;
IWorkbook workbook = null;
if (fileName.Contains(".xlsx")) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
CellReference cf = new CellReference(column);
for (int rownum = sheet.FirstRowNum; rownum <= sheet.LastRowNum; rownum++)
{
var row = sheet.GetRow(rownum);
var cell = row?.GetCell(cf.Col);
if (cell != null)
{
if (cell.CellType == CellType.Formula)
{
cell.SetCellType(CellType.String);
}
resultList.Add(cell.ToString());
}
}
}
return resultList.ToArray();
}
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
/// <summary>
/// 读取范围
/// </summary>
[Obsolete]
public static DataTable ReadRange(string fileName, string sheetName, string range, bool includeHeaders = false)
{
FileStream fs = null;
try
{
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
List<string> resultList = new List<string>();
ISheet sheet = null;
IWorkbook workbook = null;
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
System.Data.DataTable dt = new System.Data.DataTable();
if (sheet != null)
{
try
{
CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(range);
int firstcolumn = cellRangeAddress.FirstColumn;
int lastcolumn = cellRangeAddress.LastColumn;
int firstrow = cellRangeAddress.FirstRow;
int lastrow = cellRangeAddress.LastRow;
if (!range.Contains(":"))
{
if (lastcolumn == firstcolumn)
{
IRow headrow = sheet.GetRow(firstrow);
lastcolumn = headrow.Cells.Count;
}
if (lastrow == firstrow)
lastrow = sheet.LastRowNum + 1;//得到总行数
}
int Column = lastcolumn - firstcolumn;
int startRow = firstrow;
List<int> columns = new List<int>();
//创建列头
if (includeHeaders)
{
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = null;
cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
columns.Add(i);
}
}
}
startRow = firstrow + 1;
}
else
{
for (int i = 0; i < Column; i++)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
columns.Add(i);
}
}
//数据
for (var i = startRow; i < lastrow; i++)
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
bool merged = false;
for (var j = firstcolumn; j < Column + firstcolumn; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
if (cell.IsMergedCell) //检测列的单元格是否合并
{
dr[j] = GetValueType(cell); //获取单元格的值
if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)
{
if (merged)
{
dr[j] = dr[j - 1];
}
}
merged = true;
}
else
{
merged = false;
var firsLine = row.GetCell(j).ToString();
dr[j - cellRangeAddress.FirstColumn] = GetValueType(row.GetCell(j));
}
}
dt.Rows.Add(dr);
}
}
catch
{
throw new ArgumentException(ConfigStringHelper.ExcelError_ErrorRange);
}
}
return dt;
}
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
/// <summary>
/// 读取范围
/// </summary>
public static DataTable ReadRange(string fileName, string sheetName, string range, bool includeHeaders = false, bool isMergeColumn = false, bool isMergeRow = false)
{
FileStream fs = null;
try
{
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
List<string> resultList = new List<string>();
ISheet sheet = null;
IWorkbook workbook = null;
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new ArgumentException("ExcelError_NotSheet");
}
System.Data.DataTable dt = new System.Data.DataTable();
if (sheet != null)
{
try
{
CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(range);
int firstcolumn = cellRangeAddress.FirstColumn;
int lastcolumn = cellRangeAddress.LastColumn;
int firstrow = cellRangeAddress.FirstRow;
int lastrow = cellRangeAddress.LastRow;
if (!range.Contains(":"))
{
if (lastcolumn == firstcolumn)
{
IRow headrow = sheet.GetRow(firstrow);
lastcolumn = headrow.Cells.Count;
}
if (lastrow == firstrow)
lastrow = sheet.LastRowNum + 1;//得到总行数
}
int Column = lastcolumn - firstcolumn;
int startRow = firstrow;
List<int> columns = new List<int>();
//创建列头
if (includeHeaders)
{
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = null;
cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
columns.Add(i);
}
}
}
startRow = firstrow + 1;
}
else
{
for (int i = 0; i < Column; i++)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
columns.Add(i);
}
}
//数据
for (var i = startRow; i < lastrow; i++)
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
bool merged = false;
for (var j = firstcolumn; j < Column + firstcolumn; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
if (cell.IsMergedCell) //检测列的单元格是否合并
{
dr[j - firstcolumn] = GetValueType(cell); //获取单元格的值
if (isMergeColumn)
{
if (string.IsNullOrWhiteSpace(dr[j - firstcolumn].ToString()) && j - firstcolumn > 0)
{
if (merged)
{
dr[j - firstcolumn] = dr[j - firstcolumn - 1];
}
}
}
if (isMergeRow)
{
if (string.IsNullOrWhiteSpace(dr[j - firstcolumn].ToString()) && i - startRow > 0)
{
DataRow drRow = dt.Rows[dt.Rows.Count - 1];
dr[j - firstcolumn] = drRow[j - firstcolumn];
}
}
merged = true;
}
else
{
merged = false;
var firsLine = row.GetCell(j).ToString();
dr[j - firstcolumn] = GetValueType(row.GetCell(j));
}
}
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
throw new ArgumentException("ExcelError_ErrorRange");
}
}
return dt;
}
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
/// <summary>
/// 读取行
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="rowIndex">行号</param>
/// <param name="startColumnIndex">列号</param>
/// <returns>返回的DataTable</returns>
public static string[] ReadRow(string fileName, string sheetName, int rowIndex, int startColumnIndex)
{
FileStream fs = null;
try
{
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
List<string> resultList = new List<string>();
ISheet sheet = null;
IWorkbook workbook = null;
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
if (sheet != null)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
if (row != null)
{
for (int columnNumber = startColumnIndex - 1; columnNumber < row.LastCellNum; columnNumber++)
{
var cell = row.GetCell(columnNumber);
if (cell == null)
{
cell = row.CreateCell(columnNumber);
}
if (cell != null)
{
if (cell.CellType == CellType.Formula)
{
cell.SetCellType(CellType.String);
}
resultList.Add(cell.ToString());
}
}
}
}
return resultList.ToArray();
}
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
/// <summary>
/// 写入单元格
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="cell">单元格坐标</param>
/// <param name="value">写入的数据</param>
/// <returns>返回的DataTable</returns>
public static void WriteCell(string fileName, string sheetName, string cell, object value)
{
IWorkbook workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
//获取sheet的索引
}
if (SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
CellReference cf = new CellReference(cell);
var row = sheet.GetRow(cf.Row);
if (row == null)
{
row = sheet.CreateRow(cf.Row);
}
try
{
ICell icell = row.GetCell(cf.Col);
if (icell == null)
{
icell = row.CreateCell(cf.Col);
}
switch (icell.CellType)
{
case CellType.Formula:
icell.SetCellFormula(value.ToString());
break;
case CellType.Blank:
case CellType.Numeric:
if (value is DateTime)
{
icell.SetCellValue((DateTime)value);
}
else if (value is double)
{
icell.SetCellValue((double)value);
}
else
{
double doubleValue;
if (double.TryParse(value.ToString(), out doubleValue))
{
icell.SetCellValue(doubleValue);
}
else
{
icell.SetCellValue(value.ToString());
}
}
break;
case CellType.Boolean:
if (value is bool)
{
icell.SetCellValue((bool)value);
}
else
{
bool boolValue;
if (bool.TryParse(value.ToString(), out boolValue))
{
icell.SetCellValue(boolValue);
}
else
{
icell.SetCellValue(value.ToString());
}
}
break;
case CellType.Error:
if (value is byte)
{
icell.SetCellErrorValue((byte)value);
}
else
{
byte byteValue;
if (byte.TryParse(value.ToString(), out byteValue))
{
icell.SetCellErrorValue(byteValue);
}
else
{
icell.SetCellValue(value.ToString());
}
}
break;
case CellType.String:
default:
icell.SetCellValue(value.ToString());
break;
}
//Regex rx = new Regex(pattern);
//bool status = rx.IsMatch(value.ToString());
//if (status == true)
//{
// //纯数字
// icell.SetCellValue(Convert.ToDouble(value));
//}
//else
//{
// icell.SetCellValue(value.ToString());
//}
}
catch
{
throw new Exception(ConfigStringHelper.ExcelError_CellExist);
}
//这一句使用NPOI必须加，如不加在修改完单元格的值后 excel不会自动更新公式的值
sheet.ForceFormulaRecalculation = true;
}
//写入到excel中
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs1 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs1.Write(_data, 0, _data.Length);
fs1.Flush();
_data = null;
}
}
}
/// <summary>
/// 写入单元格
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="cell">单元格坐标</param>
/// <param name="value">写入的数据</param>
/// <returns>返回的DataTable</returns>
public static void WriteCell(ISheet sheet, string cell, object value)
{
CellReference cf = new CellReference(cell);
var row = sheet.GetRow(cf.Row);
if (row == null)
{
row = sheet.CreateRow(cf.Row);
}
try
{
if (value != null)
{
ICell icell = row.GetCell(cf.Col);
if (icell == null)
{
icell = row.CreateCell(cf.Col);
}
switch (icell.CellType)
{
case CellType.Formula:
icell.SetCellFormula(value.ToString());
break;
case CellType.Numeric:
if (value is DateTime)
{
icell.SetCellValue((DateTime)value);
}
else if (value is double)
{
icell.SetCellValue((double)value);
}
else
{
double doubleValue;
if (double.TryParse(value.ToString(), out doubleValue))
{
icell.SetCellValue(doubleValue);
}
else
{
icell.SetCellValue(value.ToString());
}
}
break;
case CellType.Boolean:
if (value is bool)
{
icell.SetCellValue((bool)value);
}
else
{
bool boolValue;
if (bool.TryParse(value.ToString(), out boolValue))
{
icell.SetCellValue(boolValue);
}
else
{
icell.SetCellValue(value.ToString());
}
}
break;
case CellType.Error:
if (value is byte)
{
icell.SetCellErrorValue((byte)value);
}
else
{
byte byteValue;
if (byte.TryParse(value.ToString(), out byteValue))
{
icell.SetCellErrorValue(byteValue);
}
else
{
icell.SetCellValue(value.ToString());
}
}
break;
case CellType.String:
default:
icell.SetCellValue(value.ToString());
break;
}
//Regex rx = new Regex(pattern);
//bool status = rx.IsMatch(value.ToString());
//if (status == true)
//{
// //纯数字
// icell.SetCellValue(Convert.ToDouble(value));
//}
//else
//{
// icell.SetCellValue(value.ToString());
//}
}
}
catch
{
throw new Exception(ConfigStringHelper.ExcelError_CellExist);
}
//这一句使用NPOI必须加，如不加在修改完单元格的值后 excel不会自动更新公式的值
sheet.ForceFormulaRecalculation = true;
}
/// <summary>
/// 写入指定行
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="rowIndex">单元格坐标</param>
/// <param name="data">写入的数据</param>
/// <returns>返回的DataTable</returns>
public static void WriteRow(string fileName, string sheetName, int rowIndex, IEnumerable<object> data, int startColumnIndex, bool isInsertRow = false)
{
FileStream fs = null;
try
{
IWorkbook workbook = null;
ISheet sheet = null;
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new Exception(ConfigStringHelper.ExcelError_NotSheet);
}
}
else
{
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
if (row != null)
{
int columnIndex = startColumnIndex;
foreach (var item in data)
{
if (item != null)
{
ICell icell = row.CreateCell(columnIndex);
if (icell == null)
{
icell = row.CreateCell(columnIndex);
}
Regex rx = new Regex(pattern);
bool status = rx.IsMatch(item.ToString());
if (status == true)
{
//纯数字
icell.SetCellValue(Convert.ToDouble(item));
}
else
{
icell.SetCellValue(item.ToString());
}
}
columnIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs1 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs1.Write(_data, 0, _data.Length);
fs1.Flush();
_data = null;
}
}
}
}
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
/// <summary>
/// 写入指定列
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="columnName">单元格坐标</param>
/// <param name="ColumnIndex">列索引</param>
/// <param name="data">写入的数据</param>
/// <param name="StartRowIndex">起始行号 从零开始</param>
/// <returns>返回的DataTable</returns>
public static void WriteColumn(string fileName, string sheetName, string columnName, int ColumnIndex, IEnumerable<object> data, int StartRowIndex, bool insertColumn = false)
{
string cell_Value = "";
List<object> inserValues = new List<object>();
foreach (var item in data)
{
inserValues.Add(item);
}
IWorkbook workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
try
{
ISheet sheet = null;
if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
throw new Exception(ConfigStringHelper.ExcelError_NotSheet);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
bool status = false;
var rowCount = sheet.LastRowNum + 1;//得到总行数
if (!string.IsNullOrEmpty(columnName))
{
IRow row = sheet.GetRow(0);
if (row == null)
{
row = sheet.CreateRow(0);
}
for (var j = 0; j < row.Cells.Count; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
var firsLine = row.GetCell(j).ToString();
if (firsLine == columnName)
{
if (status == false)
{
cell_Value = columnName;
status = true;
ColumnIndex = j;
}
//记录所在的列，只记录一次如果出现多个同名列只计算第一列
}
}
}
if (string.IsNullOrEmpty(cell_Value) && !string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException(ConfigStringHelper.ExcelError_SheetNotcolumnName);
}
int targetColumn = StartRowIndex - 1;
int k = 0;
int Index = 0;
if (status == true)
{
Index = ColumnIndex;
}
else
{
Index = ColumnIndex - 1;
}
for (int rows = 0; rows < inserValues.Count(); rows++)
{
IRow row = sheet.GetRow(targetColumn);
if (row == null)
{
row = sheet.CreateRow(targetColumn);
}
ICell cell = row.GetCell(Index);
if (cell == null)
{
cell = row.CreateCell(Index);
}
Regex rx = new Regex(pattern);
bool statued = rx.IsMatch(inserValues[k].ToString());
if (statued == true)
{
//纯数字
cell.SetCellValue(Convert.ToDouble(inserValues[k]));
}
else
{
cell.SetCellValue(inserValues[k].ToString());
}
k++;
targetColumn++;
}
}
//这一句使用NPOI必须加，如不加在修改完单元格的值后 excel不会自动更新公式的值
sheet.ForceFormulaRecalculation = true;
}
finally
{
if (fs != null)
{
fs.Dispose();
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs.Write(_data, 0, _data.Length);
fs.Flush();
_data = null;
}
}
}
public static void InsertRow(string FilePath, string sheetName, IEnumerable<object> data, int insertrowIndex, int startColumnIndex)
{
List<object> inserValues = new List<object>();
foreach (var item in data)
{
inserValues.Add(item);
}
IWorkbook workbook = null;
//读取流
using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
{ //2007版本的excel
workbook = new XSSFWorkbook(fs);
}
else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
//获取sheet的索引
}
if (string.IsNullOrEmpty(sheetName))
{
//没有填写默认sheet，则获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
else
{
if (SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
}
//插入行
sheet.ShiftRows(insertrowIndex - 1, sheet.LastRowNum + insertrowIndex - 1, 1, true, true);
IRow row = sheet.GetRow(insertrowIndex - 1);
if (row == null)
{
row = sheet.CreateRow(insertrowIndex - 1);
}
for (var j = 0; j < inserValues.Count; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
if (inserValues[j] != null)
{
Regex rx = new Regex(pattern);
bool status = rx.IsMatch(inserValues[j].ToString());
if (status == true)
{
//纯数字
cell.SetCellValue(Convert.ToDouble(inserValues[j]));
}
else
{
cell.SetCellValue(inserValues[j].ToString());
}
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs.Write(_data, 0, _data.Length);
fs.Flush();
_data = null;
}
}
}
//插入空行
private static void InsertRowCount(string FilePath, string sheetName, int rowCount, int insertrowIndex, int startColumnIndex)
{
IWorkbook workbook = null;
//读取流
using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
{ //2007版本的excel
workbook = new XSSFWorkbook(fs);
}
else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
//获取sheet的索引
}
if (string.IsNullOrEmpty(sheetName))
{
//没有填写默认sheet，则获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
else
{
if (SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
}
//插入行
sheet.ShiftRows(insertrowIndex - 1, sheet.LastRowNum + insertrowIndex - 1, 1, true, true);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs.Write(_data, 0, _data.Length);
fs.Flush();
_data = null;
}
}
}
//实现在指定列前插入列
/// <summary>
///
/// </summary>
/// <param name="FilePath">excel路径</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="data">数据源</param>
/// <param name="columnName">指定的内容列名</param>
/// <param name="insertColumnIndex">列索引</param>
/// <param name="StartRowIndex">从第几行开始</param>
public static void InsertColumn(string FilePath, string sheetName, IEnumerable<object> data, string columnName, int insertColumnIndex, int StartRowIndex)
{
string cell_Value = "";
List<object> inserValues = new List<object>();
foreach (var item in data)
{
inserValues.Add(item);
}
IWorkbook workbook = null;
//读取流
using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
{ //2007版本的excel
workbook = new XSSFWorkbook(fs);
}
else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
{
workbook = new HSSFWorkbook(fs);
}
bool SheetStatus = false;
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
//获取sheet的索引
}
if (string.IsNullOrEmpty(sheetName))
{
//没有填写默认sheet，则获取第一个sheet
sheet = workbook.GetSheetAt(0);
SheetStatus = true;
}
else
{
if (SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
}
bool status = false;
var rowCount = sheet.LastRowNum + 1;//得到总行数
if (!string.IsNullOrEmpty(columnName))
{
IRow row = sheet.GetRow(0);
if (row == null)
{
row = sheet.CreateRow(0);
}
for (var j = 0; j < row.Cells.Count; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
cell = row.CreateCell(j);
}
var firsLine = row.GetCell(j).ToString();
if (firsLine == columnName)
{
if (status == false)
{
cell_Value = columnName;
status = true;
insertColumnIndex = j;
}
//记录所在的列，只记录一次如果出现多个同名列只计算第一列
}
}
}
if (string.IsNullOrEmpty(cell_Value) && !string.IsNullOrEmpty(columnName))
{
throw new ArgumentNullException(ConfigStringHelper.ExcelError_SheetNotcolumnName);
}
//z这里遍历得到最大列数，因为存在第一行残缺为空的情况
List<int> array = new List<int>();
for (var i = 0; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
//array.Add(row.Cells.Count);//获取有效列
array.Add(row.LastCellNum);//获取所有列
}
var maxValue = array.Max();//得到最大列数
//创建列头
System.Data.DataTable dt = new System.Data.DataTable();
List<int> columns = new List<int>();
for (int i = 0; i < maxValue; i++)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
}
dt.Rows.Add(dr);
}
int Index = 0;
if (status == true)
{
Index = insertColumnIndex;
}
else
{
Index = insertColumnIndex - 1;
}
//在指定的列前插入列
dt.Columns.Add("custom999").SetOrdinal(Index);
int J = 0;
int StartIndex = StartRowIndex - 1;
for (int k = StartIndex; k < inserValues.Count() + StartIndex; k++)
{
if (dt.Rows.Count + StartIndex <= k)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
dt.Rows[k]["custom999"] = inserValues[J];
J++;
}
//因为使用datatable处理所以要先删除原有sheet，创建新sheet导入数据
if (SheetStatus == true)
{
//说明是使用默认sheet
workbook.RemoveSheetAt(workbook.GetSheetIndex(SheetName[0]));
sheet = workbook.CreateSheet(SheetName[0]);
}
else
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
sheet = workbook.CreateSheet(sheetName);
}
int WriteRowCount = 0;
//写入数据
for (int row = 0; row < dt.Rows.Count; row++)
{
IRow newRow;
newRow = sheet.CreateRow(WriteRowCount);
for (int column = 0; column < dt.Columns.Count; column++)
{
newRow.CreateCell(column).SetCellValue(dt.Rows[row][column].ToString());
}
WriteRowCount++;
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
{
byte[] _data = ms.ToArray();
fs.Write(_data, 0, _data.Length);
fs.Flush();
_data = null;
}
}
}
public static DataTable ReadTable(string FilePath, string sheetName, int startIndex, int endIndex)
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (startIndex > 0)
{
if (startIndex > endIndex)
{
throw new ArgumentException(ConfigStringHelper.ExcelError_ArgumentExceptionRowJudge);
}
}
else
{
if (endIndex > 1)
{
throw new ArgumentException(ConfigStringHelper.ExcelError_ArgumentExceptionRowNotStartIndex);
}
}
if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
{ //2007版本的excel
workbook = new XSSFWorkbook(fs);
}
else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);//获取工作表
int SheetCount = workbook.NumberOfSheets;//获取表的数量
string[] SheetName = new string[SheetCount];//保存表的名称
for (int i = 0; i < SheetCount; i++)
{
SheetName[i] = workbook.GetSheetName(i).ToLower();
//获取sheet的索引
}
if (SheetName.Contains(sheetName.ToLower()))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
}
var rowCount = sheet.LastRowNum + 1;//得到总行数
//z这里遍历得到最大列数，因为存在第一行残缺为空的情况
List<int> array = new List<int>();
for (var i = 0; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
array.Add(row.LastCellNum);//获取所有列(包含空列以及残缺)
}
var maxValue = array.Max();//得到最大列数
int Index = 0;
int Endex = 0;
List<int> columns = new List<int>();
for (int i = 0; i < maxValue; i++)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
columns.Add(i);
}
if (startIndex > 1)
{
Index = startIndex;
}
if (endIndex > 1)
{
Endex = endIndex;
}
else
{
Endex = sheet.LastRowNum;
}
//数据
for (int i = Index; i <= Endex; i++)
{
DataRow dr = dt.NewRow();
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
}
dt.Rows.Add(dr);
}
}
return dt;
}
}