using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
namespace ISCS_AutoTest
{
public class NPOIOperator
{
/// <summary>
/// Excel数据文件物理路径
/// </summary>
private string dataFilePath = null;
/// <summary>
/// Excel模板文件物理路径，根据模板生成时需要
/// </summary>
private string templateFilePath = null;
/// <summary>
/// NPOI Excel工作簿操作接口
/// </summary>
private IWorkbook workbook = null;
/// <summary>
/// 内容样式字典
/// </summary>
private Dictionary<string, ICellStyle> cellStyleDictionary;
/// <summary>
/// 标题样式
/// </summary>
private ICellStyle titleStyle;
/// <summary>
/// 写入时是否使用内置样式
/// </summary>
private bool isUseBuiltInStyle;
/// <summary>
/// 单元格数据类型
/// </summary>
public enum CellDataType
{
None = 0,
Null = 1,
Text,
Date,
DateTime,
Int,
Double,
Formula,
Boolean,
RichText
}
/// <summary>
/// 初始化构造函数
/// </summary>
/// <param name="dataFilePath">Excel数据文件物理路径</param>
public NPOIOperator(string dataFilePath, bool isUseBuiltInStyle)
: this(string.Empty, dataFilePath, isUseBuiltInStyle)
{
}
/// <summary>
/// 初始化构造函数
/// </summary>
/// <param name="templateFilePath">Excel模板文件物理路径</param>
/// <param name="dataFilePath">Excel数据文件物理路径</param>
public NPOIOperator(string templateFilePath, string dataFilePath, bool isUseBuiltInStyle)
{
this.isUseBuiltInStyle = isUseBuiltInStyle;
this.dataFilePath = dataFilePath;
this.templateFilePath = templateFilePath;
CreateWorkbook();
InitTitleStyle();
InitContentStyle();
}
/// <summary>
/// 初始化内容样式
/// </summary>
private void InitContentStyle()
{
cellStyleDictionary = new Dictionary<string, ICellStyle>();
// 内容字体
IFont contentFont = workbook.CreateFont();
contentFont.FontHeightInPoints = 9;
contentFont.FontName = "宋体";
IDataFormat format = workbook.CreateDataFormat();
// 日期样式
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.SetFont(contentFont);
dateStyle.WrapText = false;
dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
cellStyleDictionary.Add("date", dateStyle);
// 日期时间样式
ICellStyle dateTimeStyle = workbook.CreateCellStyle();
dateTimeStyle.SetFont(contentFont);
dateTimeStyle.WrapText = false;
dateTimeStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
dateTimeStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
cellStyleDictionary.Add("datetime", dateStyle);
// Money样式
ICellStyle doubleStyle = workbook.CreateCellStyle();
doubleStyle.SetFont(contentFont);
doubleStyle.WrapText = false;
doubleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
doubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyleDictionary.Add("double", doubleStyle);
// 文本样式
ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.SetFont(contentFont);
textStyle.WrapText = false;
textStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleDictionary.Add("text", textStyle);
}
/// <summary>
/// 初始化标题样式
/// </summary>
private void InitTitleStyle()
{
// 标题字体
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 9;
titleFont.FontName = "宋体";
titleFont.Boldweight = (short)FontBoldWeight.Bold;
// 标题样式
titleStyle = workbook.CreateCellStyle();
titleStyle.WrapText = false;
titleStyle.SetFont(titleFont);
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
titleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
}
/// <summary>
/// 根据Sheet表名称获取Sheet表
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public ISheet GetSheet(string sheetName)
{
if (string.IsNullOrWhiteSpace(sheetName))
{
throw new ArgumentNullException("sheetName为空");
}
return workbook.GetSheet(sheetName);
}
#region 读数据
/// <summary>
/// 自动适应列宽
/// </summary>
/// <param name="sheet">需要自适应列宽的sheet表</param>
/// <param name="columnCount">列数</param>
public void AutoFitColumnWidth(ISheet sheet, int columnCount)
{
//列宽自适应，只对英文和数字有效
for (int ci = 0; ci < columnCount; ci++)
{
sheet.AutoSizeColumn(ci);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum < columnCount; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
{
if (rowNum == 0 || rowNum == sheet.LastRowNum - 1 || rowNum == sheet.LastRowNum / 2)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
}
if (columnWidth > 255)
{
columnWidth = 255;
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
/// <summary>
/// 将Sheet表数据读取到DataTable
/// </summary>
/// <param name="sheetName">Excel Sheet表名</param>
/// <param name="isReadTitle">是否将Sheet表标题作为DataTable列名</param>
/// <returns>读取到的数据，如果Sheet表不存在则返回null</returns>
public DataTable ReadSheet(string sheetName, bool isReadTitle)
{
ISheet sheet = null;
DataTable data = null;
if (workbook == null)
{
throw new NullReferenceException("workbook为null，请先调用Open方法初始化workbook");
}
if (!string.IsNullOrWhiteSpace(sheetName))
{
sheet = GetSheet(sheetName);
}
if (sheet != null)
{
data = ReadSheet(sheet, isReadTitle);
}
return data;
}
/// <summary>
/// 将Sheet表数据读取到DataTable
/// </summary>
/// <param name="sheet">Sheet表</param>
/// <param name="isReadTitle">是否将Sheet表标题作为DataTable列名</param>
/// <returns>读取到的数据，如果Sheet表不存在则返回null</returns>
public DataTable ReadSheet(ISheet sheet, bool isReadTitle)
{
return ReadSheet(sheet, isReadTitle, 0);
}
/// <summary>
/// 将Sheet表数据读取到DataTable
/// </summary>
/// <param name="sheet">Sheet表</param>
/// <param name="isReadTitle">是否将Sheet表标题作为DataTable列名</param>
/// <param name="startRowNumber">起始行号</param>
/// <returns>读取到的数据，如果Sheet表不存在则返回null</returns>
public DataTable ReadSheet(ISheet sheet, bool isReadTitle, int startRowNumber)
{
return ReadSheet(sheet, isReadTitle, false, startRowNumber);
}
/// <summary>
/// 将Sheet表数据读取到DataTable
/// </summary>
/// <param name="sheet">Sheet表</param>
/// <param name="isReadTitle">是否将Sheet表标题作为DataTable列名</param>
/// <param name="useCellType">是否使用单元格的数据类型格式化数据，如果列中的数据类型都是一致的可以设置为true，否则使用DataTable中列的数据类型读取数据。</param>
/// <param name="startRowNumber">起始行号</param>
/// <param name="startColNumber">起始列号</param>
/// <returns>读取到的数据，如果Sheet表不存在则返回null</returns>
public DataTable ReadSheet(ISheet sheet, bool isReadTitle, bool useCellType, int startRowNumber, int? startColNumber = null)
{
// TODO:添加读取结束行号和结束列号参数
DataTable data = null;
if (sheet != null)
{
data = new DataTable();
// 是否读取标题行
if (isReadTitle)
{
IRow titleRow = sheet.GetRow(startRowNumber);
var cols = ReadTitle(titleRow, startColNumber);
if (cols != null)
{
data.Columns.AddRange(cols);
}
startRowNumber++;
}
else
{
// 如果不读取标题行，则从数据第一行获取列数
var row = sheet.GetRow(startRowNumber);
if (row != null)
{
if (!startColNumber.HasValue)
{
startColNumber = row.FirstCellNum;
}
var colCount = row.LastCellNum - startColNumber.Value;
for (int i = 0; i < colCount; i++)
{
data.Columns.Add("列" + (i + 1));
}
}
}
// 读取数据
ReadContent(sheet, startRowNumber, data, useCellType);
}
return data;
}
/// <summary>
/// 获取Sheet表的标题，读取第一行
/// </summary>
/// <param name="sheet">sheet表</param>
/// <returns>标题列数组</returns>
public DataColumn[] ReadTitle(ISheet sheet)
{
IRow firstRow = sheet.GetRow(0);
if (firstRow == null)
{
return new DataColumn[0];
}
return ReadTitle(firstRow);
}
/// <summary>
/// 从Row实例获取标题
/// </summary>
/// <param name="row">Row实例</param>
/// <param name="startColNumber">起始列号</param>
/// <returns>标题列数组</returns>
public DataColumn[] ReadTitle(IRow row, int? startColNumber = null)
{
if (!startColNumber.HasValue)
{
startColNumber = row.FirstCellNum;
}
int cellCount = row.LastCellNum - startColNumber.Value;
DataColumn[] cols = new DataColumn[cellCount];
int j = 0;
for (int i = startColNumber.Value; i < row.LastCellNum; i++)
{
if(row.GetCell(i) == null)
{
DataColumn column2 = new DataColumn("");
cols[j] = column2;
j++;
}
else
{
bool hasSameColumnName = false;
for(int k = startColNumber.Value; k < i; k++)
{
if(cols[k] != null)
{
if (cols[k].ToString() == row.GetCell(i, MissingCellPolicy.RETURN_NULL_AND_BLANK).StringCellValue)
{
hasSameColumnName = true;
break;
}
}
}
if(hasSameColumnName)
{
DataColumn column = new DataColumn(row.GetCell(i, MissingCellPolicy.RETURN_NULL_AND_BLANK).StringCellValue+j.ToString());
cols[j] = column;
j++;
}
else
{
DataColumn column = new DataColumn(row.GetCell(i, MissingCellPolicy.RETURN_NULL_AND_BLANK).StringCellValue);
cols[j] = column;
j++;
}
}
}
return cols;
}
/// <summary>
/// 读取sheet表数据到DataTable中
/// </summary>
/// <param name="sheet">sheet表</param>
/// <param name="startRowNumber">起始行号</param>
/// <param name="table">要读入数据的DataTable</param>
/// <returns>读取的数据行数</returns>
public int ReadContent(ISheet sheet, int startRowNumber, DataTable table)
{
return ReadContent(sheet, startRowNumber, table, false);
}
/// <summary>
/// 读取sheet表数据到DataTable中
/// </summary>
/// <param name="sheet">sheet表</param>
/// <param name="startRowNumber">起始行号</param>
/// <param name="table">要读入数据的DataTable</param>
/// <param name="useCellType">是否使用单元格的数据类型格式化数据，如果列中的数据类型都是一致的可以设置为true，否则使用DataTable中列的数据类型读取数据。</param>
/// <returns>读取的数据行数</returns>
public int ReadContent(ISheet sheet, int startRowNumber, DataTable table, bool useCellType)
{
int endRowNumber = sheet.LastRowNum;
return ReadContent(sheet, startRowNumber, endRowNumber, table, useCellType);
}
/// <summary>
/// 读取sheet表数据到DataTable中
/// </summary>
/// <param name="sheet">sheet表</param>
/// <param name="startRowNumber">起始行号</param>
/// <param name="endRowNumber">终止行号</param>
/// <param name="table">要读入数据的DataTable</param>
/// <returns>读取的数据行数</returns>
public int ReadContent(ISheet sheet, int startRowNumber, int endRowNumber, DataTable table)
{
return ReadContent(sheet, startRowNumber, endRowNumber, table, false);
}
/// <summary>
/// 读取sheet表数据到DataTable中
/// </summary>
/// <param name="sheet">sheet表</param>
/// <param name="startRowNumber">起始行号</param>
/// <param name="endRowNumber">终止行号</param>
/// <param name="table">要读入数据的DataTable</param>
/// <param name="useCellType">是否使用单元格的数据类型格式化数据，如果列中的数据类型都是一致的可以设置为true，否则使用DataTable中列的数据类型读取数据。</param>
/// <returns>读取的数据行数</returns>
private int ReadContent(ISheet sheet, int startRowNumber, int endRowNumber, DataTable table, bool useCellType)
{
int nRowCount = 0;
int nColCount = 0;
int nRowNum = 0;
GetSheetRowCountAndColCount(sheet, ref nRowCount, ref nColCount, ref nRowNum);
for (int rowIndex = 0; rowIndex < nRowCount; rowIndex++)
{
bool result = false;
DataRow dRow = table.NewRow();
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
for (int colIndex = 0; colIndex < nColCount; colIndex++)
{
ICell cell = row.GetCell(colIndex);
if (cell != null)
{
try
{
dRow[colIndex] = GetCellValue(cell);//获取单元格的值
}
catch
{
dRow[colIndex] = "";
}
}
if (dRow[colIndex].ToString() != "")//全为空则不取
{
result = true;
}
}
if (result == true)
{
table.Rows.Add(dRow); //把每行追加到DataTable
}
}
return 1;
}
private void GetSheetRowCountAndColCount(ISheet sheetbook, ref int nRowCount, ref int nColCount, ref int nRowNum)
{
try
{
nRowNum = 0;
nRowCount = 0;
nColCount = 0;
IRow firstRow = sheetbook.GetRow(0);
if (firstRow == null)
{
Console.WriteLine("firstRow == null");
return;
}
int nCellCount = 0;
ICell headcell = sheetbook.GetRow(0).GetCell(0);
if (headcell != null)
{
int nrowspan = 0;
int ncolspan = 0;
if (IsMergeCell(sheetbook, 1, 1, out nrowspan, out ncolspan))
{
if (ncolspan == 1)
{
nCellCount = firstRow.LastCellNum;
}
else
{
nCellCount = ncolspan;
}
}
else
{
nCellCount = firstRow.LastCellNum;
}
}
else
{
nCellCount = firstRow.LastCellNum;
}
nColCount = nCellCount;
for (int nRowIndex = 0; nRowIndex < sheetbook.LastRowNum; nRowIndex++)
{
Console.WriteLine("nRowIndex ==" + nRowIndex.ToString());
IRow row = sheetbook.GetRow(nRowIndex);
if (row == null)
{
nRowCount = nRowIndex + 1;
return;
}
int nIndex = 0;
for (int i = 0; i < nCellCount + 1; i++)
{
ICell cell = row.GetCell(i);
if (i == 0 && cell != null && "1" == cell.ToString())//数据行的第一格内容为序号 1，从此行开始按照数据内容写入datatable
{
nRowNum = nRowIndex;
}
if (cell != null && cell.CellType != CellType.Blank && string.IsNullOrEmpty(cell.ToString().Trim()))//有数据
{
nIndex++;
}
}
if (nIndex == nCellCount)
{
nRowCount = nRowIndex + 1;
break;
}
}
if (nRowCount == 0) //文件中无空白行
{
nRowCount = sheetbook.LastRowNum + 1;
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
nRowCount = sheetbook.LastRowNum;
}
}
/// <summary>
/// 是否为组合框
/// </summary>
/// <param name="sheetbook"></param>
/// <param name="nRowNum"></param>
/// <param name="nColNum"></param>
/// <param name="nRowSpan"></param>
/// <param name="nColSpan"></param>
/// <returns></returns>
private bool IsMergeCell(ISheet sheetbook, int nRowNum, int nColNum, out int nRowSpan, out int nColSpan)
{
bool isMerge = false;
nRowSpan = 0;
nColSpan = 0;
if ((nRowNum < 1) || (nColNum < 1))
{
return isMerge;
}
int nRowIndex = nRowNum - 1;
int nColIndex = nColNum - 1;
int nRegionsCount = sheetbook.NumMergedRegions;
nRowSpan = 1;
nColSpan = 1;
for (int i = 0; i < nRegionsCount; i++)
{
CellRangeAddress range = sheetbook.GetMergedRegion(i);
sheetbook.IsMergedRegion(range);
if (range.FirstRow == nRowIndex && range.FirstColumn == nColIndex)
{
nRowSpan = range.LastRow - range.FirstRow + 1;
nColSpan = range.LastColumn - range.FirstColumn + 1;
break;
}
}
try
{
isMerge = sheetbook.GetRow(nRowIndex).GetCell(nColIndex).IsMergedCell;
}
catch
{
return false;
}
return isMerge;
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="tCell">单元格</param>
/// <returns></returns>
private static string GetCellValue(ICell tCell)
{
string tempValue = "";
switch (tCell.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
tempValue = tCell.BooleanCellValue.ToString();
break;
case CellType.Error:
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(tCell))
{
tempValue = tCell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
tempValue = tCell.NumericCellValue.ToString();
}
break;
case CellType.String:
tempValue = tCell.StringCellValue.Trim();
break;
default:
tempValue = tCell.ToString();
break;
}
return tempValue;
}
/// <summary>
/// 读取一行数据
/// </summary>
/// <param name="row"></param>
/// <param name="cols"></param>
/// <returns></returns>
public object[] ReadRow(IRow row, DataColumnCollection cols)
{
return ReadRow(row, cols, false);
}
/// <summary>
/// 读取一行数据
/// </summary>
/// <param name="row">行对象</param>
/// <param name="cols">列集合</param>
/// <param name="useCellType">是否使用单元格的数据类型格式化数据，如果列中的数据类型都是一致的可以设置为true，否则使用DataTable中列的数据类型读取数据。</param>
/// <returns></returns>
public object[] ReadRow(IRow row, DataColumnCollection cols, bool useCellType)
{
if (cols.Count == 0)
{
return null;
}
var lastCellNum = row.FirstCellNum + cols.Count - 1;
int i = 0;
object[] data = new object[cols.Count];
for (int j = row.FirstCellNum; j <= lastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell != null)
{
CellDataType cellDataType = CellDataType.Text;
if (useCellType)
{
cellDataType = GetCellDataTypeByCell(cell);
}
else
{
cellDataType = GetCellDataTypeByColumn(cols[i]);
}
data[i] = ReadCell(cell, cellDataType);
}
i++;
}
return data;
}
/// <summary>
/// 读取单元格数据
/// </summary>
/// <param name="sheet">要读取数据的sheet表实例</param>
/// <param name="rowNumber">要读取数据的行号</param>
/// <param name="columnNumber">要读取数据的列号</param>
public object ReadCell(ISheet sheet, int rowNumber, int columnNumber, CellDataType cellDataType)
{
var row = sheet.GetRow(rowNumber);
if (row != null)
{
return ReadCell(row, columnNumber, cellDataType);
}
return null;
}
/// <summary>
/// 读取单元格数据
/// </summary>
/// <param name="row">要读取数据的行实例</param>
/// <param name="columnNumber">要读取数据的列号</param>
public object ReadCell(IRow row, int columnNumber, CellDataType cellDataType)
{
var cell = row.GetCell(columnNumber);
if (cell != null)
{
return ReadCell(cell, cellDataType);
}
return null;
}
/// <summary>
/// 读取单元格数据
/// </summary>
/// <param name="cell">要读取数据的单元格</param>
public object ReadCell(ICell cell, CellDataType cellDataType)
{
if (cellDataType == CellDataType.Boolean)
{
return cell.BooleanCellValue;
}
else if (cellDataType == CellDataType.Date)
{
return cell.DateCellValue;
}
else if (cellDataType == CellDataType.DateTime)
{
return cell.DateCellValue;
}
else if (cellDataType == CellDataType.Double)
{
return cell.NumericCellValue;
}
else if (cellDataType == CellDataType.Formula)
{
return cell.CellFormula;
}
else if (cellDataType == CellDataType.Int)
{
return (int)cell.NumericCellValue;
}
else if (cellDataType == CellDataType.RichText)
{
return cell.RichStringCellValue;
}
else if (cellDataType == CellDataType.Text)
{
return cell.ToString();
}
else if (cellDataType == CellDataType.None)
{
return string.Empty;
}
else if (cellDataType == CellDataType.Null)
{
return null;
}
return null;
}
#endregion
/// <summary>
/// 根据模板或数据excel文件创建工作簿实例
/// </summary>
/// <returns></returns>
private IWorkbook CreateWorkbook()
{
if (!string.IsNullOrEmpty(templateFilePath))
{
using (var templateFile = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (templateFilePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(templateFile);
}
else if (templateFilePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(templateFile);
}
}
}
else
{
if (File.Exists(dataFilePath))
{
using (var dataFile = new FileStream(dataFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (dataFilePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(dataFile);
}
else if (dataFilePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(dataFile);
}
}
}
else
{
if (dataFilePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook();
}
else if (dataFilePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook();
}
}
}
return workbook;
}
/// <summary>
/// 使用CellType初始化列的数据类型
/// </summary>
/// <param name="cols"></param>
/// <param name="rowData"></param>
private static void InitColumnTypeWithCellType(DataColumnCollection cols, object[] rowData)
{
for (int k = 0; k < rowData.Length; k++)
{
var o = rowData[k];
if (o != null)
{
if (cols.Count > k)
{
cols[k].DataType = o.GetType();
}
}
}
}
/// <summary>
/// 根据列属性获取数据类型
/// </summary>
/// <param name="column">列构造实例</param>
/// <returns></returns>
private CellDataType GetCellDataTypeByColumn(DataColumn column)
{
var cellDataType = CellDataType.None;
if (isUseBuiltInStyle)
{
if (column.ExtendedProperties != null && column.ExtendedProperties.Count > 0)
{
if (column.ExtendedProperties.ContainsKey("DataType"))
{
var propertyValue = column.ExtendedProperties["DataType"].ToString();
cellDataType = GetDataTypeFromColumnExtendedProperty(propertyValue);
}
}
else
{
Type columnDataType = column.DataType;
cellDataType = GetDataTypeFromColumnDataType(columnDataType);
}
}
return cellDataType;
}
/// <summary>
/// 根据单元格类型获取数据类型
/// </summary>
/// <param name="cellType">单元格类型</param>
/// <returns></returns>
private CellDataType GetCellDataTypeByCell(ICell cell)
{
if (cell != null)
{
var cellType = cell.CellType;
var cellDataType = CellDataType.None;
var cellStyle = cell.CellStyle;
var cellFormaStringt = cellStyle.GetDataFormatString();
if (cellType == CellType.Blank)
{
cellDataType = CellDataType.None;
}
else if (cellType == CellType.Boolean)
{
cellDataType = CellDataType.Boolean;
}
else if (cellType == CellType.Error)
{
cellDataType = CellDataType.Text;
}
else if (cellType == CellType.Formula)
{
cellDataType = CellDataType.Formula;
}
else if (cellType == CellType.Numeric)
{
cellDataType = CellDataType.Double;
// TODO:简单点可以尝试访问 cell.DateCellValue，不过可能引发异常
if (cellFormaStringt == "m/d/yy" || cellFormaStringt == "yyyy-MM-dd")
{
cellDataType = CellDataType.Date;
}
else if (cellFormaStringt == "m/d/yy h:mm" || cellFormaStringt == "yyyy-MM-dd HH:mm:ss")
{
cellDataType = CellDataType.DateTime;
}
else if (!cell.NumericCellValue.ToString().Contains("."))
{
cellDataType = CellDataType.Int;
}
}
else if (cellType == CellType.String)
{
cellDataType = CellDataType.Text;
}
else if (cellType == CellType.Unknown)
{
cellDataType = CellDataType.Text;
}
return cellDataType;
}
return CellDataType.None;
}
/// <summary>
/// 从数据列的类型获取DataType
/// </summary>
/// <param name="type">数据类型</param>
/// <returns>单元格数据类型</returns>
private static CellDataType GetDataTypeFromColumnDataType(Type type)
{
var dataType = CellDataType.None;
if (type == typeof(DateTime))
{
dataType = CellDataType.DateTime;
}
else if (type == typeof(int))
{
dataType = CellDataType.Int;
}
else if (type == typeof(double))
{
dataType = CellDataType.Double;
}
else if (type == typeof(decimal))
{
dataType = CellDataType.Double;
}
else if (type == typeof(bool))
{
dataType = CellDataType.Boolean;
}
else
{
dataType = CellDataType.Text;
}
return dataType;
}
/// <summary>
/// 从列的扩展属性获取DataType
/// </summary>
/// <param name="propertyValue">属性值</param>
/// <returns>单元格数据类型</returns>
private static CellDataType GetDataTypeFromColumnExtendedProperty(string propertyValue)
{
var dataType = CellDataType.None;
switch (propertyValue)
{
case "Null":
dataType = CellDataType.Null;
break;
case "Date":
dataType = CellDataType.Date;
break;
case "DateTime":
dataType = CellDataType.DateTime;
break;
case "Int":
dataType = CellDataType.Int;
break;
case "Double":
dataType = CellDataType.Double;
break;
case "Boolean":
dataType = CellDataType.Boolean;
break;
case "Formula":
dataType = CellDataType.Formula;
break;
case "RichText":
dataType = CellDataType.RichText;
break;
default:
dataType = CellDataType.Text;
break;
}
return dataType;
}
}
}
1480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
