public void ReadExcelWithNPOI(DataGridView dataGridView, DataTable dataTable)
{
try
{
string ext;
//获取excel文件名
string excelFileName = this.OpenExcelFile(out ext);
if (System.IO.File.Exists(excelFileName))
{
IWorkbook workbook = null; //IWorkbook determina si es xls o xlsx
ISheet worksheet = null;
string first_sheet_name = "";
using (FileStream FS = new FileStream(excelFileName, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(FS); //Abre tanto XLS como XLSX
worksheet = workbook.GetSheetAt(0); //Obtener Hoja por indice
first_sheet_name = worksheet.SheetName; //Obtener el nombre de la Hoja
dataTable = new DataTable(first_sheet_name);
dataTable.Rows.Clear();
dataTable.Columns.Clear();
// Leer Fila por fila desde la primera
for (int rowIndex = 0; rowIndex <= worksheet.LastRowNum; rowIndex++)
{
DataRow NewReg = null;
IRow row = worksheet.GetRow(rowIndex);
IRow row2 = null;
IRow row3 = null;
if (rowIndex == 0)
{
row2 = worksheet.GetRow(rowIndex + 1); //Si es la Primera fila, obtengo tambien la segunda para saber el tipo de datos
row3 = worksheet.GetRow(rowIndex + 2); //Y la tercera tambien por las dudas
}
if (row != null) //null is when the row only contains empty cells
{
if (rowIndex > 0)
{
NewReg = dataTable.NewRow();
}
int colIndex = 0;
//Leer cada Columna de la fila
foreach (ICell cell in row.Cells)
{
object valorCell = null;
string cellType = "";
string[] cellType2 = new string[2];
if (rowIndex == 0) //Asumo que la primera fila contiene los titlos:
{
for (int i = 0; i < 2; i++)
{
ICell cell2 = null;
if (i == 0)
{
cell2 = row2.GetCell(cell.ColumnIndex);
}
else
{
cell2 = row3.GetCell(cell.ColumnIndex);
}
if (cell2 != null)
{
switch (cell2.CellType)
{
case CellType.Blank: break;
case CellType.Boolean: cellType2[i] = "System.Boolean"; break;
case CellType.String: cellType2[i] = "System.String"; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell2))
{
cellType2[i] = "System.DateTime";
}
else
{
cellType2[i] = "System.Double"; //valorCell = cell2.NumericCellValue;
}
break;
case CellType.Formula:
bool continuar = true;
switch (cell2.CachedFormulaResultType)
{
case CellType.Boolean: cellType2[i] = "System.Boolean"; break;
case CellType.String: cellType2[i] = "System.String"; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell2))
{
cellType2[i] = "System.DateTime";
}
else
{
try
{
//DETERMINAR SI ES BOOLEANO
if (cell2.CellFormula == "TRUE()")
{
cellType2[i] = "System.Boolean"; continuar = false;
}
if (continuar && cell2.CellFormula == "FALSE()")
{
cellType2[i] = "System.Boolean"; continuar = false;
}
if (continuar)
{
cellType2[i] = "System.Double"; continuar = false;
}
}
catch { }
}
break;
}
break;
default:
cellType2[i] = "System.String"; break;
}
}
}
//Resolver las diferencias de Tipos
if (cellType2[0] == cellType2[1])
{
cellType = cellType2[0];
}
else
{
if (cellType2[0] == null)
{
cellType = cellType2[1];
}
if (cellType2[1] == null)
{
cellType = cellType2[0];
}
if (cellType == "")
{
cellType = "System.String";
}
}
//Obtener el nombre de la Columna
string colName = "Column_{0}";
try { colName = cell.StringCellValue; }
catch { colName = string.Format(colName, colIndex); }
//Verificar que NO se repita el Nombre de la Columna
foreach (DataColumn col in dataTable.Columns)
{
if (col.ColumnName == colName)
{
colName = string.Format("{0}_{1}", colName, colIndex);
}
}
//Agregar el campos de la tabla:
DataColumn codigo = new DataColumn(colName, System.Type.GetType(cellType));
dataTable.Columns.Add(codigo); colIndex++;
}
else
{
//Las demas filas son registros:
switch (cell.CellType)
{
case CellType.Blank: valorCell = DBNull.Value; break;
case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
case CellType.String: valorCell = cell.StringCellValue; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
valorCell = cell.DateCellValue;
}
else
{
valorCell = cell.NumericCellValue;
}
break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Blank: valorCell = DBNull.Value; break;
case CellType.String: valorCell = cell.StringCellValue; break;
case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
valorCell = cell.DateCellValue;
}
else
{
valorCell = cell.NumericCellValue;
}
break;
}
break;
default: valorCell = cell.StringCellValue; break;
}
//Agregar el nuevo Registro
if (cell.ColumnIndex <= dataTable.Columns.Count - 1)
{
NewReg[cell.ColumnIndex] = valorCell;
}
}
}
}
if (rowIndex > 0)
{
dataTable.Rows.Add(NewReg);
}
}
dataTable.AcceptChanges();
dataGridView.DataSource = dataTable;
}
}
else
{
throw new Exception("ERROR 404: El archivo especificado NO existe.");
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fs);
//IWorkbook workbook = new HSSFWorkbook(fs);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
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
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 只读取不保存
/// </summary>
/// <param name="excelfile"></param>
/// <param name="IsHaveHead"></param>
/// <returns></returns>
public List <Sheetitem> Import(Microsoft.AspNetCore.Http.IFormFile excelfile, bool IsHaveHead = true)
{
//最终结果集
List <Sheetitem> result = new List <Sheetitem>();
try
{
//工厂读取文件流
IWorkbook readWorkbook = WorkbookFactory.Create(excelfile.OpenReadStream());
int CountSheet = readWorkbook.NumberOfSheets;
for (var i = 0; i < CountSheet; i++)
{
//获取sheet
ISheet worksheet = readWorkbook.GetSheetAt(i);
Sheetitem item = new Sheetitem();
//保存sheet名字
item.Name = worksheet.SheetName;
//获取行数长度(计算是从0开始的)
int rowCount = worksheet.LastRowNum + 1;
if (rowCount == 0)
{
continue;
}
int startRow = 0;
//需要读取的列数
List <int> NeedReadCol = new List <int>();
//如果有头部处理方式
if (IsHaveHead)
{
int col = -1;
foreach (ICell cell in worksheet.GetRow(0).Cells)
{
string CellValue = "";
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
CellValue = cell.DateCellValue.ToString();
}
else
{
CellValue = cell.ToString();
}
col++;
if (!string.IsNullOrEmpty(CellValue))
{
NeedReadCol.Add(col);
//item.Thread[0].Data.Add(new excle_option() { value = CellValue });
item.MyTitle.Add(new excle_option()
{
value = CellValue
});
}
}
startRow++;
}
//如果不存在头部怎么全部读出来
else
{
for (int NeedCell = 0; NeedCell < int.Parse(worksheet.GetRow(0).LastCellNum.ToString()); NeedCell++)
{
NeedReadCol.Add(NeedCell);
}
}
//开始遍历所有行（如果有头部怎么去掉头部的行）
for (var RowNumber = startRow; RowNumber < rowCount; RowNumber++)
{
//保存每行是数据
RowData Row = new RowData();
foreach (int CellNumber in NeedReadCol)
{
string CellValue = "";
ICell Cell = worksheet.GetRow(RowNumber).GetCell(CellNumber);
if (Cell == null)
{
CellValue = "";
}
else if (Cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(Cell))
{
CellValue = Cell.DateCellValue.ToString();
}
else
{
CellValue = Cell.ToString();
}
//每个但单元格的数据
excle_option DataCell = new excle_option();
DataCell.value = CellValue;
//将单元的数据加到行中
Row.Data.Add(DataCell);
}
if (Row.Data.FindAll(e => !string.IsNullOrEmpty(e.value)).Count > 0)
{
//遍历完行后，添加到sheet中
item.RowData.Add(Row);
}
}
//将sheet添加到结果集中
result.Add(item);
}
}
catch (Exception e)
{
throw e;
}
return(result);
}
public static DataSet ExcelToDataset(string file)
{
var workbook = WorkbookFactory.Create(file);
return(WorkbookToDataset(workbook));
}
/// <summary>
/// 替换成C#的数据类型
/// </summary>
/// <param name="filePath"></param>
/// <returns>文件是否有改变</returns>
public static bool ToCSharpSyntax(string filePath)
{
bool fileChange = false;
IWorkbook Workbook;
ISheet Worksheet;
using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) // no isolation
{
try
{
Workbook = WorkbookFactory.Create(file);
}
catch (Exception e)
{
throw new Exception(string.Format("无法打开Excel: {0}, 可能原因：正在打开？或是Office2007格式（尝试另存为）？ {1}", filePath,
e.Message));
//IsLoadSuccess = false;
}
}
Worksheet = Workbook.GetSheetAt(0);
List <ICell> cells = Worksheet.GetRow(4).Cells;
foreach (ICell cell in cells)
{
if (cell.StringCellValue == "num")
{
cell.SetCellValue("int");
fileChange = true;
}
if (cell.StringCellValue == "str")
{
cell.SetCellValue("string");
fileChange = true;
}
if (cell.StringCellValue.StartsWith("arr"))
{
cell.SetCellValue("string");
fileChange = true;
}
if (cell.StringCellValue.StartsWith("ssg"))
{
cell.SetCellValue("string");
fileChange = true;
}
}
//文件没有改变，不保存
if (!fileChange)
{
return(false);
}
using (var memStream = new MemoryStream())
{
Workbook.Write(memStream);
memStream.Flush();
memStream.Position = 0;
using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
var data = memStream.ToArray();
fileStream.Write(data, 0, data.Length);
fileStream.Flush();
}
}
return(true);
}
public void TestCreateWithPasswordFromStream()
{
IWorkbook wb;
// Unprotected, no password given, opens normally
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls), null
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx), null
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx, wb);
// Unprotected, wrong password, opens normally
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls), "wrong"
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx), "wrong"
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx, wb);
// Protected, correct password, opens fine
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls_prot[0]), xls_prot[1]
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls_prot[0], wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx_prot[0]), xlsx_prot[1]
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx_prot[0], wb);
// Protected, wrong password, throws Exception
try
{
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls_prot[0]), "wrong"
);
AssertCloseDoesNotModifyFile(xls_prot[0], wb);
Assert.Fail("Shouldn't be able to open with the wrong password");
}
catch (EncryptedDocumentException e) { }
try
{
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx_prot[0]), "wrong"
);
AssertCloseDoesNotModifyFile(xlsx_prot[0], wb);
Assert.Fail("Shouldn't be able to open with the wrong password");
}
catch (EncryptedDocumentException e) { }
}
private List <ExcelSheet> GetExcelsOnlyTop(ParseParam param)
{
List <ExcelSheet> sheets = new List <ExcelSheet>();
var allConfigs = Directory.GetFiles(param.ExcelDir, "*.*", SearchOption.TopDirectoryOnly).Where(s => !string.IsNullOrEmpty(Path.GetExtension(s)) && SUPPORTED_EXTENSIONS.Contains(Path.GetExtension(s).ToLower()) && !s.Contains("~"));
foreach (var configPath in allConfigs)
{
string workbookName = Path.GetFileNameWithoutExtension(configPath);
using (FileStream fs = new FileStream(configPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook workbook = WorkbookFactory.Create(fs);
for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
if (sheet.PhysicalNumberOfRows == 0)
{
continue;
}
for (int rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum;)
{
IRow row = sheet.GetRow(rowIndex++);
if (row == null)
{
continue;
}
ICell cell = row.GetCell(0);
//寻找以[Config]为标记的首行
if (cell == null ||
cell.CellType != CellType.String)
{
continue;
}
if (cell.GetStringCellValue().Trim() == "[ExcelLENT]")
{
ExcelSheet excelSheet = new ExcelSheet()
{
Workbook = workbook,
Sheet = sheet,
ClassName = row.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK).GetStringCellValue(),
m_primaryKeyRow = sheet.GetRow(rowIndex++),
CustomTypeRow = sheet.GetRow(rowIndex++),
FieldTypeRow = sheet.GetRow(rowIndex++),
FieldNameRow = sheet.GetRow(rowIndex++),
FieldDescriptionRow = sheet.GetRow(rowIndex++),
ContentBeginRowNum = rowIndex,
ContentEndRowNum = sheet.LastRowNum,
};
sheets.Add(excelSheet);
excelSheet.Close();
break;
}
}
}
}
}
return(sheets);
}
/// <summary>
/// 合并表格
/// </summary>
/// <param name="myExcel">表格数据model</param>
/// <param name="mySheet">传入和返回的工作薄</param>
/// <param name="dataSouceFileNames">源数据路径</param>
/// <param name="isAddFilename">是否加标题</param>
/// <returns></returns>
public ISheet MergeExcel(MyExcel myExcel, ISheet mySheet, string dataSouceFileNames, bool isAddFilename)
{
int count = myExcel.AddFileNames.Count;
int num2 = myExcel.AddFileNames.Count;
int rownum = myExcel.SouceStartRow - 1;
using (FileStream stream = File.OpenRead(dataSouceFileNames))
{
ISheet sheetAt = WorkbookFactory.Create(stream).GetSheetAt(0);
int lastRowNum = sheetAt.LastRowNum;
IRow row = sheetAt.GetRow(rownum);
IRow row2 = mySheet.CreateRow(myExcel.CurrentRow);
int lastCellNum = row.LastCellNum;
string str = sheetAt.GetRow(0).GetCell(1).ToString();
if (isAddFilename)
{
if (num2 > 0)
{
for (int k = 0; k < myExcel.AddFileNames.Count; k++)
{
row2.CreateCell(k).SetCellValue(myExcel.AddFileNames[k]);
}
}
for (int j = 0; j < lastCellNum; j++)
{
if (row.GetCell(j).StringCellValue == "运单编号")
{
row2.CreateCell(count + j).SetCellValue("运单号");
}
else if (row.GetCell(j).StringCellValue == "第一次出件时间")
{
row2.CreateCell(count + j).SetCellValue("入网日期");
}
else
{
this.CopyCell(row2.CreateCell(count + j), row.GetCell(j), row.GetCell(j).CellType);
}
}
myExcel.CurrentRow++;
}
rownum++;
for (int i = rownum; i <= lastRowNum; i++)
{
row = sheetAt.GetRow(rownum);
row2 = mySheet.CreateRow(myExcel.CurrentRow);
if (count > 0)
{
row2.CreateCell(0).SetCellValue(str);
row2.CreateCell(1).SetCellFormula(string.Format("Max(J{0}:N{0})", myExcel.CurrentRow + 1));
}
for (int j = 0; j < lastCellNum; j++)
{
ICell myCell = row2.CreateCell(count + j);
this.CopyCell(myCell, row.GetCell(j), row.GetCell(j).CellType);
}
rownum++;
myExcel.CurrentRow++;
}
}
return(mySheet);
}
public ExcelReader(Stream stream)
{
_book = WorkbookFactory.Create(stream);
_workContext = EngineContext.Current.Resolve <IWorkContext>();
}
public List <Cliente> ImportarClientesExcel(IFormFile file)
{
IWorkbook excel = WorkbookFactory.Create(file.OpenReadStream());
ISheet tabelaClientes = excel.GetSheet("Clientes");
if (tabelaClientes == null)
{
throw new Exception("A planilha Clientes não foi encontrada");
}
// Pega o index da primeira linha (Titulos) dessa tabela
int primeiraLinhaIndexTitulos = tabelaClientes.FirstRowNum;
// Pega o index da seguna linha (1° Registro) dessa tabela
int segundaLinhaIndex = (tabelaClientes.FirstRowNum + 1);
// Pega o index da ultima linha (Ultimo Registro) dessa tabela
int ultimaLinhaIndex = tabelaClientes.LastRowNum;
ICell TituloId = null;
ICell TituloNomeRazaoSocial = null;
ICell TituloCapacidadeFreezerEmPacotes = null;
ICell TituloEndereco = null;
List <Cliente> clientes = new List <Cliente>();
List <Endereco> enderecos = new List <Endereco>();
IRow linhasTitulos = tabelaClientes.GetRow(primeiraLinhaIndexTitulos);
foreach (ICell cell in linhasTitulos.Cells)
{
//TODO: Fazer uma busca de clientes pelo NomeRazaoSocial, verificando se este cliente já existe[...]
//[...] Caso existir, o if pula este processo do switch e soma segundaLinhaIndex +1
switch (cell.StringCellValue)
{
case "Id":
TituloId = cell;
break;
case "NomeRazaoSocial":
TituloNomeRazaoSocial = cell;
break;
case "CapacidadeFreezerEmPacotes":
TituloCapacidadeFreezerEmPacotes = cell;
break;
case "Endereco":
TituloEndereco = cell;
break;
default:
break;
}
}
long IdCliente = 0;
string NomeRazaoSocial = "";
int CapacidadeFreezerEmPacotes = 0;
string Endereco = "";
Cliente clienteAddDb = null;
for (int rowNum = segundaLinhaIndex; rowNum <= ultimaLinhaIndex; rowNum++)
{
//Recupera a linha atual
IRow linha = tabelaClientes.GetRow(rowNum);
foreach (ICell cell in linha.Cells)
{
//Propriedade ID não usada
//if (cell.ColumnIndex == TituloId.ColumnIndex)
// IdCliente = Convert.ToInt32(cell.NumericCellValue);
if (cell.ColumnIndex == TituloNomeRazaoSocial.ColumnIndex)
{
NomeRazaoSocial = cell.StringCellValue;
}
if (cell.ColumnIndex == TituloCapacidadeFreezerEmPacotes.ColumnIndex)
{
CapacidadeFreezerEmPacotes = Convert.ToInt32(cell.NumericCellValue);
}
if (cell.ColumnIndex == TituloEndereco.ColumnIndex)
{
Endereco = cell.StringCellValue;
}
}
// Tentando instanciar um cliente
Cliente cliente = null;
try
{
cliente = new Cliente(NomeRazaoSocial, Convert.ToUInt32(CapacidadeFreezerEmPacotes));
}
catch (Exception)
{
throw new Exception("Houve um erro ao instanciar classe Cliente");
}
if (cliente != null)
{
clientes.Add(cliente);
}
// Adicionando cliente na DB
clienteAddDb = Create(cliente);
Endereco endereco = null;
try
{
endereco = new Endereco(Endereco, clienteAddDb.Id);
}
catch (Exception)
{
throw new Exception("Houve um erro ao instanciar classe Endereco");
}
if (endereco != null)
{
enderecos.Add(endereco);
}
}
//Adicionando Enderecos vinculados à clientes na DB
_context.Enderecos.AddRange(enderecos);
//TODO Contatos, NF, Boletos e Preço por Produto (FAZER TODO O PROCEDIMENTO IGUAL A TabelaClientes)
_context.SaveChanges();
// Nota Fiscal Condição
ISheet tabelaNF = excel.GetSheet("NF");
if (tabelaNF == null)
{
throw new Exception("A planilha Nota Fiscal(NF) não foi encontrada");
}
// Pega o index da primeira linha (Titulos) dessa tabela
primeiraLinhaIndexTitulos = tabelaNF.FirstRowNum;
// Pega o index da seguna linha (1° Registro) dessa tabela
segundaLinhaIndex = (tabelaNF.FirstRowNum + 1);
// Pega o index da ultima linha (Ultimo Registro) dessa tabela
ultimaLinhaIndex = tabelaNF.LastRowNum;
ICell TituloIdNF = null;
ICell TituloDescricaoNF = null;
ICell TituloEmitirNF = null;
ICell TituloClienteId = null;
List <NotaFiscalCondicao> notasFiscais = new List <NotaFiscalCondicao>();
linhasTitulos = tabelaNF.GetRow(primeiraLinhaIndexTitulos);
foreach (ICell cell in linhasTitulos.Cells)
{
switch (cell.StringCellValue)
{
case "Id":
TituloIdNF = cell;
break;
case "Descricao":
TituloDescricaoNF = cell;
break;
case "EmitirNF":
TituloEmitirNF = cell;
break;
case "ClienteId":
TituloClienteId = cell;
break;
default:
break;
}
}
string descricaoNF = "";
bool emitirNF = true;
long clienteIdNF = 0;
for (int rowNum = segundaLinhaIndex; rowNum <= ultimaLinhaIndex; rowNum++)
{
//Recupera a linha atual
IRow linha = tabelaNF.GetRow(rowNum);
foreach (ICell cell in linha.Cells)
{
if (cell.ColumnIndex == TituloDescricaoNF.ColumnIndex)
{
descricaoNF = cell.StringCellValue;
}
if (cell.ColumnIndex == TituloEmitirNF.ColumnIndex)
{
if (cell.StringCellValue.ToUpper() == "SIM")
{
emitirNF = true;
}
if (cell.StringCellValue.ToUpper() == "NAO" || cell.StringCellValue.ToUpper() == "NÃO")
{
emitirNF = false;
}
}
if (cell.ColumnIndex == TituloClienteId.ColumnIndex)
{
clienteIdNF = Convert.ToInt32(cell.NumericCellValue);
}
}
NotaFiscalCondicao nfc = null;
if (!string.IsNullOrEmpty(descricaoNF))
{
nfc = new NotaFiscalCondicao(descricaoNF, emitirNF, clienteIdNF);
}
else
{
nfc = new NotaFiscalCondicao(emitirNF, clienteIdNF);
}
notasFiscais.Add(nfc);
}
// Adiciona todas as notasFiscais no banco
_context.NotasFiscaisCondicoes.AddRange(notasFiscais);
// Boletos
ISheet tabelaBoletos = excel.GetSheet("Boletos");
if (tabelaBoletos == null)
{
throw new Exception("A planilha Boletos não foi encontrada");
}
// Pega o index da primeira linha (Titulos) dessa tabela
primeiraLinhaIndexTitulos = tabelaBoletos.FirstRowNum;
// Pega o index da seguna linha (1° Registro) dessa tabela
segundaLinhaIndex = (tabelaBoletos.FirstRowNum + 1);
// Pega o index da ultima linha (Ultimo Registro) dessa tabela
ultimaLinhaIndex = tabelaBoletos.LastRowNum;
ICell TituloIdBO = null;
ICell TituloDescricaoBO = null;
TituloClienteId = null;
List <BoletoCondicao> boletos = new List <BoletoCondicao>();
linhasTitulos = tabelaBoletos.GetRow(primeiraLinhaIndexTitulos);
foreach (ICell cell in linhasTitulos.Cells)
{
switch (cell.StringCellValue)
{
case "Id":
TituloIdBO = cell;
break;
case "Descricao":
TituloDescricaoBO = cell;
break;
case "ClienteId":
TituloClienteId = cell;
break;
default:
break;
}
}
string descricaoBO = "";
long clienteIdBO = 0;
for (int rowNum = segundaLinhaIndex; rowNum <= ultimaLinhaIndex; rowNum++)
{
//Recupera a linha atual
IRow linha = tabelaBoletos.GetRow(rowNum);
foreach (ICell cell in linha.Cells)
{
if (cell.ColumnIndex == TituloDescricaoBO.ColumnIndex)
{
descricaoBO = cell.StringCellValue;
}
if (cell.ColumnIndex == TituloClienteId.ColumnIndex)
{
clienteIdBO = Convert.ToInt32(cell.NumericCellValue);
}
}
BoletoCondicao bc = null;
try
{
bc = new BoletoCondicao(descricaoBO, clienteIdBO);
}
catch (Exception)
{
throw new Exception("Houve um erro ao instanciar a classe BoletoCondicao");
}
boletos.Add(bc);
}
// Adiciona todos os BoletosCondicoes no banco
_context.BoletosCondicoes.AddRange(boletos);
// Contatos
ISheet tabelaContatos = excel.GetSheet("Contatos");
if (tabelaContatos == null)
{
throw new Exception("A planilha Contatos não foi encontrada");
}
// Pega o index da primeira linha (Titulos) dessa tabela
primeiraLinhaIndexTitulos = tabelaContatos.FirstRowNum;
// Pega o index da seguna linha (1° Registro) dessa tabela
segundaLinhaIndex = (tabelaContatos.FirstRowNum + 1);
// Pega o index da ultima linha (Ultimo Registro) dessa tabela
ultimaLinhaIndex = tabelaContatos.LastRowNum;
ICell TituloIdCO = null;
ICell TituloTelefoneCO = null;
ICell TituloNomesDonosCO = null;
TituloClienteId = null;
List <Contato> contatos = new List <Contato>();
linhasTitulos = tabelaContatos.GetRow(primeiraLinhaIndexTitulos);
foreach (ICell cell in linhasTitulos.Cells)
{
switch (cell.StringCellValue)
{
case "Id":
TituloIdCO = cell;
break;
case "Telefone":
TituloTelefoneCO = cell;
break;
case "NomesDonos":
TituloNomesDonosCO = cell;
break;
case "ClienteId":
TituloClienteId = cell;
break;
default:
break;
}
}
string telefoneCO = "";
string nomesDonosCO = "";
long clienteIdCO = 0;
for (int rowNum = segundaLinhaIndex; rowNum <= ultimaLinhaIndex; rowNum++)
{
//Recupera a linha atual
IRow linha = tabelaContatos.GetRow(rowNum);
foreach (ICell cell in linha.Cells)
{
if (cell.ColumnIndex == TituloTelefoneCO.ColumnIndex)
{
telefoneCO = cell.NumericCellValue.ToString();
}
if (cell.ColumnIndex == TituloNomesDonosCO.ColumnIndex)
{
if (!string.IsNullOrEmpty(cell.StringCellValue))
{
nomesDonosCO = cell.StringCellValue;
}
}
if (cell.ColumnIndex == TituloClienteId.ColumnIndex)
{
clienteIdCO = Convert.ToInt32(cell.NumericCellValue);
}
}
Contato con = null;
try
{
con = new Contato(telefoneCO, nomesDonosCO, clienteIdCO);
}
catch (Exception)
{
throw new Exception("Houve um erro ao instanciar a classe Contato");
}
contatos.Add(con);
}
// Adiciona todos os contatos no banco
_context.Contatos.AddRange(contatos);
return(clientes);
}
/// <summary>
///從datatable中導出到excel
/// </summary>
/// <param name="strFileName"> excel文件名</param>
/// <param name="dtSource"> datatabe源數據</param>
/// <param name="strHeaderText">表名</param>
/// <param name="sheetnum"> sheet的編號</param>
/// <returns></returns>
static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary <string, string> dir, int sheetnum)
{
//創建工作簿和sheet
IWorkbook workbook = new HSSFWorkbook();
using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
{
if (writefile.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(writefile);
}
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(" yyyy-mm-dd ");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 0)
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
#region 表頭及樣式
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
rowIndex = 1;
}
#endregion
#region 列頭及樣式
if (rowIndex == 1)
{
IRow headerRow = sheet.CreateRow(1); //第二行設置列名
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//寫入列標題
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
rowIndex = 2;
}
#endregion
}
#endregion
#region 填充內容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case " System.String ": //字符串類型
double result;
if (isNumeric(drValue, out result))
{
//數字字符串
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case " System.DateTime ": //日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化顯示
break;
case " System.Boolean ": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case " System.Int16 ": //整型
case " System.Int32 ":
case " System.Int64 ":
case " System.Byte ":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case " System.Decimal ": //浮點型
case " System.Double ":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case " System.DBNull ": //空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 从datatable 中导出到excel
/// </summary>
/// <param name="dtSource">datatable数据源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="fs">文件流</param>
/// <param name="readfs">内存流</param>
/// <param name="sheetnum">sheet索引</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary <string, string> dir, int sheetnum)
{
IWorkbook workbook = new XSSFWorkbook();
if (readfs.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(readfs);
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <param name="dir">excel列名和DataTable列名的对应字典</param>
/// <returns></returns>
static DataTable ImportDt(string strFileName, int HeaderRowIndex, Dictionary <string, string> dir)
{
FileStream fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read);
IWorkbook Workbook = WorkbookFactory.Create(fs);
ISheet sheet = Workbook.GetSheetAt(0);
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
//没有标头或者不需要表头用excel列的序号（1,2,3..）作为DataTable的列名
if (HeaderRowIndex < 0)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//有表头，使用表头做为DataTable的列名
else
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
//如果excel某一列列名不存在：以该列的序号作为Datatable的列名，如果DataTable中包含了这个序列为名的列，那么列名为重复列名+序号
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//excel中的某一列列名不为空，但是重复了：对应的Datatable列名为“重复列名+序号”
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
//正常情况，列名存在且不重复：用excel中的列名作为datatable中对应的列名
{
string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;
DataColumn column = new DataColumn(colName);
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍历
{
try
{
IRow row;
if (sheet.GetRow(i) == null)//如果excel有空行，则添加缺失的行
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍历
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String: //字符串
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = default(string);
}
break;
case CellType.Numeric: //数字
if (DateUtil.IsCellDateFormatted(row.GetCell(j))) //时间戳数字
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula: //公式
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
Console.Write(exception.Message);
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
Console.Write(exception.Message);
}
}
}
catch (Exception exception)
{
Console.Write(exception.Message);
}
return(table);
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable excelToDataTable(bool isFirstRowColumn, string sheetName = null)
{
ISheet sheet = null;
DataTable dt_sheet = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
workBook = WorkbookFactory.Create(fs);
/*
* if (fileName.IndexOf(".xlsx") > 0)
* {
* workBook = new XSSFWorkbook();
* }
* else if (fileName.IndexOf(".xls") > 0)
* {
* workBook = new HSSFWorkbook(fs);
* }
*/
if (sheetName != null)
{
sheet = workBook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workBook.GetSheetAt(0);
}
}
else
{
sheet = workBook.GetSheetAt(0);
}
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);
dt_sheet.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 = dt_sheet.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt_sheet.Rows.Add(dataRow);
}
}
fs.Close();
return(dt_sheet);
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
AppInfo.WriteLogs(ex.Message);
return(null);
}
}
public async Task <List <HosoCourier> > ReadXlsxFile(MemoryStream stream, int createBy)
{
var importExelFrameWork = await _rpTailieu.GetImportTypes((int)HosoType.HosoCourrier);
BusinessExtentions.GetObjectParams(importExelFrameWork);
//return null;
var result = new TupleModel();
var workBook = WorkbookFactory.Create(stream);
var sheet = workBook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
var hasData = rows.MoveNext();
var param = new DynamicParameters();
var pars = new List <DynamicParameters>();
int count = 0;
var hosos = new List <HosoCourier>();
for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
{
try
{
var row = sheet.GetRow(i);
if (row != null)
{
if (row.Cells.Count > 1)
{
bool isNullRow = row.Cells.Count < 3 ? true : false;
}
foreach (var col in importExelFrameWork)
{
param.Add(col.Name, BusinessExtentions.TryGetValueFromCell(row.Cells[col.Position].ToString(), col.ValueType));
}
pars.Add(param);
var hoso = new HosoCourier()
{
CustomerName = row.Cells[0] != null ? row.Cells[0].ToString() : "",
Phone = row.Cells[1] != null ? row.Cells[1].ToString() : "",
Cmnd = row.Cells[2] != null ? row.Cells[2].ToString() : "",
LastNote = row.Cells[4] != null ? row.Cells[4].ToString() : "",
ProvinceId = row.Cells[5] != null?Convert.ToInt32(row.Cells[5].ToString()) : 0,
DistrictId = row.Cells[6] != null?Convert.ToInt32(row.Cells[6].ToString()) : 0,
SaleCode = row.Cells[7] != null ? row.Cells[7].ToString().Trim().ToLower() :string.Empty,
Status = (int)HosoCourierStatus.New,
CreatedBy = createBy
};
var strAssignee = row.Cells[3] != null ? row.Cells[3].ToString() : "";
var assigneeIdsStr = string.IsNullOrWhiteSpace(strAssignee) ? new List <string>() : strAssignee.Split(',').ToList();
var assigneeIds = (assigneeIdsStr != null && assigneeIdsStr.Any()) ? assigneeIdsStr.Select(s => Convert.ToInt32(s)).ToList() : new List <int>();
hoso.AssigneeIds = assigneeIds;
hoso.AssignId = assigneeIds.FirstOrDefault();
hoso.GroupId = await _rpCourierProfile.GetGroupIdByNguoiQuanLyId(hoso.AssignId);
hosos.Add(hoso);
count++;
}
}
catch
{
return(hosos);
}
}
return(hosos);
}
public ExcelReader(string excelUrl)
{
_book = WorkbookFactory.Create(excelUrl);
_workContext = EngineContext.Current.Resolve <IWorkContext>();
}
/// <summary>
/// API lấy dữ liệu địa chỉ tất cả các xã từ file excel mã mới
/// </summary>
/// <returns>List địa chỉ với mã mới</returns>
/// Created by bvbao (21/7/2020)
/// Modified by bvbao (24/7/2020)
private List <Province> ReadNewLocationExcelFile(IFormFile file)
{
List <Province> list = new List <Province>();
string filePath = UploadFile(file);
// upload file and get filepath
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fs);
ISheet sheet = workbook.GetSheetAt(0);
// đọc sheet này bắt đầu từ row 1 (0 bỏ vì tiêu đề)
int rowIndex = 1;
// nếu vẫn chưa gặp end thì vẫn lấy data
while (sheet.LastRowNum >= rowIndex)
{
// lấy row hiện tại
var nowRow = sheet.GetRow(rowIndex);
var wardID = "VN" + NumericOrString(nowRow.GetCell(6)) + NumericOrString(nowRow.GetCell(4)) + NumericOrString(nowRow.GetCell(0));
var districtID = "VN" + NumericOrString(nowRow.GetCell(6)) + NumericOrString(nowRow.GetCell(4));
var provinceID = "VN" + NumericOrString(nowRow.GetCell(6));
var wardName = nowRow.GetCell(1).StringCellValue;
var districtName = nowRow.GetCell(5).StringCellValue;
var provinceName = nowRow.GetCell(7).StringCellValue;
if (list.Count > 0)
{
bool checkExist = false;
foreach (Province province in list)
{
if (province.ProvinceName == provinceName)
{
if (province.districts.Count > 0)
{
foreach (District district in province.districts)
{
if (district.DistrictName == districtName)
{
checkExist = true;
district.wards.Add(DefineNewWard(wardID, wardName));
break;
}
}
if (!checkExist)
{
if (wardName != "")
{
checkExist = true;
District newDistrict = DefineNewDistrict(districtID, districtName);
province.districts.Add(newDistrict);
newDistrict.wards.Add(DefineNewWard(wardID, wardName));
}
else
{
checkExist = true;
province.districts.Add(DefineNewDistrict(districtID, districtName));
}
}
}
else
{
checkExist = true;
District newDistrict = DefineNewDistrict(districtID, districtName);
province.districts.Add(newDistrict);
newDistrict.wards.Add(DefineNewWard(wardID, wardName));
}
break;
}
}
if (!checkExist)
{
checkExist = true;
Province newProvince = DefineNewProvince(provinceID, provinceName);
list.Add(newProvince);
District newDistrict = DefineNewDistrict(districtID, districtName);
newProvince.districts.Add(newDistrict);
newDistrict.wards.Add(DefineNewWard(wardID, wardName));
}
}
else
{
Province newProvince = DefineNewProvince(provinceID, provinceName);
list.Add(newProvince);
District newDistrict = DefineNewDistrict(districtID, districtName);
newProvince.districts.Add(newDistrict);
newDistrict.wards.Add(DefineNewWard(wardID, wardName));
}
rowIndex++;
}
}
System.IO.File.Delete(filePath);
return(list);
}
public object GetWorkbook(string path)
{
return(WorkbookFactory.Create(path));
}
public void TestCreateWithPasswordFromFile()
{
IWorkbook wb;
// Unprotected, no password given, opens normally
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xls).FullName, null
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xlsx).FullName, null
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx, wb);
// Unprotected, wrong password, opens normally
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xls).FullName, "wrong"
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xlsx).FullName, "wrong"
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx, wb);
// Protected, correct password, opens fine
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xls_prot[0]).FullName, xls_prot[1]
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xlsx_prot[0]).FullName, xlsx_prot[1]
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
Assert.IsTrue(wb.NumberOfSheets > 0);
Assert.IsNotNull(wb.GetSheetAt(0));
Assert.IsNotNull(wb.GetSheetAt(0).GetRow(0));
AssertCloseDoesNotModifyFile(xlsx, wb);
// Protected, wrong password, throws Exception
try
{
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xls_prot[0]).FullName, "wrong"
);
AssertCloseDoesNotModifyFile(xls_prot[0], wb);
Assert.Fail("Shouldn't be able to open with the wrong password");
}
catch (EncryptedDocumentException)
{
// expected here
}
try
{
wb = WorkbookFactory.Create(
HSSFTestDataSamples.GetSampleFile(xlsx_prot[0]).FullName, "wrong"
);
AssertCloseDoesNotModifyFile(xlsx_prot[0], wb);
Assert.Fail("Shouldn't be able to open with the wrong password");
}
catch (EncryptedDocumentException)
{
// expected here
}
}
public object GetWorkbook(Stream stream)
{
return(WorkbookFactory.Create(stream));
}
private void LoadStream(Stream stream)
{
workbook = WorkbookFactory.Create(stream);
stream.Close();
sheetNames = getSheetNames();
}
public Workbook ReadWorkbook(Stream stream)
{
return(CreateWorkbook(WorkbookFactory.Create(stream)));
}
public static DataSet ExcelToDataset(Stream stream)
{
var workbook = WorkbookFactory.Create(stream);
return(WorkbookToDataset(workbook));
}
private void ImportBaseClientInfo(int templateid, string path)
{
IPT_UploadTemplateBLL _bll = new IPT_UploadTemplateBLL(templateid);
if (_bll.Model.State != 1)
{
return; //模版作废
}
string message = "";
int State = 0;//五个Sheet导入结果的合计
#region 导入Excel文件
if (!File.Exists(path))
{
message += "Excel在当前路径中不存在！\r\n";
State = 20;
}
else
{
try
{
FileStream fs = new FileStream(path, FileMode.Open);
IWorkbook _ibook = WorkbookFactory.Create(fs);
if (_ibook.GetSheet("商品资料") == null || _ibook.GetSheet("客户资料") == null || _ibook.GetSheet("供货单位信息") == null || _ibook.GetSheet("期初库存") == null)
{
message += "Excel表格缺少Sheet表单\r\n";
State = 20;
}
else
{
ClientInit _ClientInit = new ClientInit();
int _State = 0;
message += _ClientInit.DoImportProduct(templateid, _bll.Model.ClientID, _ibook.GetSheet("商品资料"), out _State);
message += "\r\n";
State += _State;
message += _ClientInit.DoImportClient(templateid, _bll.Model.ClientID, _ibook.GetSheet("客户资料"), out _State);
message += "\r\n";
State += _State;
message += _ClientInit.DoImportSupplier(templateid, _bll.Model.ClientID, _ibook.GetSheet("供货单位信息"), out _State);
message += "\r\n";
State += _State;
message += _ClientInit.DoImportInventory(templateid, _bll.Model.ClientID, _ibook.GetSheet("期初库存"), out _State);
message += "\r\n";
State += _State;
fs.Close();
fs.Dispose();
}
}
catch (System.Exception err)
{
string error = "Message:" + err.Message + "<br/>" + "Source:" + err.Source + "<br/>" +
"StackTrace:" + err.StackTrace + "<br/>";
message += "系统错误-4!" + err.Message;
_bll.Model.Remark = message;
}
}
#endregion
if (State == 15)
{
_bll.Model.State = 3;
}
else if (State == 20)
{
_bll.Model.State = 4;
}
else
{
_bll.Model.State = 5;
}
_bll.Update();
this.SendMessage("UploadExcel.导入提示!", message);
}
/// <summary>
/// 将文件流读取到DataTable数据表中
/// </summary>
/// <param name="fileStream">文件流</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
//根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
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 || row.FirstCellNum < 0)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cell))
{
dataRow[j] = row.GetCell(j).DateCellValue;
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();
}
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult Export(HttpPostedFileBase fileData, string TypeId)
{
if (fileData != null)
{
try
{
string filePath = Server.MapPath("~/Uploads/");
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
string fileName = Path.GetFileName(fileData.FileName); // 原始文件名称
string fileExtension = Path.GetExtension(fileName); // 文件扩展名
string saveName = CommonHelper.CalcMD5(fileData.InputStream) + CommonHelper.CreateVerifyCode(5) + fileExtension; // 保存文件名称
fileData.SaveAs(filePath + saveName);
IWorkbook wb1 = WorkbookFactory.Create(filePath + saveName);
ISheet sheel = wb1.GetSheetAt(0);
ExamDTO dto = new ExamDTO();
for (int i = 1; i <= sheel.LastRowNum; i++)
{
IRow row = sheel.GetRow(i);
ICell StuName = row.GetCell(0);
ICell SysName = row.GetCell(1);
ICell MajorName = row.GetCell(2);
ICell StuId = row.GetCell(3);
ICell SFZCode = row.GetCell(4);
ICell ClassName = row.GetCell(5);
ICell ZKZCode = row.GetCell(6);
ICell SchoolName = row.GetCell(7);
ICell PlaceNum = row.GetCell(8);
ICell Address = row.GetCell(9);
ICell ExamTime = row.GetCell(10);
ICell LLllzwh = row.GetCell(11);
ICell LLExamAddress = row.GetCell(12);
ICell LLExamTime = row.GetCell(13);
ICell LLExamPlaceNum = row.GetCell(14);
dto.StuName = StuName.ToString();
dto.SysName = SysName.ToString();
dto.MajorName = MajorName.ToString();
dto.StuId = StuId.ToString();
dto.SFZCode = SFZCode.ToString();
dto.ClassName = ClassName.ToString();
dto.ZKZCode = ZKZCode.ToString();
dto.SchoolName = SchoolName.ToString();
dto.PlaceNum = PlaceNum.ToString();
dto.Address = Address.ToString();
dto.ExamTime = ExamTime.ToString();
dto.LLExamAddress = LLExamAddress.ToString();
dto.LLExamTime = LLExamTime.ToString();
dto.LLExamPlaceNum = LLExamPlaceNum.ToString();
dto.TypeId = Convert.ToInt64(TypeId);
dto.llzwh = LLllzwh.ToString();
examService.Add(dto);
}
return(Content("<script>alert('导入成功!');parent.location.reload()</script>"));
}
catch (Exception ex)
{
return(Content("<script>alert('导入失败!" + ex.Message + "');parent.location.reload()</script>"));
//return Json(new AjaxResult { Status = "error", ErrorMsg = ex.Message });
}
}
else
{
return(Content("<script>alert('请选择要上传的文件！');parent.location.reload()</script>"));
}
}
public void SaveToSpreadsheet(
string file,
IList <CombatLog> combatLogs)
{
lock (SpreadsheetLocker)
{
var master = Path.Combine(
DirectoryHelper.FindSubDirectory("resources"),
"CombatLogBase.xlsx");
var work = Path.Combine(
DirectoryHelper.FindSubDirectory("resources"),
"CombatLogBase_work.xlsx");
if (!File.Exists(master))
{
throw new FileNotFoundException(
$"CombatLog Master File Not Found. {master}");
}
File.Copy(master, work, true);
var book = WorkbookFactory.Create(work);
var sheet = book.GetSheet("CombatLog");
try
{
// セルの書式を生成する
var noStyle = book.CreateCellStyle();
var timestampStyle = book.CreateCellStyle();
var timeStyle = book.CreateCellStyle();
var textStyle = book.CreateCellStyle();
var perStyle = book.CreateCellStyle();
noStyle.DataFormat = book.CreateDataFormat().GetFormat("#,##0_ ");
timestampStyle.DataFormat = book.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss.000");
timeStyle.DataFormat = book.CreateDataFormat().GetFormat("mm:ss");
textStyle.DataFormat = book.CreateDataFormat().GetFormat("@");
perStyle.DataFormat = book.CreateDataFormat().GetFormat("0.0%");
var now = DateTime.Now;
var row = 1;
foreach (var data in combatLogs)
{
var timeAsDateTime = new DateTime(
now.Year,
now.Month,
now.Day,
0,
data.TimeStampElapted.Minutes >= 0 ? data.TimeStampElapted.Minutes : 0,
data.TimeStampElapted.Seconds >= 0 ? data.TimeStampElapted.Seconds : 0);
var col = 0;
writeCell <long>(sheet, row, col++, data.No, noStyle);
writeCell <DateTime>(sheet, row, col++, timeAsDateTime, timeStyle);
writeCell <double>(sheet, row, col++, data.TimeStampElapted.TotalSeconds, noStyle);
writeCell <string>(sheet, row, col++, data.LogTypeName, textStyle);
writeCell <string>(sheet, row, col++, data.Actor, textStyle);
writeCell <decimal>(sheet, row, col++, data.HPRate, perStyle);
writeCell <string>(sheet, row, col++, data.Activity, textStyle);
writeCell <string>(sheet, row, col++, data.RawWithoutTimestamp, textStyle);
writeCell <string>(sheet, row, col++, data.Text, textStyle);
writeCell <string>(sheet, row, col++, data.SyncKeyword, textStyle);
writeCell <DateTime>(sheet, row, col++, data.TimeStamp, timestampStyle);
writeCell <string>(sheet, row, col++, data.Zone, textStyle);
row++;
}
if (File.Exists(file))
{
File.Delete(file);
}
FileHelper.CreateDirectory(file);
using (var fs = new FileStream(file, FileMode.OpenOrCreate, FileAccess.Write))
{
book.Write(fs);
}
}
finally
{
book?.Close();
File.Delete(work);
}
}
// セルの編集内部メソッド
void writeCell <T>(ISheet sh, int r, int c, T v, ICellStyle style)
{
var rowObj = sh.GetRow(r) ?? sh.CreateRow(r);
var cellObj = rowObj.GetCell(c) ?? rowObj.CreateCell(c);
switch (Type.GetTypeCode(typeof(T)))
{
case TypeCode.SByte:
case TypeCode.Byte:
case TypeCode.Int16:
case TypeCode.UInt16:
case TypeCode.Int32:
case TypeCode.UInt32:
case TypeCode.Int64:
case TypeCode.UInt64:
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
cellObj.SetCellValue(Convert.ToDouble(v));
cellObj.CellStyle = style;
break;
case TypeCode.DateTime:
cellObj.SetCellValue(Convert.ToDateTime(v));
cellObj.CellStyle = style;
break;
case TypeCode.String:
cellObj.SetCellValue(Convert.ToString(v));
cellObj.CellStyle = style;
break;
}
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileWholePath">待处理的EXCEL文件全路径</param>
/// <param name="sheetName">EXCEL工作薄中工作表的名称</param>
/// <param name="isFirstRowHeaderLine">工作表第一行是否是DataTable的列名</param>
/// <param name="trim">是否去除获取的单元格内容前后的空格</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTableNpoi(string fileWholePath, string sheetName = null, bool isFirstRowHeaderLine = true, bool trim = true)
{
ISheet sheet = null;
DataTable dataTable = new DataTable();
try
{
IWorkbook workbook = null;
if (!File.Exists(fileWholePath))
{
Console.WriteLine("ExcelToDataTableNpoi 指定的EXCEL文件不存在。");
return(null);
}
var fs = new FileStream(fileWholePath, FileMode.Open, FileAccess.Read);
workbook = WorkbookFactory.Create(fs); //旧的方法需要判断后缀名，.xlsx 则 new XSSFWorkbook(fs)；.xls则new HSSFWorkbook(fs)
if (workbook == null)
{
Console.WriteLine("ExcelToDataTableNpoi 读取错误,请确认文件类型");
return(null);
}
//如果有指定工作表名称，则按名字读取工作表
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
}
//如果没有找到指定的sheetName对应的sheet，或者根据名字读取失败，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
{
Console.WriteLine("ExcelToDataTableNpoi 读取错误,读不出工作表。");
return(null);
}
IRow firstRow = sheet.GetRow(0);
int startRow = sheet.FirstRowNum;
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowHeaderLine)
{//第一行是标题行，读取标题行，设置DataTable的各列名字
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);
dataTable.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
string tmp = row.GetCell(j).ToString();
if (trim)
{
tmp = tmp.Trim();
}
dataRow[j] = tmp;
}
}
dataTable.Rows.Add(dataRow);
}
return(dataTable);
}
catch (Exception ex)
{
Console.WriteLine("ExcelToDataTableNpoi出现异常: " + ex.Message);
return(null);
}
}
/// <summary>
/// Read data from an Excel file.
/// </summary>
/// <typeparam name="T">The type of data to read.</typeparam>
/// <param name="xlsFilePath">The file path of the Excel file</param>
/// <param name="sheetName">The name of the sheet to read</param>
/// <param name="colCount">The number of columns to read</param>
/// <param name="errorMessage">The error message that indicate the reason when failed to read the data.</param>
/// <returns>The data list</returns>
public static List <T> ReadDataFromExcelFile <T>(string xlsFilePath,
string sheetName, int colCount, out string errorMessage) where T : CustomData
{
errorMessage = string.Empty;
List <T> list = new List <T>();
try
{
//XSSFWorkbook wb;
//HSSFWorkbook wb;
//using (FileStream file = new FileStream(xlsFilePath, FileMode.Open, FileAccess.Read))
//{
// wb = new XSSFWorkbook(file);
// wb = new HSSFWorkbook(file);
//}
IWorkbook wb = WorkbookFactory.Create(xlsFilePath);
ISheet sheet = wb.GetSheet(sheetName);
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
string[] data = new string[colCount];
bool allEmpty = true;
for (int i = 0; i < colCount; i++)
{
data[i] = sheet.GetRow(row).GetCell(i).StringCellValue;
allEmpty &= string.IsNullOrWhiteSpace(data[i]);
}
if (allEmpty)
{
break;
}
string errMsg;
T t = (T)Activator.CreateInstance(typeof(T));
if (t.SetData(out errMsg, data))
{
list.Add(t);
}
else
{
errorMessage = errMsg;
break;
}
}
}
if (string.IsNullOrEmpty(errorMessage))
{
return(list);
}
else
{
return(null);
}
}
catch (Exception e)
{
errorMessage = e.Message;
return(null);
}
}
public static List <Dictionary <string, object> > ImportExcel(Stream stream, int titleRownum = 0)
{
List <Dictionary <string, object> > list = new List <Dictionary <string, object> >();
IWorkbook workbook = null;
ISheet sheet = null;
try
{
workbook = WorkbookFactory.Create(stream);
sheet = workbook.GetSheetAt(0);
}
catch (System.Exception)
{
return(null);
}
if (titleRownum < 0)
{
return(null);
}
try
{
int sheetCount = sheet.LastRowNum;
int sheetMergeCount = sheet.NumMergedRegions;
IRow row = null;
Dictionary <string, int> titleMap = new Dictionary <string, int>();
for (int rowIndex = titleRownum + 1; rowIndex <= sheetCount; rowIndex++)
{
row = sheet.GetRow(rowIndex);
if (null == row || row.RowNum < titleRownum - 1)
{
continue;
}
#region 解析标题行
if (row.RowNum == titleRownum - 1)
{
List <ICell> cellList = row.Cells;
int index = 0;
object cellValue = null;
foreach (ICell cellItem in cellList)
{
if (sheetMergeCount > 0)
{
for (int i = 0; i < sheetMergeCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
int firstColumn = range.FirstColumn;
int lastColumn = range.LastColumn;
int firstRow = range.FirstRow;
int lastRow = range.LastRow;
if (cellItem.RowIndex == firstRow &&
cellItem.RowIndex == lastRow &&
cellItem.ColumnIndex >= firstColumn &&
cellItem.ColumnIndex >= lastColumn)
{
for (int j = firstColumn; j <= lastColumn; j++)
{
index = j;
cellValue = GetCellValue(sheet.GetRow(firstRow + 1).GetCell(j));
titleMap.Add(null == cellValue ? "" : cellValue.ToString(), index);
}
}
}
}
else
{
cellValue = GetCellValue(cellItem);
titleMap.Add(null == cellValue ? "" : cellValue.ToString(), index);
}
index++;
}
}
#endregion
if (sheetMergeCount > 0 &&
row.RowNum == titleRownum - 1)
{
continue;
}
Dictionary <string, object> map = new Dictionary <string, object>();
foreach (string key in titleMap.Keys)
{
int index = titleMap[key];
object contentCellValue = GetCellValue(row.GetCell(index));
map.Add(key, null == contentCellValue ? "" : contentCellValue.ToString());
}
list.Add(map);
}
}
catch (System.Exception)
{
throw;
}
finally
{
stream.Close();
}
return(list);
}