/// <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); } }