using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using PT.BLL; using PT.Common; using PT.Model; using PT.Web.Mvc.App_Start; namespace PT.Web.Mvc.Controllers { [LoginAuthorizationFilter] public class FileManageController : BaseController<FileManage, FileManage> { FileManageBll bll = new FileManageBll(); private SessionHelper sessionHelper; private readonly IHostingEnvironment _hostingEnvironment; public FileManageController(IHostingEnvironment hostingEnvironment) { _hostingEnvironment = hostingEnvironment; } public IActionResult Index() { return View(); } /// <summary> /// 上传文件 /// </summary> /// <param name="t"></param> /// <returns></returns> public IActionResult UploadFile(FileManage t) { ResponseResult result = new ResponseResult() { code = 0, msg = "成功", data = 0 }; try { sessionHelper = new SessionHelper(HttpContext.Session); UserList user = sessionHelper.GetUserInfoString(); string msg = string.Empty; //LoggerHelper._.Info("1"); HttpRequest request = Request; //string TargetID = request.Form["TargetID"]; //string TargetTable = request.Form["TargetTable"]; //string TargetField = request.Form["TargetField"]; //string Remarks = request.Form["Remarks"]; string webRootPath = _hostingEnvironment.WebRootPath; string contentRootPath = _hostingEnvironment.ContentRootPath; string uploadPath = contentRootPath + "\\FileManage\\" + t.TargetTable + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\";// Server.MapPath("~/FileManage/" + TargetTable + "/" + DateTime.Now.ToString("yyyyMMdd") + "/"); var fileCollection = request.Form.Files; // 判断是否有文件 if (fileCollection.Count > 0) { //LoggerHelper._.Info("3"); // 获取文件 var httpPostedFile = fileCollection[0]; string fileExtension = Path.GetExtension(httpPostedFile.FileName);// 文件扩展名 //string fileName = Path.GetFileNameWithoutExtension(httpPostedFile.FileName); string fileName = Guid.NewGuid().ToString() + fileExtension;// 名称 string filePath = uploadPath + fileName;// 上传路径 //LoggerHelper._.Info("5:"+ filePath); // 如果目录不存在则要先创建 if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } // 保存新的文件 while (System.IO.File.Exists(filePath)) { fileName = Guid.NewGuid().ToString() + fileExtension; filePath = uploadPath + fileName; } //httpPostedFile.SaveAs(filePath); using (FileStream fs = System.IO.File.Create(filePath)) { httpPostedFile.CopyTo(fs); fs.Flush(); } msg = "上传成功"; t.FileName = httpPostedFile.FileName; t.FileGuidName = fileName; t.FileSize = httpPostedFile.Length; t.FilePath = filePath; //fileManage.TargetId = TargetID; //fileManage.TargetTable = TargetTable; //fileManage.TargetField = TargetField; //fileManage.Remark = Remarks; t.IsDelete = false; t.CreatBy = user.Id + "|" + user.UserID + "|" + user.UserName; t.CreatDate = DateTime.Now; result.msg = bll.InsertableIgnoreColumns(t).ToString(); result.data = t; } } catch (Exception ex) { result.code = -1; result.msg = ex.Message; } var res = new JsonResult(result); return res; } /// <summary> /// 上传文件 不存文件管理表 /// </summary> /// <param name="t"></param> /// <returns></returns> public IActionResult UploadFileNotSaveData(string TargetTable) { ResponseResult result = new ResponseResult() { code = 0, msg = "成功", data = 0 }; try { FileManage fileManage = new FileManage(); HttpRequest request = Request; string webRootPath = _hostingEnvironment.WebRootPath; string contentRootPath = _hostingEnvironment.ContentRootPath; string uploadPath = contentRootPath + "\\FileManage\\" + TargetTable + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\";// Server.MapPath("~/FileManage/" + TargetTable + "/" + DateTime.Now.ToString("yyyyMMdd") + "/"); var fileCollection = request.Form.Files; // 判断是否有文件 if (fileCollection.Count > 0) { //LoggerHelper._.Info("3"); // 获取文件 var httpPostedFile = fileCollection[0]; string fileExtension = Path.GetExtension(httpPostedFile.FileName);// 文件扩展名 //string fileName = Path.GetFileNameWithoutExtension(httpPostedFile.FileName); string fileName = Guid.NewGuid().ToString() + fileExtension;// 名称 string filePath = uploadPath + fileName;// 上传路径 //LoggerHelper._.Info("5:"+ filePath); // 如果目录不存在则要先创建 if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } // 如果文件名已存在则生成新的文件名 while (System.IO.File.Exists(filePath)) { fileName = Guid.NewGuid().ToString() + fileExtension; filePath = uploadPath + fileName; } //保存 using (FileStream fs = System.IO.File.Create(filePath)) { httpPostedFile.CopyTo(fs); fs.Flush(); } fileManage.FileName = httpPostedFile.FileName; fileManage.FileGuidName = fileName; fileManage.FileSize = httpPostedFile.Length; fileManage.FilePath = filePath; fileManage.TargetId = ""; fileManage.TargetTable = TargetTable; fileManage.TargetField = ""; fileManage.Remark = ""; fileManage.IsDelete = false; //fileManage.CreatBy = user.Id + "|" + user.UserID + "|" + user.UserName; fileManage.CreatDate = DateTime.Now; result.data = fileManage; } } catch (Exception ex) { result.code = -1; result.msg = ex.Message; } var res = new JsonResult(result); return res; } /// <summary> /// 导出 excel /// </summary> /// <returns></returns> public IActionResult ExportExcel2() { ResponseResult result = new ResponseResult() { code = 0, msg = "成功", data = 0 }; try { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn() { ColumnName = "AA" }); dt.Columns.Add(new DataColumn() { ColumnName = "BB" }); DataRow dr = dt.NewRow(); dr[0] = "11"; dr[1] = "22"; dt.Rows.Add(dr); //string[] fileArr = file.Split(new char[] { '.' }); string file = "测试" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; IWorkbook workbook; string fileExt = Path.GetExtension(file).ToLower(); if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return null; } ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName); //设置列宽 //sheet.SetColumnWidth(0, 15 * 256); //sheet.SetColumnWidth(1, 15 * 256); //sheet.SetColumnWidth(2, 15 * 256); //sheet.SetColumnWidth(3, 15 * 256); //sheet.SetColumnWidth(4, 15 * 256); //sheet.SetColumnWidth(5, 25 * 256); //sheet.SetColumnWidth(6, 25 * 256); //sheet.SetColumnWidth(7, 30 * 256); //sheet.SetColumnWidth(8, 15 * 256); //sheet.SetColumnWidth(9, 15 * 256); //sheet.SetColumnWidth(10, 15 * 256); //sheet.SetColumnWidth(12, 15 * 256); //for (int i = 0; i < dt.Rows.Count; i++) //{ // sheet.SetColumnWidth(i, 20 * 256); //} //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); ICellStyle style = workbook.CreateCellStyle(); ICellStyle style2 = workbook.CreateCellStyle(); string val = ""; //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); val = dt.Rows[i][j].ToString(); cell.SetCellValue(val); } } //设置行高 //HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //IRow row2 = sheet.GetRow(2); //row2.HeightInPoints = 120; //row2.GetCell(2).CellStyle = cellStyle; //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream, true); var buf = stream.ToArray(); string webRootPath = _hostingEnvironment.WebRootPath + "\\Downloads\\"; string Route = webRootPath;// @"C:\Downloads\"; //如找不到文件夹则创建 if (!Directory.Exists(Route)) { Directory.CreateDirectory(Route); } //保存为Excel文件 using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); fs.Close(); fs.Dispose(); result.data = file; } //xls //return File(stream, "application/application/vnd.ms-excel", $"{DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")}"); //xlsx /* * * 前端调用 $.ajax({ type: "Get", url: "/FileManage/ExportExcel2", async: true, data: {}, success: function (data) { window.open('/Downloads/' + data.data, '_blank'); } }); */ } catch (Exception ex) { //LoggerHelper._.Error(ex.ToString()); } var res = new JsonResult(result); return res; } /// <summary> /// 导出 excel /// </summary> /// <returns></returns> [HttpGet] public FileStreamResult ExportExcel() { try { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn() { ColumnName = "AA" }); dt.Columns.Add(new DataColumn() { ColumnName = "BB" }); DataRow dr = dt.NewRow(); dr[0] = "11"; dr[1] = "22"; dt.Rows.Add(dr); //string[] fileArr = file.Split(new char[] { '.' }); string file = "测试" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; IWorkbook workbook; string fileExt = Path.GetExtension(file).ToLower(); if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return null; } ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName); //设置列宽 //sheet.SetColumnWidth(0, 15 * 256); //sheet.SetColumnWidth(1, 15 * 256); //sheet.SetColumnWidth(2, 15 * 256); //sheet.SetColumnWidth(3, 15 * 256); //sheet.SetColumnWidth(4, 15 * 256); //sheet.SetColumnWidth(5, 25 * 256); //sheet.SetColumnWidth(6, 25 * 256); //sheet.SetColumnWidth(7, 30 * 256); //sheet.SetColumnWidth(8, 15 * 256); //sheet.SetColumnWidth(9, 15 * 256); //sheet.SetColumnWidth(10, 15 * 256); //sheet.SetColumnWidth(12, 15 * 256); //for (int i = 0; i < dt.Rows.Count; i++) //{ // sheet.SetColumnWidth(i, 20 * 256); //} //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); ICellStyle style = workbook.CreateCellStyle(); ICellStyle style2 = workbook.CreateCellStyle(); string val = ""; //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); val = dt.Rows[i][j].ToString(); cell.SetCellValue(val); } } //设置行高 //HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //IRow row2 = sheet.GetRow(2); //row2.HeightInPoints = 120; //row2.GetCell(2).CellStyle = cellStyle; //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream, true); var buf = stream.ToArray(); string webRootPath = _hostingEnvironment.WebRootPath + "\\Downloads\\"; string Route = webRootPath;// @"C:\Downloads\"; //如找不到文件夹则创建 if (!Directory.Exists(Route)) { Directory.CreateDirectory(Route); } //保存为Excel文件 using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); fs.Close(); fs.Dispose(); } //xls //return File(stream, "application/application/vnd.ms-excel", $"{DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")}"); //xlsx return File(stream, "application/vnd.ms-excel", Route + file); } catch (Exception ex) { //LoggerHelper._.Error(ex.ToString()); } return null; } } }
标签:sheet,string,SetColumnWidth,FileManageController,new,dt,256 From: https://www.cnblogs.com/zyx321/p/17505237.html