EXcel导入

private void OpenFile()

{

OpenFileDialog openFileDialog = new OpenFileDialog();

openFileDialog.Filter = string.Format("(*.xls;*.xlsx)|*.xls;*.xlsx");

openFileDialog.Multiselect = true;

openFileDialog.RestoreDirectory = true;

openFileDialog.FilterIndex = 1;

if (openFileDialog.ShowDialog() != System.Windows.Forms.DialogResult.Cancel)

{

//获取文件名

string file_name = openFileDialog.SafeFileName;

string file_path = openFileDialog.FileName;

string[] file_names = openFileDialog.FileNames;

if (file_name.LastIndexOf(".") <= 0)

{

return;

}

OpenFile(file_names);

}

}

public void OpenFile(string[] readPaths)

{

for (int i = 0; i < readPaths.GetLength(0); i++)

{

string readPath = readPaths[i];

OpenFile(readPath);

}

}

protected void OpenFile(string strName)

{

try

{

ImportProperty(strName);

}

catch (System.Exception ex)

{

}

}

private void ImportProperty(string strExcelName)

{

List<DataTable> listTable = new List<DataTable>();

DataTable newTable = ExcelToDataTable(strExcelName);

listTable.Add(newTable);

//遍历数据集信息

foreach (DataTable table in listTable)

{

DataTable _staticPropTable = table;//导入带矩阵坐标，颜色，透明度excel的时候是该选项

ImportMatrixColorTrans(_staticPropTable);

}

}

public DataTable ExcelToDataTable(string filePath)

{

//获取后缀名

DataTable dt = new DataTable();

string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();

FileStream FStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);

XSSFWorkbook WorkBooks = new XSSFWorkbook(FStream);

if (WorkBooks == null)

{

return null;

}

IWorkbook wk = WorkBooks;

//判断是否是excel文件

if (extension == ".xlsx" || extension == ".xls")

{

//获取第一个sheet

ISheet sheet = wk.GetSheetAt(0);

//获取第一行

IRow headrow = sheet.GetRow(0);

//创建列

for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)

{

ICell cell = headrow.GetCell(i);

string strValue = cell.ToString();

if (cell.IsMergedCell)

{

var cellValue = GetCellValue(cell);

if (string.IsNullOrEmpty(cellValue) && i > headrow.FirstCellNum)

{

strValue = "标题合并空格" + i.ToString();

}

}

dt.Columns.Add(strValue);

}

//读取每行,从第二行起

for (int r = 1; r <= sheet.LastRowNum; r++)

{

IRow row = sheet.GetRow(r);

if (row == null)

{

continue;

}

bool result = false;

DataRow dr = dt.NewRow();

//获取当前行

//读取每列

for (int j = 0; j < dt.Columns.Count; j++)

{

ICell cell = row.GetCell(j); //一个单元格

if (cell == null)

{

continue;

}

if (cell.IsMergedCell && r > 1) //检测列的单元格是否合并

{

//dr[j] = dt.Rows[r - 2][j];

var cellValue = GetCellValue(cell);

if (string.IsNullOrEmpty(cellValue))

{

dr[j] = dt.Rows[r - 2][j];

}

else

{

dr[j] = cellValue; //获取单元格的值

if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)

{

dr[j] = dr[j - 1];

}

}

}

else

{

dr[j] = GetCellValue(cell); //获取单元格的值

}

if (dr[j].ToString() != "")//全为空则不取

{

result = true;

}

}

if (result == true)

{

dt.Rows.Add(dr); //把每行追加到DataTable

}

}

}

return dt;

}

private static string GetCellValue(ICell cell)

{

if (cell == null)

return string.Empty;

switch (cell.CellType)

{

case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)

return string.Empty;

case CellType.Boolean: //bool类型

return cell.BooleanCellValue.ToString();

case CellType.Error:

return cell.ErrorCellValue.ToString();

case CellType.Numeric: //数字类型

if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型

{

return cell.DateCellValue.ToString();

}

else //其它数字

{

return cell.NumericCellValue.ToString();

}

case CellType.Unknown: //无法识别类型

default: //默认类型

return cell.ToString();//

case CellType.String: //string 类型

{

if (cell.IsMergedCell) { }

return cell.StringCellValue;

}

case CellType.Formula: //带公式类型

try

{

HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);

e.EvaluateInCell(cell);

return cell.ToString();

}

catch

{

return cell.NumericCellValue.ToString();

}

}

}

private void ImportMatrixColorTrans(DataTable table)

{

int columnCount = table.Columns.Count;//确定表格的列数

List<string> propertyKeys = new List<string>();

foreach (DataRow rowData in table.Rows)

{

//根据模型名称获取对应的Model

List<SModel> mappedModels = GetModelsByInstanceName(rowData.ItemArray[1].ToString());//将每行的第2列ID值作为参数传递，获取到模型列表

string getMatrix = rowData.ItemArray[5].ToString();//获取当前行的矩阵坐标

string getColor = rowData.ItemArray[6].ToString();//获取当前行的颜色

string getTrans = rowData.ItemArray[7].ToString();//获取当前行的透明度

if (mappedModels.Count > 0)

{

foreach (var mappedModel in mappedModels)

{

//float getColor_r = 0;

//float getColor_g = 0;

//float getColor_b = 0;

//float getColor_a = 0;

//SplitString(getColor, getTrans, out getColor_r, out getColor_g, out getColor_b, out getColor_a);

//M3DSharp.Color color = new M3DSharp.Color();

//color.m_r = getColor_r;

//color.m_g = getColor_g;

//color.m_b = getColor_b;

//color.m_a = getColor_a;

//Matrix3x4 matrix3X4 = GetMatrix3X4(getMatrix);

//mappedModel.SetWorldTransform(matrix3X4);

//mappedModel.SetColor(color, true);

}

}

}

}