//导出个人比对表到文件
public string PersonToFile(string path, int query_id)
{
IWorkbook workbook;
try
{
using (FileStream file = new FileStream(System.Environment.CurrentDirectory + "\\Templates\\比对表.xlsx", FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(file);
}
ISheet sheet = workbook.GetSheetAt(0);
BLL.query_detail bDetail = new BLL.query_detail();
Model.query_detail mDetail = bDetail.GetModel(query_id);
int[] cellpos = NPOI_CellChange("B3");
ICell cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
IRow row;
cell.SetCellValue(mDetail.full_name);
cellpos = NPOI_CellChange("E3");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mDetail.work_unit);
cellpos = NPOI_CellChange("G3");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mDetail.post);
BLL.compare_result bResult = new BLL.compare_result();
//护照比对情况
Model.compare_result mResult = bResult.GetModal(string.Format("query_id={0} and result_type='护照'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C5");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("D5");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("F5");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str.Replace("\r\n", ""));
if (mResult.report_str.Length < 30 && mResult.feedback_str.Length < 30 && mResult.compare_str.Length < 30)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//港澳通行证比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='港澳台通行证'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C6");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("D6");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("F6");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str.Replace("\r\n", ""));
if (mResult.report_str.Length < 30 && mResult.feedback_str.Length < 30 && mResult.compare_str.Length < 30)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 50 * 20;
}
}
//出国比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='出国'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C7");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("D7");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("F7");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str.Replace("\r\n", ""));
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//出境比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='出境'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C8");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("D8");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("F8");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str.Replace("\r\n", ""));
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//在国外连续生活一年以上比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='国外生活'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C9");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("D9");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str.Replace("\r\n", ""));
cellpos = NPOI_CellChange("F9");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str.Split('|')[0].Replace("\r\n", ""));
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//房产比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='房产'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C10");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str);
cellpos = NPOI_CellChange("D10");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str);
cellpos = NPOI_CellChange("F10");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str);
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//股票比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='股票'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C11");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str);
cellpos = NPOI_CellChange("D11");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str);
cellpos = NPOI_CellChange("F11");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str);
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//基金比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='基金'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C11");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(cell.StringCellValue + "\r\n" + mResult.report_str);
cellpos = NPOI_CellChange("D11");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(cell.StringCellValue + "\r\n" + mResult.feedback_str);
cellpos = NPOI_CellChange("F11");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
if (!mResult.compare_str.Contains("基本一致"))
{
if (cell.StringCellValue.Contains("基本一致"))
{
cell.SetCellValue(mResult.compare_str);
}
else
{
cell.SetCellValue(cell.StringCellValue + "\r\n" + mResult.compare_str);
}
}
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 &&
mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//保险比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='保险'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C12");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str);
cellpos = NPOI_CellChange("D12");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str);
cellpos = NPOI_CellChange("F12");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str);
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//工商比对情况
mResult = bResult.GetModal(string.Format("query_id={0} and result_type='工商'", query_id));
if (mResult != null)
{
cellpos = NPOI_CellChange("C13");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.report_str);
cellpos = NPOI_CellChange("D13");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.feedback_str);
cellpos = NPOI_CellChange("F13");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cell.SetCellValue(mResult.compare_str);
if (mResult.report_str.Length < 15 && mResult.feedback_str.Length < 15 && mResult.compare_str.Length < 15)
{
row = sheet.GetRow(cellpos[0]);
row.Height = 25 * 20;
}
}
//认定比对结果
cellpos = NPOI_CellChange("C14");
cell = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
cellpos = NPOI_CellChange("C15");
ICell cell2 = sheet.GetRow(cellpos[0]).GetCell(cellpos[1]);
if (!mDetail.is_match)
{
DataSet ds = bResult.GetList(string.Format("query_id={0} and compare_str not like ('%基本一致%')", query_id));
cell.SetCellValue("比对结果不一致：\r\n");
string cellValue = "";
int rowcount = 6;//单元格字符行数，用于估算行高
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cellValue += (i + 1).ToString() + "、" + ds.Tables[0].Rows[i]["compare_str"].ToString().Replace("\r\n", "") + "\r\n";
}
cell2.SetCellValue(cell2.StringCellValue.Replace("{MatchStr}", cellValue));
}
else
{
cell.SetCellValue(EqualStr);
cell2.SetCellValue(cell2.StringCellValue.Replace("{MatchStr}", ""));
}
cell2.SetCellValue(cell2.StringCellValue.Replace("{CompareDate}",
DateTime.Now.ToLongDateString()));
using (FileStream fileStream = File.Open(path, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
fileStream.Close();
}
return("OK");
}
catch (Exception e)
{
return(e.Message);
}
}
/// <summary>
/// 获取EXCEL表数据
/// 返回为datatable
/// </summary>
/// <param name="stream">文件流</param>
/// <returns>Excel中的所有数据</returns>
public DataSet GetExcel(Stream stream)
{
this._allData.Clear();
IWorkbook workbook;
if (stream.Length > 0)
{
workbook = WorkbookFactory.Create(stream);
stream.Close();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = new DataTable();
dt.TableName = sheet.SheetName;
IRow excelColumnRow = sheet.GetRow(0);
if (excelColumnRow != null)
{
//获取列头, 现仅支持第一行作为列头
for (int k = 0; k < excelColumnRow.LastCellNum; k++)
{
DataColumn col;
ICell cell = excelColumnRow.GetCell(k);
object v = GetValueFromICell(cell);
if (null != v && DBNull.Value != v)
{
col = new DataColumn(v.ToString(), typeof(string));
}
else
{
//DataRow dr = this._errorInfo.NewRow();
//dr[0] = sheet.SheetName;
//dr[1] = "列头验证";
//dr[2] = i + 1;
//dr[3] = String.Format("工作表【{0}】第【{1}】行,列名为空", sheet.SheetName, i + 1);
//this._errorInfo.Rows.Add(dr);
col = new DataColumn("col" + k.ToString(), typeof(string));
}
//如果列头已存在就返回错误信息
if (!dt.Columns.Contains(col.ColumnName))
{
dt.Columns.Add(col);
}
else
{
DataRow dr = this._errorInfo.NewRow();
dr[0] = sheet.SheetName;
dr[1] = "列头验证";
dr[2] = 1;
dr[3] = String.Format("工作表【{0}】第【{1}】列列头重复", sheet.SheetName, k);
this._errorInfo.Rows.Add(dr);
return(null);
}
}
IRow iRow; DataRow row;
//获取行数据,从第2行开始获取
for (int j = 1; j <= sheet.LastRowNum; j++)
{
row = dt.NewRow();
iRow = sheet.GetRow(j);
ICell cell;
if (iRow != null && iRow.Cells.Count == excelColumnRow.LastCellNum)
{
bool isHasValue = false;
for (int f = 0; f < iRow.LastCellNum; f++)
{
cell = iRow.GetCell(f);
object v = GetValueFromICell(cell);
if (null != v && DBNull.Value != v)
{
row[f] = v.ToString().Trim();
isHasValue = true;
}
else
{
row[f] = DBNull.Value;
}
}
if (isHasValue)
{
dt.Rows.Add(row);
}
}
else
{
DataRow dr = this._errorInfo.NewRow();
dr[0] = sheet.SheetName;
dr[1] = "数据内容验证";
dr[2] = j + 1;
dr[3] = String.Format("工作表【{0}】第【{1}】行数据内容错误", sheet.SheetName, j);
this._errorInfo.Rows.Add(dr);
return(null);
}
}
this._allData.Tables.Add(dt);
}
else
{
//DataRow dr = this._errorInfo.NewRow();
//dr[0] = sheet.SheetName;
//dr[1] = "列头验证";
//dr[2] = 1;
//dr[3] = String.Format("获取工作表【{0}】列头错误", sheet.SheetName);
//this._errorInfo.Rows.Add(dr);
//this._allData = null;
//return null;
}
}
}
return(this._allData);
}
/// <summary>
/// 导出入库数据到excle
/// </summary>
/// <param name="fields">字段</param>
/// <param name="query">数据集</param>
/// <param name="path">excle模版的位置</param>
/// <param name="from">显示的标题默认行数为1</param>
/// <returns></returns>
public string WriteExcleRuKu(string[] fields, dynamic[] query, string path = @"~/up/ruku.xls", int from = 1)
{
string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path);
FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
IWorkbook hssfworkbook = WorkbookFactory.Create(file); //new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet("入库单");
string guid = Guid.NewGuid().ToString();
string saveFileName = xlsPath.Path(@"RuKu/" + guid);
Dictionary <string, string> propertyName;
PropertyInfo[] properties;
//标题行 委托单号 器具名称 型号 出厂编号 证书单位 客户特殊要求 器具所在位置 器具状态 入库说明
var titles = "条码,委托单号,器具名称,型号,出厂编号,证书单位,客户特殊要求,器具所在位置,器具状态,入库说明".Split(',');
var dd = sheet.GetRow(0).GetCell(1).CellStyle;
HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.ShrinkToFit = true;
for (int i = 0; i < titles.Length; i++)
{
if (!string.IsNullOrWhiteSpace(titles[i]))
{
var cell = dataRow.CreateCell(i);
cell.CellStyle = dd;
cell.SetCellValue(titles[i]); //列值
}
}
//内容行
for (int i = 0; i < query.Length; i++)
{
propertyName = new Dictionary <string, string>();
if (query[i] == null)
{
continue;
}
Type type = query[i].GetType();
properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo property in properties)
{
object o = property.GetValue(query[i], null);
if (!string.IsNullOrEmpty(property.Name) && o != null)
{
propertyName.Add(property.Name, o.ToString());
}
}
int j = 0;
dataRow = sheet.CreateRow(i + from) as HSSFRow;
fields.All(a =>
{
if (propertyName.ContainsKey(a)) //列名
{
var cell = dataRow.CreateCell(j);
cell.SetCellValue(propertyName[a]);
//列值
}
j++;
return(true);
});
}
sheet.ForceFormulaRecalculation = true;
using (FileStream fileWrite = new FileStream(saveFileName, FileMode.Create))
{
hssfworkbook.Write(fileWrite);
}
//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(string.Format("../../up/RuKu/{0}.xls", guid));
//记录日志
}
/// <summary>
/// 导入
/// </summary>
/// <typeparam name="T">具体对象</typeparam>
/// <param name="fs"></param>
/// <param name="fileName"></param>
/// <param name="isFirstRowColumn"></param>
/// <returns></returns>
public static IEnumerable <T> ExcelToDataTable <T>(Stream fs, bool isFirstRowColumn = false) where T : new()
{
List <T> ts = new List <T>();
Type type = typeof(T);
IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
try
{
IWorkbook workbook = WorkbookFactory.Create(fs);
fs.Dispose();
ISheet sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
Int32 startRow = isFirstRowColumn ? 1 : 0;
int rowCount = sheet.LastRowNum; //行数
int length = checkPropertyInfos.Count;
length = length > cellCount + 1 ? cellCount + 1 : length;
Boolean haveValue = false;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
T t = new T();
for (int f = 0; f < length; f++)
{
ICell cell = row.GetCell(f);
if (cell == null || String.IsNullOrEmpty(cell.ToString()))
{
continue;
}
object b = cell.ToString();
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
b = cell.DateCellValue;
}
else
{
b = cell.NumericCellValue;
}
}
PropertyInfo pinfo = checkPropertyInfos[f];
if (pinfo.PropertyType.Name != b.GetType().Name) //类型不一样的时候，强转
{
b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());
}
type.GetProperty(pinfo.Name).SetValue(t, b, null);
if (!haveValue)
{
haveValue = true;
}
}
if (haveValue)
{
ts.Add(t); haveValue = false;
}
}
}
return(ts);
}
catch (Exception ex)
{
return(null);
}
}
/// <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>
/// Fetches objects from the specified sheet index.
/// </summary>
/// <typeparam name="T">The type of objects the Excel file is mapped to.</typeparam>
/// <param name="stream">The stream the Excel file is read from.</param>
/// <param name="sheetIndex">Index of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable <T> Fetch <T>(Stream stream, int sheetIndex) where T : new()
{
Workbook = WorkbookFactory.Create(stream);
return(Fetch <T>(sheetIndex));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelMapper"/> class.
/// </summary>
/// <param name="stream">The stream the Excel file is read from.</param>
public ExcelMapper(Stream stream)
{
Workbook = WorkbookFactory.Create(stream);
}
/// <summary>
/// 从文件导入资料到数据库,必须和模板上的列一致
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public static List <CustomerImportDto> DoImport(ImportedFileDto dto)
{
var importDtos = new List <CustomerImportDto>();
if (string.IsNullOrWhiteSpace(dto.FileName))
{
return(importDtos);
}
var fileFullName = Path.Combine(Settings.ImportCustomerFilesPathFullname, dto.FileName);
using (var fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Read, FileShare.Read))
{
var workbook = WorkbookFactory.Create(fs);
if (workbook != null)
{
var sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
var rowCount = sheet.LastRowNum; //总行数
if (rowCount > 0)
{
var firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //列数
for (var r = 1; r < rowCount; r++)
{
var row = sheet.GetRow(r);
var importDto = new CustomerImportDto();
var cell = row.GetCell(0); //分类
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.CustomerCategoryName = str;
}
}
cell = row.GetCell(1); //姓名
if (cell != null)
{
var str = cell.ToString();
if (string.IsNullOrWhiteSpace(str))
{
continue;
}
importDto.RealName = str;
}
cell = row.GetCell(2); //性别
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.GenderDescription = str;
}
}
cell = row.GetCell(3); //昵称
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.NickName = str;
}
}
cell = row.GetCell(4); //出生年月
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str) && cell.CellType == CellType.Numeric)
{
importDto.BirthdayDescription = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm");
}
}
cell = row.GetCell(5); //手机
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.MobilePhoneNumber = str;
}
}
cell = row.GetCell(6); //QQ
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.Qq = str;
}
}
cell = row.GetCell(7); //微信
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.Wechat = str;
}
}
cell = row.GetCell(8); //联系地址
if (cell != null)
{
var str = cell.ToString();
if (!string.IsNullOrWhiteSpace(str))
{
importDto.Address = str;
}
}
importDtos.Add(importDto);
}
}
}
}
}
return(importDtos);
}
public static void InsertSheet(string outputFile, string sheetname, DataTable dt)
{
FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
IWorkbook hssfworkbook = WorkbookFactory.Create(readfile);
//HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
int num = hssfworkbook.GetSheetIndex(sheetname);
ISheet sheet1;
if (num >= 0)
{
sheet1 = hssfworkbook.GetSheet(sheetname);
}
else
{
sheet1 = hssfworkbook.CreateSheet(sheetname);
}
try
{
if (sheet1.GetRow(0) == null)
{
sheet1.CreateRow(0);
}
for (int coluid = 0; coluid < dt.Columns.Count; coluid++)
{
if (sheet1.GetRow(0).GetCell(coluid) == null)
{
sheet1.GetRow(0).CreateCell(coluid);
}
sheet1.GetRow(0).GetCell(coluid).SetCellValue(dt.Columns[coluid].ColumnName);
}
}
catch (Exception ex)
{
//wl.WriteLogs(ex.ToString());.WriteLogs(ex.ToString());
throw;
}
for (int i = 1; i <= dt.Rows.Count; i++)
{
try
{
if (sheet1.GetRow(i) == null)
{
sheet1.CreateRow(i);
}
for (int coluid = 0; coluid < dt.Columns.Count; coluid++)
{
if (sheet1.GetRow(i).GetCell(coluid) == null)
{
sheet1.GetRow(i).CreateCell(coluid);
}
sheet1.GetRow(i).GetCell(coluid).SetCellValue(dt.Rows[i - 1][coluid].ToString());
}
}
catch (Exception ex)
{
//wl.WriteLogs(ex.ToString());.WriteLogs(ex.ToString());
//throw;
}
}
try
{
readfile.Close();
FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);
hssfworkbook.Write(writefile);
writefile.Close();
}
catch (Exception ex)
{
//wl.WriteLogs(ex.ToString());.WriteLogs(ex.ToString());
}
}
public override Task <IDataToSent> TransformData(IDataToSent receiveData)
{
if (!File.Exists(fileName))
{
throw new FileNotFoundException(fileName);
}
receiveData ??= new DataToSentTable();
var dtExcel = new DataTable();
dtExcel.TableName = Path.GetFileName(fileName);
dtExcel.Columns.Add("Number", typeof(int));
dtExcel.Columns.Add("SheetName", typeof(string));
FastAddTable(receiveData, dtExcel);
IWorkbook wb;
using (var file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
wb = WorkbookFactory.Create(file);
}
if (wb is XSSFWorkbook)
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}
else
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}
var nrSheets = wb.NumberOfSheets;
var sheetsTable = new List <DataTable>();
for (int indexSheet = 0; indexSheet < nrSheets; indexSheet++)
{
var sheet = wb.GetSheetAt(indexSheet);
var dtSheet = new DataTable();
sheetsTable.Add(dtSheet);
dtSheet.TableName = sheet.SheetName;
dtExcel.Rows.Add(new object[2] {
indexSheet, sheet.SheetName
});
var lastRow = sheet.LastRowNum + 1;
if (lastRow < 1)
{
continue;
}
//header
var rowHeader = sheet.GetRow(0);
if (rowHeader == null)
{
continue;
}
if (lastRow == 1)
{
//just the header, no data
foreach (var item in rowHeader.Cells)
{
dtSheet.Columns.Add(item.StringCellValue, typeof(string));
}
}
else//it has another row, let's see
{
var secondRow = sheet.GetRow(1);
var maxCels = secondRow.LastCellNum;
for (var i = 0; i < maxCels; i++)
{
var cell = secondRow.GetCell(i);
string name = (rowHeader.GetCell(i)?.ToString()) ?? "";
if (string.IsNullOrEmpty(name))//no empty headers
{
break;
}
Type t = typeof(string);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Formula:
t = FromCellType(cell.CachedFormulaResultType);
break;
default:
t = FromCellType(cell.CellType);
break;
}
}
dtSheet.Columns.Add(name, t);
}
while (dtSheet.Columns.Count < rowHeader.LastCellNum)
{
dtSheet.Columns.Add(rowHeader.GetCell(dtSheet.Columns.Count).StringCellValue, typeof(string));
}
}
var nrCols = dtSheet.Columns.Count;
for (int iRow = 1; iRow < lastRow; iRow++)
{
rowHeader = sheet.GetRow(iRow);
if ((rowHeader?.Cells?.Count ?? 0) < 1)
{
continue;
}
var valuesWithColumnIndex = rowHeader.Cells.Select(it => ValueCell(it, it?.CellType)).ToList();
var values = new object[nrCols];
for (var nrIndex = 0; nrIndex < nrCols; nrIndex++)
{
values[nrIndex] = null;
}
foreach (var item in valuesWithColumnIndex)
{
if (item.Item1 < nrCols)
{
values[item.Item1] = item.Item2;
}
}
if (values.Count(it => (it == null || it == DBNull.Value)) == nrCols)
{
continue;//do not add empty rows
}
try
{
bool canAdd = true;
for (int i = 0; i < nrCols; i++)
{
var currentType = dtSheet.Columns[i].DataType;
if (!CanChangeTypeToDataColumn(values[i], currentType))
{
ConvertColumnType(dtSheet, dtSheet.Columns[i].ColumnName, typeof(string));
currentType = dtSheet.Columns[i].DataType;
if (!CanChangeTypeToDataColumn(values[i], currentType))
{
string message = ($"cannot add row {iRow} because {values[i]} do not match {dtSheet.Columns[i].DataType}");
Console.WriteLine(message);
canAdd = false;
}
}
}
if (canAdd)
{
dtSheet.Rows.Add(values.ToArray());
}
}
catch (Exception ex)
{
string s = ex.Message;
throw;
}
}
}
FastAddTables(receiveData, sheetsTable.ToArray());
return(Task.FromResult(receiveData));
}
/// <summary>
/// 将EXCEL文件导入到DataTable
/// </summary>
/// <param name="filePath">EXCEL路径</param>
/// <param name="sheetIndex">Sheet索引</param>
/// <param name="headIndex">列索引</param>
/// <param name="rowIndex">行起始索引</param>
/// <returns>DataTable</returns>
/// 时间：2016/10/11 17:07
/// 备注：
public static DataTable ToDataTable(string filePath, ushort sheetIndex, ushort headIndex, ushort rowIndex)
{
ValidateOperator.Begin().NotNull(filePath, "需要导入到EXCEL文件路径").IsFilePath(filePath).CheckFileExists(filePath);
DataTable _table = new DataTable();
IWorkbook _hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
_hssfworkbook = WorkbookFactory.Create(file);
}
ISheet _sheet = _hssfworkbook.GetSheetAt(sheetIndex);
AddDataColumns(_sheet, headIndex, _table);
bool _supportFormula = string.Compare(Path.GetExtension(filePath), ".xlsx", true) == 0;
for (int i = (_sheet.FirstRowNum + rowIndex); i <= _sheet.LastRowNum; i++)
{
IRow _row = _sheet.GetRow(i);
bool _emptyRow = true;
if (_row == null)
{
continue;
}
object[] _itemArray = new object[_row.LastCellNum];
for (int j = _row.FirstCellNum; j < _row.LastCellNum; j++)
{
if (_row.GetCell(j) == null)
{
continue;
}
switch (_row.GetCell(j).CellType)
{
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(_row.GetCell(j))) //日期类型
{
_itemArray[j] = _row.GetCell(j).DateCellValue;
}
else //其他数字类型
{
_itemArray[j] = _row.GetCell(j).NumericCellValue;
}
break;
case CellType.Blank:
_itemArray[j] = string.Empty;
break;
case CellType.Formula:
IFormulaEvaluator _eva = null;
if (_supportFormula)
{
_eva = new XSSFFormulaEvaluator(_hssfworkbook);
}
else
{
_eva = new HSSFFormulaEvaluator(_hssfworkbook);
}
if (_eva.Evaluate(_row.GetCell(j)).CellType == CellType.Numeric)
{
_itemArray[j] = _eva.Evaluate(_row.GetCell(j)).NumberValue;
}
else
{
_itemArray[j] = _eva.Evaluate(_row.GetCell(j)).StringValue;
}
break;
default:
_itemArray[j] = _row.GetCell(j).StringCellValue;
break;
}
if (_itemArray[j] != null && !string.IsNullOrEmpty(_itemArray[j].ToString().Trim()))
{
_emptyRow = false;
}
}
if (!_emptyRow)
{
_table.Rows.Add(_itemArray);
}
}
return(_table);
}
/// <summary>
/// 将单元格的值转化为字符串
/// </summary>
/// <param name="tCell"></param>
/// <returns></returns>
private string getCellStringValueAllCase(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.Formula:
IFormulaEvaluator fe = WorkbookFactory.CreateFormulaEvaluator(tCell.Sheet.Workbook);
var cellValue = fe.Evaluate(tCell);
switch (cellValue.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
tempValue = cellValue.BooleanValue.ToString();
break;
case CellType.Error:
break;
case CellType.Formula:
break;
case CellType.Numeric:
tempValue = cellValue.NumberValue.ToString();
break;
case CellType.String:
tempValue = cellValue.StringValue.ToString();
break;
case CellType.Unknown:
break;
default:
break;
}
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;
case CellType.Unknown:
break;
default:
break;
}
return(tempValue);
}
public ActionResult FAQImportTo()
{
string _ReturnStr = string.Empty;
try
{
var _FAQList = AllFAQList;
HttpPostedFileBase file = Request.Files[0];
string _Path = Path.Combine(HttpContext.Server.MapPath("~/Content/Upload/"), "FAQImport.xls");
file.SaveAs(_Path);
int _SuccessCount = 0;
int _AllCount = 0;
using (FileStream fs = new FileStream(_Path, FileMode.Open, FileAccess.Read))
{
IWorkbook wb = WorkbookFactory.Create(fs);
ISheet sheet1 = wb.GetSheetAt(0);
_AllCount = sheet1.LastRowNum;
for (int i = 1; i < sheet1.LastRowNum + 1; i++)
{
try
{
IRow _Row = sheet1.GetRow(i);
FAQ _FAQ = new FAQ
{
Orderchannel = _Row.GetCell(1).ToString(),
CateOne = _Row.GetCell(2).ToString(),
CateTwo = _Row.GetCell(3).ToString(),
CateThree = _Row.GetCell(4).ToString(),
Question = _Row.GetCell(5).ToString(),
Answer = _Row.GetCell(6).ToString()
};
//if (_FAQList.Where(p =>
// p.Orderchannel.Equals(_FAQ.Orderchannel) &&
// p.CateOne.Equals(_FAQ.CateOne) &&
// p.CateTwo.Equals(_FAQ.CateTwo) &&
// p.CateThree.Equals(_FAQ.CateThree) &&
// p.Question.Equals(_FAQ.Question)
// )
// .FirstOrDefault() == null)
//{
// manager.Add(_FAQ);
// _SuccessCount++;
//}
manager.Add(_FAQ);
_SuccessCount++;
}
catch { }
}
System.IO.File.Delete(_Path);
System.Web.Caching.Cache objCache = System.Web.HttpRuntime.Cache;
if (objCache["AllFAQList"] != null)
{
objCache.Remove("AllFAQList");
}
_ReturnStr = "导入成功！总条数：" + _AllCount + ",有效条数：" + _SuccessCount;
}
}
catch (Exception ex)
{
_ReturnStr = "导入失败！原因：" + ex.Message + ":请参照订单模板数据！";
}
return(Content("<script>alert('" + _ReturnStr + "');location='/FAQ/FAQManage'</script>"));
}
public static DataTable ExcelSheetToDataTable(string excelFileLocation, int excelSheetIndex)
{
// --------------------------------- //
/* REFERENCES:
* NPOI.dll
* NPOI.OOXML.dll
* NPOI.OpenXml4Net.dll */
// --------------------------------- //
/* USING:
* using NPOI.SS.UserModel;
* using NPOI.HSSF.UserModel;
* using NPOI.XSSF.UserModel; */
// --------------------------------- //
DataTable Tabla = null;
try
{
if (System.IO.File.Exists(excelFileLocation))
{
IWorkbook workbook = null; //IWorkbook xls or xlsx
ISheet worksheet = null;
string sheetName = "";
using (FileStream FS = new FileStream(excelFileLocation, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(FS); //Can be XLS or XLSX
worksheet = workbook.GetSheetAt(excelSheetIndex
); // Get the desired sheet at the appropriate index
sheetName = worksheet.SheetName; //Get the sheet name
Tabla = new DataTable(sheetName);
Tabla.Rows.Clear();
Tabla.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;
if (row != null) //null is when the row only contains empty cells
{
if (rowIndex > 0)
{
NewReg = Tabla.NewRow();
}
// Loop over all row cells
foreach (ICell cell in row.Cells)
{
object valorCell = null;
string cellType = "";
if (rowIndex == 0) //The first row contains headers
{
// The concrete column types are to retrieved from the first data row (e.g. #1)
row2 = worksheet.GetRow(rowIndex + 1);
ICell cell2 = row2.GetCell(cell.ColumnIndex);
switch (cell2.CellType)
{
case CellType.Boolean: cellType = "System.Boolean"; break;
case CellType.String: cellType = "System.String"; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell2))
{
cellType = "System.DateTime";
}
else
{
cellType = "System.Double";
}
break;
case CellType.Formula:
switch (cell2.CachedFormulaResultType)
{
case CellType.Boolean: cellType = "System.Boolean"; break;
case CellType.String: cellType = "System.String"; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell2))
{
cellType = "System.DateTime";
}
else
{
cellType = "System.Double";
}
break;
}
break;
default:
cellType = "System.String"; break;
}
// Create an appropriate UI DataTable column with the appropriate type
DataColumn codigo = new DataColumn(cell.StringCellValue, System.Type.GetType(cellType));
Tabla.Columns.Add(codigo);
}
else
{
// For the concrete data rows...
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 = DateTime.FromOADate(cell.NumericCellValue);
/*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;
}
try
{
NewReg[cell.ColumnIndex] = valorCell;
} catch (Exception e)
{
}
}
}
}
if (rowIndex > 0)
{
Tabla.Rows.Add(NewReg);
}
}
Tabla.AcceptChanges();
}
}
else
{
string message = "ERROR: The file " + excelFileLocation + " doesn't exist!!! ";
//if (result == DialogResult.Yes)
//{
// this.Close();
//}
//else
//{
// // Do something
//}
throw new Exception(message);
}
}
catch (Exception ex)
{
throw ex;
}
return(Tabla);
}
/// <summary>
/// Fetches objects from the specified sheet index.
/// </summary>
/// <typeparam name="T">The type of objects the Excel file is mapped to.</typeparam>
/// <param name="file">The path to the Excel file.</param>
/// <param name="sheetIndex">Index of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable <T> Fetch <T>(string file, int sheetIndex) where T : new()
{
Workbook = WorkbookFactory.Create(file);
return(Fetch <T>(sheetIndex));
}
/// <summary>
/// Fetches objects from the specified sheet name.
/// </summary>
/// <param name="type">The type of objects the Excel file is mapped to.</param>
/// <param name="file">The path to the Excel file.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable Fetch(string file, Type type, string sheetName)
{
Workbook = WorkbookFactory.Create(file);
return(Fetch(type, sheetName));
}
/// <summary>
/// Fetches objects from the specified sheet name.
/// </summary>
/// <typeparam name="T">The type of objects the Excel file is mapped to.</typeparam>
/// <param name="stream">The stream the Excel file is read from.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable <T> Fetch <T>(Stream stream, string sheetName) where T : new()
{
Workbook = WorkbookFactory.Create(stream);
return(Fetch <T>(sheetName));
}
/// <summary>
/// Fetches objects from the specified sheet index.
/// </summary>
/// <param name="type">The type of objects the Excel file is mapped to.</param>
/// <param name="file">The path to the Excel file.</param>
/// <param name="sheetIndex">Index of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable Fetch(string file, Type type, int sheetIndex)
{
Workbook = WorkbookFactory.Create(file);
return(Fetch(type, sheetIndex));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelMapper"/> class.
/// </summary>
/// <param name="file">The path to the Excel file.</param>
public ExcelMapper(string file)
{
Workbook = WorkbookFactory.Create(file);
}
/// <summary>
/// Fetches objects from the specified sheet name.
/// </summary>
/// <param name="type">The type of objects the Excel file is mapped to.</param>
/// <param name="stream">The stream the Excel file is read from.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable Fetch(Stream stream, Type type, string sheetName)
{
Workbook = WorkbookFactory.Create(stream);
return(Fetch(type, sheetName));
}
/// <summary>
/// Recupère les données d'un fichier excel, sans excel, utilisable sur serveur
/// </summary>
/// <param name="iFilePath"></param>
/// <param name="iColumnIndexList">Liste des numéros de colonne, commence à 1</param>
/// <param name="iSheetIndex">Index de la page à récupérer, commence à 1</param>
/// <param name="iAllowIncompleteRow">Autorise une ligne avec des colonnes obligatoires vides</param>
/// <param name="iMasterColumn">Numéro de la colonne qui ne peut pas être vide, sinon c'est que nous sommes la fin du fichier</param>
/// <returns></returns>
public List <List <string> > GetListFromExcelFile(string iFilePath, List <int> iColumnIndexList, int iSheetIndex, bool iAllowIncompleteRow, int iFirstRow)
{
if (iFilePath == null)
{
throw new ArgumentNullException("Chemin non renseigné");
}
if (System.IO.File.Exists(iFilePath) == false)
{
throw new ArgumentNullException("Le chemin {0] n'existe pas".FormatString(iFilePath));
}
if (iColumnIndexList.IsNotNullAndNotEmpty() &&
iSheetIndex != 0)
{
var rowList = new List <List <string> >();
IWorkbook workbook;
using (FileStream file = new FileStream(iFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
workbook = WorkbookFactory.Create(file);
var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
var sheet = workbook.GetSheetAt(iSheetIndex - 1);
int lastUsedRow = sheet.LastRowNum;
//bouclage sur les lignes
int rowCounter = 1;
int rowCount = lastUsedRow + 1 - iFirstRow + 1;
for (int rowIndex = iFirstRow - 1; rowIndex <= lastUsedRow; rowIndex++)
{
var isCompletRow = true;
var columnList = new List <string>();
//bouclage sur les colonnes
foreach (var columnIndex in iColumnIndexList.Enum())
{
CancellationToken.ThrowIfCancellationRequested();
var cellRange = sheet.GetRow(rowIndex).GetCell(columnIndex - 1);
if (cellRange != null)
{
columnList.Add(GetStringVisibleValue(cellRange, evaluator).Replace("\n", Environment.NewLine));
}
else
{
isCompletRow = iAllowIncompleteRow;
columnList.Add(string.Empty);
if (isCompletRow == false)
{
break;
}
}
}
if (isCompletRow)
{
rowList.Add(columnList);
MyDebug.PrintInformation("Ligne {0} complète importée".FormatString(rowIndex), rowCount, rowCounter);
}
else
{
MyDebug.PrintInformation("Ligne {0} imcomplète".FormatString(rowIndex), rowCount, rowCounter);
}
rowCounter++;
}
MyDebug.PrintInformation("Fin de l'importation excel '{0}'".FormatString(iFilePath));
return(rowList);
}
return(null);
}
/// <summary>
/// Fetches objects from the specified sheet index.
/// </summary>
/// <param name="type">The type of objects the Excel file is mapped to.</param>
/// <param name="stream">The stream the Excel file is read from.</param>
/// <param name="sheetIndex">Index of the sheet.</param>
/// <returns>The objects read from the Excel file.</returns>
public IEnumerable Fetch(Stream stream, Type type, int sheetIndex)
{
Workbook = WorkbookFactory.Create(stream);
return(Fetch(type, sheetIndex));
}
/// <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 void ImportExcelCatch()
{
String path = Server.MapPath(Request["file"]);
if (File.Exists(path) == false)
{
die("File not found: " + path);
}
fileColums = GetFileColumns(path);
mappedColumns = new Dictionary <string, uint>();
IWorkbook genericWB = WorkbookFactory.GetExcel2007Reader(path);
IWorksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
for (uint r = genericWS.FirstRow + 1; r <= genericWS.LastRow; r++)
{
IRow row = genericWS.Rows.GetRow(r);
String Cruise = GetCellValue(row, "catchCruise");
String Trawl = GetCellValue(row, "catchTrawl");
String Station = GetCellValue(row, "catchStation");
String Common = GetCellValue(row, "catchCommon");
String Genus = GetCellValue(row, "catchGenus");
String Species = GetCellValue(row, "catchSpecies");
String Abundance = GetCellValue(row, "catchAbundance");
String Biomass = GetCellValue(row, "catchBiomass");
String Description = GetCellValue(row, "catchDescription");
if (Abundance.Trim() == "")
{
Abundance = "0";
}
if (Biomass.Trim() == "")
{
Biomass = "0";
}
Response.Write(Common + " " + Genus + " " + Species + ": ");
manSpecies ms = new manSpecies();
int species = ms.GetSpeciesID(Genus, Species, Common);
if (species == 0)
{
Response.Write("Failed to add species");
}
else
{
manEvents me = new manEvents();
int eventID = me.FindEventID(Cruise, Trawl, Station);
if (eventID == 0)
{
die(String.Format("Event not found: {0}, {1}, {2}", Cruise, Trawl, Station));
}
manEventSpecies mes = new manEventSpecies();
mes.addEventSpecies(eventID, species, double.Parse(Abundance), double.Parse(Biomass), Description);
}
Response.Write("<br>");
Response.Flush();
}
}
internal static IWorkbook ExecuteIWorkBookGet(Stream fileStream)
{
return(WorkbookFactory.Create(fileStream));
}
public void ImportExcelTrawl()
{
String path = Server.MapPath(Request["file"]);
if (File.Exists(path) == false)
{
die("File not found: " + path);
}
fileColums = GetFileColumns(path);
mappedColumns = new Dictionary <string, uint>();
// resolve indicators
Dictionary <uint, int> findicators = new Dictionary <uint, int>();
if (Request["indicators"] != null && Request["indicators"] != "")
{
String[] sindicators = Request["indicators"].Split('~');
foreach (String indicator in sindicators)
{
String[] indic = indicator.Split('|');
int indicatorID = int.Parse(indic[0]);
uint column = fileColums[indic[1]];
findicators[column] = indicatorID;
}
}
IWorkbook genericWB = WorkbookFactory.GetExcel2007Reader(path);
IWorksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
for (uint r = genericWS.FirstRow + 1; r <= genericWS.LastRow; r++)
{
IRow row = genericWS.Rows.GetRow(r);
String survey = GetCellValue(row, "fieldSurvey");
String cruise = GetCellValue(row, "fieldCruise");
String trawl = GetCellValue(row, "fieldTrawl");
String Station = GetCellValue(row, "fieldStation");
String sLatHour = GetCellValue(row, "sLatHour");
String sLatMin = GetCellValue(row, "sLatMin");
String sLonHour = GetCellValue(row, "sLonHour");
String sLonMin = GetCellValue(row, "sLonMin");
String eLatHour = GetCellValue(row, "eLatHour");
String eLatMin = GetCellValue(row, "eLatMin");
String eLonHour = GetCellValue(row, "eLonHour");
String eLonMin = GetCellValue(row, "eLonMin");
String comments = GetCellValue(row, "Comments");
int dateYear = int.Parse(GetCellValue(row, "dateYear"));
int dateMonth = int.Parse(GetCellValue(row, "dateMonth"));
int dateDay = int.Parse(GetCellValue(row, "dateDay"));
double duration = double.Parse(GetCellValue(row, "Duration"));
Dictionary <int, string> indicators = new Dictionary <int, string>();
foreach (uint col in findicators.Keys)
{
int indicator = findicators[col];
indicators[indicator] = GetCellValue(row, col);
Response.Write("Row = " + r + ", Indicator: " + indicator + " = " + indicators[indicator] + "<br>");
}
double lat1 = -ParseCoord(sLatHour, sLatMin);
double lon1 = ParseCoord(sLonHour, sLonMin);
double lat2 = -ParseCoord(eLatHour, eLatMin);
double lon2 = ParseCoord(eLonHour, eLonMin);
manSurveys sv = new manSurveys();
int nSurveyID = sv.GetSurveyID(2, survey);
if (nSurveyID < 1)
{
die("Failed to create survey");
}
manStations st = new manStations();
int nStationID = st.GetStationID(Station, lat1, lon1);
if (nStationID < 1)
{
die("Failed to create station");
}
manEvents ev = new manEvents();
ev.res = Response;
int nEventID = ev.GetEventID(nSurveyID, nStationID, dateYear, dateMonth, dateDay, cruise, trawl, lat1, lon1, lat2, lon2, duration, comments, indicators);
}
Response.Write("cools!");
}
/// <summary>
/// Excel表格转DataTable
/// </summary>
/// <param name="FilePath">Excel文件物理路径</param>
/// <returns>DataTable</returns>
public DataTable ExcelToDataTable(string FilePath)
{
DataTable dt = new DataTable("table1");
using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fs); //使用接口，自动识别excel2003/2007格式
ISheet sheet = workbook.GetSheetAt(0); //得到里面第一个sheet
//表头
IRow headerRow = sheet.GetRow(0); //获得第一行
int cellCount = headerRow.LastCellNum - 1; //获得最后一个单元格的列号
for (int i = 0; i <= cellCount; i++)
{
DataColumn column;
if (headerRow.GetCell(i) == null)
{
column = new DataColumn("");
}
else
{
string ColumnName = headerRow.GetCell(i).StringCellValue.ToString().Trim();
//判断重名
if (dt.Columns.Contains(ColumnName))
{
int RepeatNum = 1;
//加重复数后缀，循环判断
while (dt.Columns.Contains(ColumnName + RepeatNum.ToString()))
{
RepeatNum++;
}
column = new DataColumn(ColumnName + RepeatNum.ToString());
}
else
{
column = new DataColumn(ColumnName);
}
}
dt.Columns.Add(column);
}
//内容
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
IRow Row = sheet.GetRow(i);
if (Row == null)
{
continue;
}
for (int j = 0; j <= cellCount; j++)
{
if (Row.GetCell(j) == null)
{
dr[j] = "";
}
else
{
dr[j] = Row.GetCell(j).ToString().Trim();
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, out List <string> columns)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
columns = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
workbook = WorkbookFactory.Create(fs);
//if (fileName.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase)) // 2007版本及其以上的版本
// workbook = new XSSFWorkbook(fs);
//else if (fileName.EndsWith(".xls", StringComparison.InvariantCultureIgnoreCase)) // 2003版本
// workbook = new HSSFWorkbook(fs);
if (workbook == null)
{
return(null);
}
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)
{
IRow firstRow = sheet.GetRow(0);
int cellFirst = firstRow.FirstCellNum;
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
columns = new List <string>();
if (isFirstRowColumn)
{
for (int i = cellFirst; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
columns.Add(cellValue);
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
for (int i = cellFirst; i < cellCount; ++i)
{
string cellValue = "Field" + i;
columns.Add(cellValue);
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(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);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
/// <summary>
/// transfer stream to workbook
/// </summary>
/// <param name="inputStream">stream</param>
/// <returns>workbook</returns>
public static IWorkbook StreamToWorkBook(Stream inputStream)
{
IWorkbook workBook = WorkbookFactory.Create(inputStream);
return(workBook);
}
//将比对结果添加到汇总表
public string CollectToFile(string path, int list_id)
{
int rowIndex = 0; //excel新建行的行号
IWorkbook workbook;
IRow row;
ICell cell;
ICellStyle style, style2;
//HSSFCellStyle celStyle = getCellStyle();
try
{
using (FileStream file = new FileStream(System.Environment.CurrentDirectory + "\\Templates\\汇总表.xlsx", FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(file);
}
if (workbook is HSSFWorkbook)
{
style = ((HSSFWorkbook)workbook).CreateCellStyle();
style2 = ((HSSFWorkbook)workbook).CreateCellStyle();
}
else
{
style = ((XSSFWorkbook)workbook).CreateCellStyle();
style2 = ((XSSFWorkbook)workbook).CreateCellStyle();
}
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = 128;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = true;
style2.CloneStyleFrom(style);
style2.Alignment = HorizontalAlignment.Left;
ISheet sheet = workbook.GetSheetAt(0);
BLL.query_detail bDetail = new BLL.query_detail();
List <Model.query_detail> list = bDetail.GetModelList(String.Format("where list_id={0} and parent_id=0", list_id));
BLL.compare_result bResult = new BLL.compare_result();
for (int i = 0; i < list.Count; i++)
{
if (list[i].is_compare)
{
style.FillBackgroundColor = 128;
}
else
{
style.FillBackgroundColor = 10;
}
rowIndex = 3 + i;
row = sheet.CreateRow(rowIndex);
for (int j = 0; j < 17; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = style;
}
sheet.GetRow(rowIndex).GetCell(6).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(8).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(10).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(12).CellStyle = style2;
sheet.GetRow(rowIndex).GetCell(14).CellStyle = style2;
cell = sheet.GetRow(rowIndex).GetCell(0);
cell.SetCellValue(i + 1);
cell = sheet.GetRow(rowIndex).GetCell(2);
cell.SetCellValue(list[i].full_name);
cell = sheet.GetRow(rowIndex).GetCell(3);
cell.SetCellValue(list[i].work_unit);
cell = sheet.GetRow(rowIndex).GetCell(4);
cell.SetCellValue(list[i].post);
List <Model.compare_result> mResult = bResult.GetModelList(string.Format("query_id={0}", list[i].id));
foreach (Model.compare_result item in mResult)
{
switch (item.result_type)
{
case "护照":
case "港澳台通行证":
case "出国":
case "出境":
case "国外生活":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(6);
if ("" == cell.StringCellValue)
{
cell = sheet.GetRow(rowIndex).GetCell(5);
cell.SetCellValue("√");
}
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(5);
if ("√" == cell.StringCellValue)
{
cell.SetCellValue("");
}
cell = sheet.GetRow(rowIndex).GetCell(6);
if ("" == cell.StringCellValue)
{
cell.SetCellValue(item.compare_str);
}
else
{
cell.SetCellValue(cell.StringCellValue + "\r\n" + item.compare_str);
}
}
break;
case "房产":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(7);
cell.SetCellValue("√");
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(8);
cell.SetCellValue(item.compare_str);
}
break;
case "股票":
case "基金":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(10);
if ("" == cell.StringCellValue)
{
cell = sheet.GetRow(rowIndex).GetCell(9);
cell.SetCellValue("√");
}
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(9);
if ("√" == cell.StringCellValue)
{
cell.SetCellValue("");
}
cell = sheet.GetRow(rowIndex).GetCell(10);
if ("" == cell.StringCellValue)
{
cell.SetCellValue(item.compare_str);
}
else
{
cell.SetCellValue(cell.StringCellValue + "\r\n" + item.compare_str);
}
}
break;
case "保险":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(11);
cell.SetCellValue("√");
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(12);
cell.SetCellValue(item.compare_str);
}
break;
case "工商":
if (0 <= item.compare_str.IndexOf("基本一致"))
{
cell = sheet.GetRow(rowIndex).GetCell(13);
cell.SetCellValue("√");
}
else
{
cell = sheet.GetRow(rowIndex).GetCell(14);
cell.SetCellValue(item.compare_str);
}
break;
}
}
}
using (FileStream fileStream = File.Open(path + "\\汇总表.xlsx", FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
fileStream.Close();
}
workbook.Close();
return("OK");
}
catch (Exception e)
{
return(e.Message);
}
}
