/// <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);
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);
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
/// <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);
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);
}
public static HSSFEvaluationWorkbook Create(NPOI.SS.UserModel.IWorkbook book)
{
if (book == null)
{
return(null);
}
return(new HSSFEvaluationWorkbook((HSSFWorkbook)book));
}
public NPOIWorksheet(NPOI.SS.UserModel.IWorkbook npoiWorkbook, ISheet npoiWorksheet,
IWorkbook workbook, DataTable table, String sheetName,
Int32 startRow = 1, Int32 startColumn = 1)
{
_wb = npoiWorkbook;
_ws = npoiWorksheet;
Workbook = workbook;
Table = table;
Name = sheetName;
StartRow = startRow;
StartColumn = startColumn;
}
/// <summary>
/// Get or create IWorkbook from file name.
/// </summary>
/// <param name="fileName">Full workbook file path to get or create.</param>
public NPOIWorkbook(IExcel excel)
{
try
{
Excel = excel;
_wb = GetNPOIWorkbook(Excel.FileName);
}
catch (Exception)
{
throw;
}
}
/// <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);
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
/// <summary>
/// open excel workbook(工作簿)
/// </summary>
/// <param name="path">文件路径</param>
/// <returns></returns>
public static IWorkbook OpenWorkbook(string path)
{
FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Write);
NPOI.SS.UserModel.IWorkbook workbook = WorkbookFactory.Create(fileStream);
//var ext = System.IO.Path.GetExtension(path);
//if (ext == ".xls")
// workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
//else if (ext == ".xlsx")
// workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream);
//else
//{ }
return(workbook);
}
/// <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);
}
public NPOIWorkbook(NModel.IWorkbook workbook)
{
_workbook = workbook;
}
public void Export(Stream ms, DataTable dt, string postfix)
{
//var fileName = m_Handler.GetFileName();
NPOI.SS.UserModel.IWorkbook book = null;
if (postfix == ".xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (postfix == ".xlsx")
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名");
}
m_Handler.Encode(ref dt);
//WriteExcel(ref book, dt);
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle cellStyle = book.CreateCellStyle();
var font = book.CreateFont();
font.FontHeightInPoints = 14;
font.IsBold = true;
cellStyle.SetFont(font);
int index = 0;
//string caption = m_Handler.GetCaption();
//if (string.IsNullOrEmpty(caption))
//{
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.CellStyle = cellStyle;
cell.SetCellValue(item.Caption);
index++;
}
//}
//else
//{
// NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
// cell.SetCellType(NPOI.SS.UserModel.CellType.String);
// cell.CellStyle = cellStyle;
// cell.SetCellValue(caption);
//}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
//// 写入
//MemoryStream ms = new MemoryStream();
book.Write(ms);
book = null;
//using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
//ms.Close();
//ms.Dispose();
}
/// <summary>
/// Читаем файл и заполняем listTraveller данными из каждой строки
/// </summary>
/// <returns>
/// </returns>
public static async Task <List <Traveller> > GetListTravellersFromFileXLSAsync()
{
List <Traveller> listTraveller = new List <Traveller>();
Console.WriteLine("Путь к файлу: ");
string filenameRead = Console.ReadLine().Replace("\"", "");
using (FileStream fs = new FileStream(filenameRead, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
NPOI.SS.UserModel.IWorkbook wb = null;
NPOI.SS.UserModel.ISheet sh = null;
try
{
wb = new XSSFWorkbook(fs);
sh = (XSSFSheet)wb.GetSheetAt(0);
}
catch
{
Console.WriteLine("Can't open file as a Workbook");
return(null);
}
//первая строка с данными в текущем файле МВД 23
for (int row = 24; row <= sh.LastRowNum; row++)
{
//Получаем фамилию из файла
string family = sh.GetRow(row).GetCell(1).StringCellValue;
//Получаем имя с отчеством из файла
string[] nameAndPatronymic = sh.GetRow(row).GetCell(2).StringCellValue.Split(' ');
//Получаем имя из массива nameAndPatronymic
string name = nameAndPatronymic[0];
//Получаем отчество из массива nameAndPatronymic //на случай если кто то без отчества
string patronymic = nameAndPatronymic.Length < 3 ? nameAndPatronymic[1] : nameAndPatronymic[1] + " " + nameAndPatronymic[2];
//Получаем дату рождения из файла
string birthDate = sh.GetRow(row).GetCell(3).NumericCellValue.ToString();
//в файле источнике дата представлена строкой 7 или 8 символов, вставляем точки в зависимости от одной или второй ситуации
string resBirth = birthDate.Length > 7 ? birthDate.Insert(2, ".").Insert(5, ".") : birthDate.Insert(1, ".").Insert(4, ".");
//Получаем Код Мед организации
string codeMO = await GetCodeMOFromFOMSAsync(family, name, patronymic, resBirth);
Traveller traveller = new Traveller()
{
//название полей идут в файл, названиями столбцов. поэтому на кириллице
//присаиваем фамилию
Фамилия = family,
//присваиваем имя
Имя = name,
//присваиваем отчество
Отчество = patronymic,
//присваиваем ДР
ДатаРождения = birthDate,
//Присваиваем НР(стоблец 8)
НР = sh.GetRow(row).GetCell(8).CellType == NPOI.SS.UserModel.CellType.Numeric ? sh.GetRow(row).GetCell(8).NumericCellValue.ToString() : sh.GetRow(row).GetCell(8).StringCellValue,
//приваиваем код медорганизации
КодМедОрганизации = String.IsNullOrEmpty(codeMO) ? "0" : codeMO
};
//добавляем заполненный объект в список
listTraveller.Add(traveller);
Console.WriteLine(traveller.Фамилия + " " + traveller.КодМедОрганизации);
}
}
return(listTraveller);
}
/// <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>
/// 初始化一个<see cref="NpoiWorkbook"/>类型的实例
/// </summary>
/// <param name="format">Excel格式类型</param>
public NpoiWorkbook(ExcelFormat format) : base()
{
_workbook = CreateWorkbook(format);
}
public string ExportAccountTemplate(SaveFileDialog sflg, DataTable dt)
{
string message = string.Empty;
try
{
string filename = sflg.FileName;
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
//整体样式
ICellStyle style = book.CreateCellStyle();
style.FillPattern = FillPattern.NoFill;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
style.SetFont(font);
//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;
//一般样式
ICellStyle style_center = book.CreateCellStyle();
style_center.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style_center.VerticalAlignment = VerticalAlignment.Center;
style_center.WrapText = true;
//style_center.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//一般样式
ICellStyle style_left = book.CreateCellStyle();
style_left.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
style_left.WrapText = true;
//style_left.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("清算信息汇总");
sheet.DefaultRowHeight = 20;
for (int columnNum = 0; columnNum < 11; columnNum++)
{
sheet.SetDefaultColumnStyle(columnNum, style);
}
int rowIndex = 0;
#region 添加表头
NPOI.SS.UserModel.IRow rowName = sheet.CreateRow(rowIndex);
rowName.CreateCell(0, CellType.String).SetCellValue("地区");
rowName.CreateCell(1, CellType.String).SetCellValue("序号");
rowName.CreateCell(2, CellType.String).SetCellValue("车辆生产企业");
rowName.CreateCell(3, CellType.String).SetCellValue("车辆型号");
rowName.CreateCell(4, CellType.Numeric).SetCellValue("企业申报推广数");
rowName.CreateCell(5, CellType.Numeric).SetCellValue("企业申请补助标准");
rowName.CreateCell(6, CellType.Numeric).SetCellValue("企业申请清算资金");
rowName.CreateCell(7, CellType.Numeric).SetCellValue("专家组核定的推广数");
rowName.CreateCell(8, CellType.Numeric).SetCellValue("专家组核定的补助标准");
rowName.CreateCell(9, CellType.Numeric).SetCellValue("应清算补助资金");
rowName.CreateCell(10, CellType.String).SetCellValue("核减原因");
rowIndex++;
#endregion
#region 添加数据
int dealerNum = 0;
//第一行总计
var countENTNum = (from DataRow row in dt.Rows select new { ent_num = row["ENT_NUM"] }).Sum(a => Convert.ToDecimal(a.ent_num));
var countENTMoney = (from DataRow row in dt.Rows select new { ent_money = row["SQBZBZ"] }).Sum(a => Convert.ToDecimal(a.ent_money));
var countENTCount = (from DataRow row in dt.Rows select new { ent_count = row["ENT_COUNT"] }).Sum(a => Convert.ToDecimal(a.ent_count));
var countAPPNum = (from DataRow row in dt.Rows select new { app_num = row["APP_NUM"] }).Sum(a => Convert.ToDecimal(a.app_num));
var countAPPMoney = (from DataRow row in dt.Rows select new { app_money = row["APP_MONEY"] }).Sum(a => Convert.ToDecimal(a.app_money));
var countAPPCount = (from DataRow row in dt.Rows select new { app_count = row["APP_COUNT"] }).Sum(a => Convert.ToDecimal(a.app_count));
IRow rowTotal = sheet.CreateRow(rowIndex);
ICell cellTotal = rowTotal.CreateCell(0);
cellTotal.SetCellValue("总计");
cellTotal.CellStyle = style_center;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3));
rowTotal.CreateCell(4, CellType.String).SetCellValue(countENTNum.ToString());
rowTotal.CreateCell(5, CellType.String).SetCellValue(countENTMoney.ToString());
rowTotal.CreateCell(6, CellType.String).SetCellValue(countENTCount.ToString());
rowTotal.CreateCell(7, CellType.String).SetCellValue(countAPPNum.ToString());
rowTotal.CreateCell(8, CellType.String).SetCellValue(countAPPMoney.ToString());
rowTotal.CreateCell(9, CellType.String).SetCellValue(countAPPCount.ToString());
rowIndex++;
#region 循环省份写入
var ProvinceList = dt.AsEnumerable().Select(d => d.Field <string>("DQ")).Distinct().ToList();
for (int i = 0; i < ProvinceList.Count(); i++)
{
dealerNum = 1;
//地区 省份
int DealerCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().Count();
int ClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLXH")).Count();
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + ClxhCount + DealerCount, 0, 0));
IRow rowSFTotal = sheet.CreateRow(rowIndex);
ICell cellSF = rowSFTotal.CreateCell(0);
cellSF.SetCellValue(ProvinceList[i]);
#region 省份合计
// IRow rowSFTotal = sheet.CreateRow(rowIndex);
var dvShengfen = dt.DefaultView;
dvShengfen.RowFilter = String.Format("DQ='{0}'", ProvinceList[i]);
var dtShengfen = dvShengfen.ToTable();
var sum_ENT_NUM = Convert.ToDecimal(dtShengfen.Compute("sum(ENT_NUM)", "TRUE"));
var sum_SQBZBZ = dtShengfen.Compute("sum(SQBZBZ)", "TRUE");
var sum_ENT_COUNT = dtShengfen.Compute("sum(ENT_COUNT)", "TRUE");
var sum_APP_NUM = dtShengfen.Compute("sum(APP_NUM)", "TRUE");
var sum_APP_MONEY = dtShengfen.Compute("sum(APP_MONEY)", "TRUE");
var sum_APP_COUNT = dtShengfen.Compute("sum(APP_COUNT)", "TRUE");
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
ICell cellSFTotal = rowSFTotal.CreateCell(1);
cellSFTotal.SetCellValue("合计");
cellSFTotal.CellStyle = style_center;
rowSFTotal.CreateCell(4, CellType.String).SetCellValue(sum_ENT_NUM.ToString());
rowSFTotal.CreateCell(5, CellType.String).SetCellValue(sum_SQBZBZ.ToString());
rowSFTotal.CreateCell(6, CellType.String).SetCellValue(sum_ENT_COUNT.ToString());
rowSFTotal.CreateCell(7, CellType.String).SetCellValue(sum_APP_NUM.ToString());
rowSFTotal.CreateCell(8, CellType.String).SetCellValue(sum_APP_MONEY.ToString());
rowSFTotal.CreateCell(9, CellType.String).SetCellValue(sum_APP_COUNT.ToString());
rowIndex++;
#endregion
#region 照汽车生产企业写入
var DealerList = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().ToList();
for (int j = 0; j < DealerList.Count(); j++)
{
//企业序号
int DealerClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).Select(d => d.Field <string>("CLXH")).Count();
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 1, 1));
IRow rowDealerTotal = sheet.CreateRow(rowIndex);
ICell cellDealerNum = rowDealerTotal.CreateCell(1);
cellDealerNum.SetCellValue(dealerNum);
//企业名称
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 2, 2));
ICell cellDealer = rowDealerTotal.CreateCell(2);
cellDealer.SetCellValue(DealerList[j]);
//企业小计
var dvDealer = dt.DefaultView;
dvDealer.RowFilter = String.Format("CLSCQY='{0}' AND DQ = '{1}'", DealerList[j], ProvinceList[i]);
var dtDealer = dvDealer.ToTable();
var dealer_ENT_NUM = dtDealer.Compute("sum(ENT_NUM)", "TRUE");
var dealer_SQBZBZ = dtDealer.Compute("sum(SQBZBZ)", "TRUE");
var dealer_ENT_COUNT = dtDealer.Compute("sum(ENT_COUNT)", "TRUE");
var dealer_APP_NUM = dtDealer.Compute("sum(APP_NUM)", "TRUE");
var dealer_APP_MONEY = dtDealer.Compute("sum(APP_MONEY)", "TRUE");
var dealer_APP_COUNT = dtDealer.Compute("sum(APP_COUNT)", "TRUE");
rowDealerTotal.CreateCell(3, CellType.String).SetCellValue("小计");
rowDealerTotal.CreateCell(4, CellType.String).SetCellValue(dealer_ENT_NUM.ToString());
rowDealerTotal.CreateCell(5, CellType.String).SetCellValue(dealer_SQBZBZ.ToString());
rowDealerTotal.CreateCell(6, CellType.String).SetCellValue(dealer_ENT_COUNT.ToString());
rowDealerTotal.CreateCell(7, CellType.String).SetCellValue(dealer_APP_NUM.ToString());
rowDealerTotal.CreateCell(8, CellType.String).SetCellValue(dealer_APP_MONEY.ToString());
rowDealerTotal.CreateCell(9, CellType.String).SetCellValue(dealer_APP_COUNT.ToString());
rowIndex++;
//按照车辆型号写入
var detailData = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).CopyToDataTable();
for (int k = 0; k < detailData.Rows.Count; k++)
{
IRow rowDealer = sheet.CreateRow(rowIndex);
rowDealer.CreateCell(3, CellType.String).SetCellValue(detailData.Rows[k]["CLXH"].ToString());
rowDealer.CreateCell(4, CellType.String).SetCellValue(detailData.Rows[k]["ENT_NUM"].ToString());
rowDealer.CreateCell(5, CellType.String).SetCellValue(detailData.Rows[k]["SQBZBZ"].ToString());
rowDealer.CreateCell(6, CellType.String).SetCellValue(detailData.Rows[k]["ENT_COUNT"].ToString());
rowDealer.CreateCell(7, CellType.String).SetCellValue(detailData.Rows[k]["APP_NUM"].ToString());
rowDealer.CreateCell(8, CellType.String).SetCellValue(detailData.Rows[k]["APP_MONEY"].ToString());
rowDealer.CreateCell(9, CellType.String).SetCellValue(detailData.Rows[k]["APP_COUNT"].ToString());
rowDealer.CreateCell(10, CellType.String).SetCellValue(detailData.Rows[k]["APP_RESULT"].ToString());
rowDealer.Cells[7].CellStyle = style_left;
rowIndex++;
}
dealerNum++;
}
#endregion
}
for (int colNum = 0; colNum <= 10; colNum++)
{
int columnWidth = sheet.GetColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(colNum);
if (currentCell != null)
{
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length + 1)
{
columnWidth = length + 1 > 254 ? 255 : length + 1;
}
currentRow.HeightInPoints = 20;
}
}
sheet.SetColumnWidth(colNum, columnWidth * 256);
}
sheet.ProtectSheet("1");
#endregion
#endregion
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
catch (Exception ex)
{
message = ex.Message;
}
return(message);
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="exceltype">是否模板</param>
/// <param name="FileName">文件名称</param>
public void DataTableToExcelTemplet(DataTable dtSource, string ExportType, string FileName = "")
{
string TempletFileName = "";//模板文件名称
FileStream file = null;
NPOI.SS.UserModel.IWorkbook workbook = null;
SaveFileDialog savefd = new SaveFileDialog();
//模板文件
TempletFileName = Application.StartupPath + "\\JNMLTemp.xls";
file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
if (FileName != "")
{
savefd.FileName = FileName + ".xls";
}
savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
if (savefd.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("Sheet1");
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 1;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.GetRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.GetCell(column.Ordinal);
string drValue = row[column].ToString();
if (column.ColumnName == "numxh")
{
newCell.SetCellValue(rowIndex.ToString());
continue;
}
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
if (file != null)
{
file.Dispose();
file.Close();
}
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="FileName">文件名称</param>
/// <param name="exceltype">是否模板</param>
public void DataTableToExcel(DataTable dtSource, string ExportType, string strHeaderText, string FileName = "")
{
NPOI.SS.UserModel.IWorkbook workbook = null;
SaveFileDialog savefd = new SaveFileDialog();
if (FileName != "")
{
savefd.FileName = FileName + ".xls";
}
savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
if (savefd.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ExportType);
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = SetExcelColWidth(dtSource, ExportType);
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet(ExportType);
}
#region 表头及样式
{
if (strHeaderText != "")
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 18;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
rowIndex = 1;
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 11;
font.Boldweight = 700;
font.IsBold = true;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex++;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 功能： 导出到Excel
/// </summary>
/// <param name="workbook"></param>
/// <param name="title">表头标题</param>
/// <param name="dt">导出的数据</param>
/// <param name="sheetname">sheet表格的名字</param>
public static bool CreateExcel(NPOI.SS.UserModel.IWorkbook workbook, List <string> title, List <int> iColumns, DataTable dt, string sheetname, string sheetTitle, string subTitle)
{
if (iColumns.Count() != title.Count() || iColumns.Max() >= dt.Columns.Count)
{
return(false);
}
int rowRecord = 0;
ISheet sheet = workbook.CreateSheet(sheetname);
#region 定义Sheet标题及子标题
IRow titleRow = sheet.CreateRow(0);
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue(sheetTitle);
//ICellStyle titleStyle = workbook.CreateCellStyle();
//titleStyle.Alignment = HorizontalAlignment.Center;
//IFont titleFont = workbook.CreateFont();
//titleFont.Boldweight = 200;
//titleFont.FontHeight = 16 * 16;
//titleStyle.SetFont(titleFont);
//titleCell.CellStyle = titleStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, title.Count));
IRow subTitleRow = sheet.CreateRow(1);
ICell subTitleCell = subTitleRow.CreateCell(0);
subTitleCell.SetCellValue(subTitle);
//ICellStyle subTitleStyle = workbook.CreateCellStyle();
//subTitleStyle.Alignment = HorizontalAlignment.Center;
//IFont subTitleFont = workbook.CreateFont();
//subTitleFont.Boldweight = 100;
//subTitleFont.FontHeight = 15 * 15;
//subTitleFont.Color = HSSFColor.Grey80Percent.Index;
//subTitleStyle.SetFont(subTitleFont);
//subTitleCell.CellStyle = subTitleStyle;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, title.Count));
#endregion
#region 初始化表格标题
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillBackgroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillPattern = FillPattern.Squares;
IFont headerFont = workbook.CreateFont();
//headerFont.Boldweight = 200;
//headerFont.FontHeight = 15 * 15;
headerFont.Color = HSSFColor.White.Index;
headerStyle.SetFont(headerFont);
ICellStyle valueStyle = workbook.CreateCellStyle();
//valueStyle.Alignment = HorizontalAlignment.Center;
valueStyle.BorderBottom = BorderStyle.Thin;
valueStyle.BorderLeft = BorderStyle.Thin;
valueStyle.BorderRight = BorderStyle.Thin;
valueStyle.BorderTop = BorderStyle.Thin;
//IFont valueFont = workbook.CreateFont();
//valueFont.FontHeight = 15 * 15;
//valueStyle.SetFont(valueFont);
int rowIndex = 3;
IRow headerRow = sheet.CreateRow(rowIndex);
for (int j = 0; j <= title.Count; j++)
{
ICell headerCell = headerRow.CreateCell(j);
if (j == 0)
{
headerCell.SetCellValue("序号");
}
else
{
headerCell.SetCellValue(title[j - 1]);
}
headerCell.CellStyle = headerStyle;
}
#endregion
if (dt.Rows.Count > 0)
{
#region 数据写入
rowIndex += 1;
for (int k = rowRecord; k < dt.Rows.Count; k++)
{
rowRecord = k;
if (rowIndex > 65535)
{
break;
}
IRow dtRow = sheet.CreateRow(rowIndex);
DataRow dr = dt.Rows[k];
for (int l = 0; l <= dt.Columns.Count; l++)
{
for (int cl = 0; cl <= iColumns.Count(); cl++)
{
ICell valueCell = dtRow.CreateCell(cl);
if (cl == 0)
{
valueCell.SetCellValue(k + 1);
}
else
{
string columnValue = dr[iColumns[cl - 1]].ConvertTo <string>();
columnValue = columnValue.IsNullOrEmpty() || dt.Columns[iColumns[cl - 1]].DataType.Name != "DateTime" ? columnValue : DateTime.Parse(columnValue).ToString("yyyy-MM-dd");
valueCell.SetCellValue(columnValue);
}
valueCell.CellStyle = valueStyle;
}
}
rowIndex += 1;
}
#endregion
}
else
{
rowIndex += 1;
IRow dtRow = sheet.CreateRow(rowIndex);
for (int colIndex = 0; colIndex <= title.Count; colIndex++)
{
ICell valueCell = dtRow.CreateCell(colIndex);
if (colIndex == 0)
{
valueCell.SetCellValue("没有数据");
}
else
{
valueCell.SetCellValue("");
}
valueCell.CellStyle = valueStyle;
}
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, title.Count));
}
#region 自动伸展列宽
for (int colIndex = 0; colIndex <= title.Count; colIndex++)
{
sheet.AutoSizeColumn(colIndex);
}
#endregion
//}
return(true);
}
/// <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;
}
}
public NPOI.SS.UserModel.IWorkbook GetExportData(int companyid, NPOI.SS.UserModel.IWorkbook book, string[] CellArray, string templetFileName, bool IsSenior)
{
var company = _customerCompanyRepository.TableNoTracking.Where(o => o.Id == companyid).FirstOrDefault();
using (FileStream file = new FileStream(templetFileName, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet1 = hssfworkbook.GetSheet("Sheet1");
var query = (from oc in this.NoTrackingQuery.Where(o => o.CustomsAuthenticationID == company.CustomsAuthenticationID)
join c in _clausesRepository.TableNoTracking on oc.Id equals c.OutlineClassID
join i in _itemRepository.TableNoTracking on c.Id equals i.ClausesID
join st in _scoreTaskRepository.TableNoTracking on i.Id equals st.ItemID into temp
from tp in temp.DefaultIfEmpty()
join fi in _fineItemRepository.TableNoTracking on i.Id equals fi.ItemID
join fr in _fileRequireRepository.TableNoTracking on fi.Id equals fr.FineItemID into temp1
from tp1 in temp1.DefaultIfEmpty()
select new
{
oc.OutlineClassName, //类名称
c.ClausesName, // 条名称
ItemID = i.Id, //项ID
i.ItemName, //项名称
tp.Score, //评分
fi.FineItemName, //细项名称
tp1.Id,
tp1.SuggestFileName, //建议文件名
scorecfg = i.ScoreConfigure.Select(o => new
{
o.ScoreValue, //评分项
}).ToList()
}
);
//排序项
var itemlist = (from a in query
group a by new
{
a.ItemName,
a.ItemID
}
into g
select new
{
g.Key.ItemName,
g.Key.ItemID,
list = g.Select(o => new {
o.SuggestFileName,
o.Score
}).ToList()
}).OrderBy(o => o.ItemID).ToList();
Dictionary <int, FileNameScore> value = new Dictionary <int, FileNameScore>();
var n = 0;
//获取该项的文件要求、分数
foreach (var item in itemlist)
{
var str = "";
for (int i = 0; i < item.list.Count(); i++)
{
if (!str.Contains(item.list[i].SuggestFileName))
{
if (i == item.list.Count() - 1)
{
str = str + item.list[i].SuggestFileName;
}
else
{
str = str + item.list[i].SuggestFileName + Environment.NewLine;
}
}
if (i == item.list.Count() - 1)
{
value.Add(n, new FileNameScore {
SuggestFileName = str, Score = item.list[i].Score
});
}
}
n++;
}
var Score = 100;
//给每一行标记过的数据设置数据
for (int i = 0; i < CellArray.Length; i++)
{
var curcell = CellArray[i];
var b = value[i];
if (IsSenior ? curcell == "29" : curcell == "24")
{
IRow row = sheet1.GetRow(Convert.ToInt32(curcell));
IRow row1 = sheet1.GetRow(Convert.ToInt32(curcell) + 1);
row.GetCell(3).SetCellValue(b.SuggestFileName);
if (b.Score.HasValue)
{
if (b.Score.Value == ScoreLevel.ReachStandard)
{
row1.GetCell(4).SetCellValue("0");
}
if (b.Score.Value == ScoreLevel.Substandard)
{
row1.GetCell(5).SetCellValue("-2");
Score += -2;
}
if (b.Score.Value == ScoreLevel.NotApplicable)
{
row1.GetCell(7).SetCellValue("-");
}
}
}
else if (IsSenior ? curcell == "58" : curcell == "53")
{
IRow row = sheet1.GetRow(Convert.ToInt32(curcell));
row.GetCell(3).SetCellValue(b.SuggestFileName);
if (b.Score.HasValue)
{
if (b.Score.Value == ScoreLevel.Conform)
{
row.GetCell(4).SetCellValue("2");
Score += 2;
}
if (b.Score.Value == ScoreLevel.NotApplicableV2)
{
row.GetCell(6).SetCellValue("0");
}
}
}
else
{
IRow row = sheet1.GetRow(Convert.ToInt32(curcell));
row.GetCell(3).SetCellValue(b.SuggestFileName);
if (b.Score.HasValue)
{
if (b.Score.Value == ScoreLevel.ReachStandard)
{
row.GetCell(4).SetCellValue("0");
}
if (IsSenior ? i > 15 && i < 24 : i > 11 && i < 20)
{
if (b.Score.Value == ScoreLevel.Substandard)
{
row.GetCell(5).SetCellValue("-2");
Score += -2;
}
}
else
{
if (b.Score.Value == ScoreLevel.PartiallyCompliant)
{
row.GetCell(5).SetCellValue("-1");
Score += -1;
}
if (b.Score.Value == ScoreLevel.Substandard)
{
row.GetCell(6).SetCellValue("-2");
Score += -2;
}
}
if (b.Score.Value == ScoreLevel.NotApplicable)
{
row.GetCell(7).SetCellValue("-");
}
}
}
if (i == CellArray.Length - 1)
{
IRow row = sheet1.GetRow(Convert.ToInt32(curcell) + 1);
if (Score >= 95)
{
row.GetCell(1).SetCellValue("认证通过，认证分数为：" + Score);
}
else
{
row.GetCell(1).SetCellValue("认证不通过，认证分数为：" + Score);
}
}
}
return(hssfworkbook);
}
}
