/// <summary>
/// 更改Excel工作簿内容-导出按钮导出使用
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 1, 6, para.StartTime.Value, para.ReportType);
if (listExport == null || listExport.Count == 0)
{
GetData(para);
}
//获取导出日期数据
if (listExport != null)
{
for (int i = 0; i < listExport.Count; i++)
{
SetValue(sheet, i + 3, 0, listExport[i].CalculTime); //数据日期
SetValue(sheet, i + 3, 1, listExport[i].LineSum.ToString("F2")); //总交通量（万辆）
SetValue(sheet, i + 3, 2, (double.Parse(listExport[i].SumGrow)).ToString("F2")); //同比增幅（%）
SetValue(sheet, i + 3, 3, listExport[i].LineEnSum.ToString("F2")); //进京交通量（万辆）
SetValue(sheet, i + 3, 4, listExport[i].LineExSum.ToString("F2")); //出京交通量（万辆）
SetValue(sheet, i + 3, 5, listExport[i].ExEnPer.ToString("F2")); //出进京比
SetValue(sheet, i + 3, 6, listExport[i].SmaCarFeeNum.ToString("F2")); //小型客车交通量（万辆）
SetValue(sheet, i + 3, 7, (double.Parse(listExport[i].SmaCarCompGrow)).ToString("F2")); //同比增幅（%）
SetValue(sheet, i + 3, 8, listExport[i].SmaCarFee.ToString("F2")); //小型客车免收通行费（万元）
SetValue(sheet, i + 3, 9, listExport[i].ChagSumNum.ToString("F2")); //收费车辆（万辆）
}
}
}
return(readworkbook);
}
/// <summary>
/// 加载工作簿
/// </summary>
/// <param name="fileName">文件名称，绝对路径</param>
protected override void LoadWorkbook(string fileName)
{
FileStream fs = File.OpenRead(fileName);
switch (ExcelFormat)
{
case ExcelFormat.Xls:
_workbook = new HSSFWorkbook(fs);
break;
case ExcelFormat.Xlsx:
_workbook = new XSSFWorkbook(fs);
break;
default:
throw new Exception("未知 Excel 格式文件");
}
fs.Close();
// 读取当前表数据
var sheetNum = _workbook.NumberOfSheets;
for (int i = 0; i < sheetNum; i++)
{
ISheet sheet = _workbook.GetSheetAt(i);
var worksheet = new NpoiWorkSheet(sheet);
WorkSheets.Add(worksheet);
}
}
/// <summary>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
//获取导出日期数据
List <RP_EnEx> pAADTList = db.RP_EnEx.Where(a => a.CalcuTime == para.StartTime && a.StaType == para.StationType).ToList();
if (pAADTList != null && pAADTList.Count > 0)
{
RP_EnEx pInfo = pAADTList.First();
//高速
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //高速入境货车数
SetValue(sheet, 5, 4, 0); //pInfo.EnGre.ToString());//绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
string title = string.Empty;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (holidayName != null && holidayName.Count > 0)
{
title = string.Format("{0}年“{1}”黄金周京津塘高速公路交通量及客运情况统计表", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
}
SetValue(sheet, 0, 0, title);
}
if (listExport != null && listExport.Count > 0)
{
for (int i = 0; i < listExport.Count; i++)
{
SetValue(sheet, i + 4, 0, listExport[i].CalcuTime);
SetValue(sheet, i + 4, 1, listExport[i].EnTra == null ? "" : listExport[i].EnTra.ToString());
SetValue(sheet, i + 4, 2, listExport[i].EnCar == null ? "" : listExport[i].EnCar.ToString());
SetValue(sheet, i + 4, 3, listExport[i].EnTrav == null ? "0.00" : listExport[i].EnTrav.Value.ToString("F2"));
SetValue(sheet, i + 4, 4, listExport[i].ExTra == null ? "" : listExport[i].ExTra.ToString());
SetValue(sheet, i + 4, 5, listExport[i].ExCar == null ? "" : listExport[i].ExCar.ToString());
SetValue(sheet, i + 4, 6, listExport[i].ExTrav == null ? "0.00" : listExport[i].ExTrav.Value.ToString("F2"));
}
SetValue(sheet, listExport.Count + 4, 0, string.Format("统计人：{0}", qModel.CrtBy));;
}
}
return(readworkbook);
}
/// <summary>
/// 更改Excel工作簿内容
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
string title = string.Empty;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (holidayName != null && holidayName.Count > 0)
{
title = string.Format("{0}年{1}假期进出京交通流量表（北京段）", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
}
SetValue(sheet, 0, 0, title);
}
if (listExport != null)
{
for (int i = 0; i < listExport.Count; i++)
{
SetValue(sheet, i + 3, 0, listExport[i].DataDate);
SetValue(sheet, i + 3, 1, listExport[i].LineEnSum == null ? "" : listExport[i].LineEnSum.ToString());
SetValue(sheet, i + 3, 2, listExport[i].LineExSum == null ? "" : listExport[i].LineExSum.ToString());
SetValue(sheet, i + 3, 3, listExport[i].Total);
}
}
}
return(readworkbook);
}
/// <summary>
/// 读取Execl
/// </summary>
/// <param name="path">文件路径</param>
/// <param name="sum">sheet数量</param>
/// <returns></returns>
public List <DataTable> LoadExcel(string path, int sum)
{
Stream fs = System.IO.File.OpenRead(path);
List <DataTable> tableList = new List <DataTable>();
NPOI.SS.UserModel.IWorkbook wb = WorkbookFactory.Create(fs);
for (int i = 0; i < sum; i++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(i);
DataTable dt = RenderFromExcel(sheet);
tableList.Add(dt);
}
return(tableList);
}
/// <summary>
/// 预测导出--如果需要将入出口数据进行区分，则分别放在两个list的集合中，若不需区分，则将数据放入list1中，list2为空即可
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List <Common.ViewModels.IReportViewModel> list1, List <Common.ViewModels.IReportViewModel> list2)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
//设置日期
SetReportDate(sheet, 0, 13, DateTime.Parse(DateTime.Now.ToShortDateString()), para.ReportType);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
if (list1 != null && list1.Count > 0)
{
ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel;
//高速
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //货车数
SetValue(sheet, 5, 4, pInfo.EnGre.ToString()); //绿色通道数
SetValue(sheet, 5, 5, pInfo.ExSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 6, pInfo.ExOthCar.ToString()); //其他客车
SetValue(sheet, 5, 7, pInfo.ExTruk.ToString()); //货车数
SetValue(sheet, 5, 8, 0); //pInfo.EnGre.ToString());//绿色通道数
//普通
SetValue(sheet, 5, 9, 0); //小型客车
SetValue(sheet, 5, 10, 0); //其他客车
SetValue(sheet, 5, 11, 0); //高速入境货车数
SetValue(sheet, 5, 12, 0); //绿色通道数
SetValue(sheet, 5, 13, 0); //小型客车
SetValue(sheet, 5, 14, 0); //其他客车
SetValue(sheet, 5, 15, 0); //高速入境货车数
SetValue(sheet, 5, 16, 0); //绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 预测导出--如果需要将入出口数据进行区分，则分别放在两个list的集合中，若不需区分，则将数据放入list1中，list2为空即可
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <param name="list1"></param>
/// <param name="list2"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para, List <Common.ViewModels.IReportViewModel> list1, List <Common.ViewModels.IReportViewModel> list2)
{
//获取工作簿
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
if (list1 != null && list1.Count > 0)
{
ForecastEnExViewModel pInfo = list1.First() as ForecastEnExViewModel;
//高速入境
SetValue(sheet, 5, 1, pInfo.EnSmaCar.ToString()); //小型客车
SetValue(sheet, 5, 2, pInfo.EnOthCar.ToString()); //其他客车
SetValue(sheet, 5, 3, pInfo.EnTruk.ToString()); //高速入境货车数
SetValue(sheet, 5, 4, 0); //pInfo.EnGre.ToString());//绿色通道数
}
}
}
return(readworkbook);
}
/// <summary>
/// 修改excel工作簿
/// </summary>
/// <param name="readworkbook"></param>
/// <param name="para"></param>
/// <returns></returns>
public NPOI.SS.UserModel.IWorkbook GenerateSheet(NPOI.SS.UserModel.IWorkbook readworkbook, Common.QueryParameters para)
{
if (readworkbook != null)
{
ISheet sheet = readworkbook.GetSheetAt(0);
string title = string.Empty;
using (DataSubmittedEntities db = new DataSubmittedEntities())
{
var holidayName = db.OT_Dic.Where(a => a.Id == para.HolidayId).Select(a => a.Name).ToList();
if (holidayName != null && holidayName.Count > 0)
{
title = string.Format("天津市高速公路支队{0}年{1}假期重点收费站流量表（出口+入口）", ((DateTime)para.EndTime).Year, holidayName[0].ToString());
}
SetValue(sheet, 0, 0, title);
}
if (listExport != null)
{
//计算查询天数
int count = (para.EndTime.Value - para.StartTime.Value).Days + 1;
if ((para.EndTime.Value - para.StartTime.Value).Days + 1 > 15)
{
count = 15;//最多可查询15天
}
for (int i = 0; i < listExport.Count; i++)
{
if (count > 0)
{
SetValue(sheet, i + 45, 3, listExport[i].Date1);
if (i == 0)
{
SetValue(sheet, 2, 3, para.StartTime.Value.AddDays(0).ToString("M月d日"));
}
}
if (count > 1)
{
SetValue(sheet, i + 45, 4, listExport[i].Date2);
if (i == 0)
{
SetValue(sheet, 2, 4, para.StartTime.Value.AddDays(1).ToString("M月d日"));
}
}
if (count > 2)
{
SetValue(sheet, i + 45, 5, listExport[i].Date3);
if (i == 0)
{
SetValue(sheet, 2, 5, para.StartTime.Value.AddDays(2).ToString("M月d日"));
}
}
if (count > 3)
{
SetValue(sheet, i + 45, 6, listExport[i].Date4);
if (i == 0)
{
SetValue(sheet, 2, 6, para.StartTime.Value.AddDays(3).ToString("M月d日"));
}
}
if (count > 4)
{
SetValue(sheet, i + 45, 7, listExport[i].Date5);
if (i == 0)
{
SetValue(sheet, 2, 7, para.StartTime.Value.AddDays(4).ToString("M月d日"));
}
}
if (count > 5)
{
SetValue(sheet, i + 45, 8, listExport[i].Date6);
if (i == 0)
{
SetValue(sheet, 2, 8, para.StartTime.Value.AddDays(5).ToString("M月d日"));
}
}
if (count > 6)
{
SetValue(sheet, i + 45, 9, listExport[i].Date7);
if (i == 0)
{
SetValue(sheet, 2, 9, para.StartTime.Value.AddDays(6).ToString("M月d日"));
}
}
if (count > 7)
{
SetValue(sheet, i + 45, 10, listExport[i].Date8);
if (i == 0)
{
SetValue(sheet, 2, 10, para.StartTime.Value.AddDays(7).ToString("M月d日"));
}
}
if (count > 8)
{
SetValue(sheet, i + 45, 11, listExport[i].Date9);
if (i == 0)
{
SetValue(sheet, 2, 11, para.StartTime.Value.AddDays(8).ToString("M月d日"));
}
}
if (count > 9)
{
SetValue(sheet, i + 45, 12, listExport[i].Date10);
if (i == 0)
{
SetValue(sheet, 2, 12, para.StartTime.Value.AddDays(9).ToString("M月d日"));
}
}
if (count > 10)
{
SetValue(sheet, i + 45, 13, listExport[i].Date11);
if (i == 0)
{
SetValue(sheet, 2, 13, para.StartTime.Value.AddDays(10).ToString("M月d日"));
}
}
if (count > 11)
{
SetValue(sheet, i + 45, 14, listExport[i].Date12);
if (i == 0)
{
SetValue(sheet, 2, 14, para.StartTime.Value.AddDays(11).ToString("M月d日"));
}
}
if (count > 12)
{
SetValue(sheet, i + 45, 15, listExport[i].Date13);
if (i == 0)
{
SetValue(sheet, 2, 15, para.StartTime.Value.AddDays(12).ToString("M月d日"));
}
}
if (count > 13)
{
SetValue(sheet, i + 45, 16, listExport[i].Date14);
if (i == 0)
{
SetValue(sheet, 2, 16, para.StartTime.Value.AddDays(13).ToString("M月d日"));
}
}
if (count > 14)
{
SetValue(sheet, i + 45, 17, listExport[i].Date15);
if (i == 0)
{
SetValue(sheet, 2, 17, para.StartTime.Value.AddDays(14).ToString("M月d日"));
}
}
SetValue(sheet, i + 45, 3 + count, listExport[i].Total);
}
}
}
return(readworkbook);
}
/// <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工作表
NPOI.SS.UserModel.ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.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)
{
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
NPOI.SS.UserModel.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)
{
NPOI.SS.UserModel.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;
}
}
