常用的导出Excle格式设置
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore"); worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells.Style.Font.Bold = true;//字体为粗体 worksheet.Cells.Style.Font.Name = "微软雅黑";//字体 worksheet.Cells.Style.Font.Size = 10;//字体大小 worksheet.Cells.Merge = true;//合并单元格 worksheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells.Style.WrapText = true;//自动换行 worksheet.Cells[countAll,5].Style.Fill.PatternType = ExcelFillStyle.Solid; //设置单元格背景色设置
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色
worksheet.Cells.Style.Font.Bold = true;//字体设置 worksheet.Cells.Style.Font.Color.SetColor(Color.Blue)//字体颜色设置
颜色对照表链接：https://blog.csdn.net/basycia/article/details/50449386
worksheet.Cells[5, 3].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address); //ExcelAddress(2, 3, 4, 3) 第2行第3列到第4行第三列数据相加 ，到第5行第3列
worksheet.Cells[countAll, 3].Style.Numberformat.Format = "#,##0";//保留整数格式
导出代码：
1、导出之前，先下载插件
2、创建控制器，添加导出代码
//用来获取路径相关
private IHostingEnvironment _hostingEnvironment; public SchemeController(IHostingEnvironment hostingEnvironment) { _hostingEnvironment = hostingEnvironment; } private Tuple<string, string> GetTuple(string ExcelName) { string sWebRootFolder = _hostingEnvironment.WebRootPath; string sFileName = $"{ExcelName}.xlsx"; return Tuple.Create(sWebRootFolder, sFileName); } //导出代码 public IActionResult Export() {
//获取DataTable 数据 int PrejectID = ConvertHelper.SafeRequest("project", 0, Request);//当前页码 int JZ_Type = ConvertHelper.SafeRequest("jztype", 0, Request);//当前页码 int AID = ConvertHelper.SafeRequest("TaskPeople", 0, Request);//当前页码 int RecordCount = 0; DataTable dtList = new SmartNet.Repository.Project_Scheme().UP_Project_Scheme_GetList(PrejectID, JZ_Type, AID, 1000, 1, out RecordCount);
string sWebRootFolder = GetTuple("安装站点需求配置列表").Item1; string sFileName = GetTuple("安装站点需求配置列表").Item2; FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));//创建一个FileInfo实例，创建路径 if (!System.IO.Directory.Exists(sWebRootFolder + sFileName))//文件是否存在如果存在删掉文件重新创建。 { file.Delete(); //删除服务器上的临时文件 } using (ExcelPackage package = new ExcelPackage(file))//创建excel { // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore"); int countAll = 0; //worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 //循环多少列的列宽设置
for (int i = 1; i < 4; i++) { worksheet.Column(i).Width = 30;//设置列宽 } //判断不为空就进入循环 if (dtList != null && dtList.Rows.Count > 0) { //添加值 for (int i = 0; i < dtList.Rows.Count; i++) { countAll++; DataRow item = dtList.Rows[i];//创建行 //添加头 worksheet.Cells[countAll, 1, countAll, 4].Style.Font.Bold = true;//字体为粗体 worksheet.Cells[countAll, 1, countAll, 4].Style.Font.Name = "微软雅黑";//字体 worksheet.Cells[countAll, 1, countAll, 4].Style.Font.Size = 10;//字体大小 worksheet.Cells[countAll, 1, countAll, 4].Merge = true;//合并单元格 worksheet.Cells[countAll, 1, countAll, 4].Style.Fill.PatternType = ExcelFillStyle.Solid;//背景色设置属性 worksheet.Cells[countAll, 1, countAll, 4].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色 worksheet.Cells[countAll, 1, countAll, 4].Value = item["SchemeName"].ToString();//赋值
//查询子表数据DataTable ds string strSql1 = "select * from [dbo].[Project_SchemeItem] where SchemeID=" + int.Parse(item["SchemeID"].ToString()); DataTable ds = SqlHelper.Query(strSql1);
//判断不为空进入
if (ds != null && ds.Rows.Count > 0) { //添加值 for (int b = 0; b < ds.Rows.Count; b++) { countAll++; DataRow item1 = ds.Rows[b];//创建行 worksheet.Cells[countAll, 1].Value = item1["ProductName"].ToString(); worksheet.Cells[countAll, 2].Value = item1["ProductIntro"].ToString(); worksheet.Cells[countAll, 2].Style.WrapText = true;//自动换行 worksheet.Cells[countAll, 3].Value = item1["Num"].ToString(); worksheet.Cells[countAll, 4].Value = item1["UnitName"].ToString(); } } countAll++; }
//设置样式 worksheet.Cells[1, 1, countAll, 4].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 worksheet.Cells[1, 1, countAll, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells[1,1, countAll,4].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells[1, 1, countAll, 4].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells[1, 1, countAll, 4].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框 worksheet.Cells[1, 1, countAll, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框 } package.Save();//保存 } return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "安装站点需求配置列表.xlsx");//导出excel表格 }
导出表如下,样式自己设置，常用的样式设置已在开始处！
3、导入代码
Html 代码： <div id="importBox" style="display:none"> <form id="myform" enctype="multipart/form-data" method="post" action="/ProductManage/OutBill/Import"> <input type="file" name="excelfile" /> <input type="submit" value="上传" /> </form> </div> function TaiZhangExport(obj) { document.getElementById('myform').action = "/ProductManage/OutBill/Import?ReachId=" + obj; layer.open({ type: 1, skin: 'layui-layer-rim', //加上边框 area: ['420px', '240px'], //宽高 content: $("#importBox").html() }); } 控制器代码： [HttpPost] public IActionResult Import(IFormFile excelfile) { string JSONItem = ConvertHelper.SafeRequest("ReachId","", Request); string[] JSOItem = JSONItem.Split(','); string sWebRootFolder = GetTuple("导入产品数量").Item1; string sFileName = GetTuple("导入产品数量").Item2; if (excelfile.Length == 0) { //financemanage/ledger/list return Content("请导入excel文件格式（.xlsx,xls)"); } FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); string[] FileType = excelfile.FileName.Split('.'); if (FileType[FileType.Length - 1] != "xlsx" && FileType[FileType.Length - 1] != "xls") { //financemanage/ledger/list return Content("你导如的文件格式不正确，请导入excel文件格式（.xlsx,xls)"); } if (!System.IO.Directory.Exists(sWebRootFolder + sFileName))//判断路径是否存在 { file.Delete(); //删除服务器上的临时文件 } try { //FileStream对象表示在磁盘或网络路径上指向文件的流。这个类提供了在文件中读写字节的方。 //Create：文件存在删除该文件，不存在创建新文件。 using (FileStream fs = new FileStream(file.ToString(), FileMode.Create)) { excelfile.CopyTo(fs); fs.Flush(); } using (ExcelPackage package = new ExcelPackage(file)) { StringBuilder sb = new StringBuilder(); ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; int rowCount = worksheet.Dimension.Rows; int ColCount = worksheet.Dimension.Columns; bool isTrue = false;
//判断数据类型等是否正确 for (int row = 2; row <= rowCount; row++) { for (int col = 1; col <= ColCount; col++) { if (col==4) { string num = worksheet.Cells[row, 4].Value.ToString(); if (GetNum(num)<=0) { sb.Append("第" + row + "行" + 4 + "列数据只能为整数！" + "\t"); isTrue = true; } } if (col == 5) { string num1 = worksheet.Cells[row, 5].Value.ToString(); if (GetNum1(num1) <= 0) { sb.Append("第" + row + "行" + 5 + "列数据只能数据类型！" + "\t"); isTrue = true; } } } sb.Append(Environment.NewLine); } if (isTrue == true) { return Content(sb.ToString()); } string str1 = " delete [dbo].[Product_BillItem] where BillId =" + int.Parse(JSOItem[0]); SqlHelper.Query(str1); SmartNet.Repository.Product_BillItem itemRep_Bill = new SmartNet.Repository.Product_BillItem(); decimal AllMoney = 0;
//数据正确进入循环导入 for (int row = 2; row <= rowCount; row++) { if (int.Parse(worksheet.Cells[row, 4].Value.ToString()) > 0) { SmartNet.Entity.Product_BillItem itemModel_CheckItem = new Entity.Product_BillItem(); itemModel_CheckItem.BillId = int.Parse(JSOItem[0]); itemModel_CheckItem.ReachId= int.Parse(JSOItem[1]); itemModel_CheckItem.IsAdd = 2; itemModel_CheckItem.ProductId = int.Parse(worksheet.Cells[row, 1].Value.ToString()); itemModel_CheckItem.ProductName = worksheet.Cells[row, 2].Value.ToString(); itemModel_CheckItem.ProductIntro = worksheet.Cells[row, 3].Value.ToString(); itemModel_CheckItem.Num = int.Parse(worksheet.Cells[row, 4].Value.ToString()); itemModel_CheckItem.Price = decimal.Parse(worksheet.Cells[row, 5].Value.ToString()); itemModel_CheckItem.AddTime = DateTime.Now; AllMoney += itemModel_CheckItem.Num * itemModel_CheckItem.Price; itemModel_CheckItem.UnitName = worksheet.Cells[row, 6].Value.ToString(); itemRep_Bill.Insert(itemModel_CheckItem); } } file.Delete();//删除刚创建的临时文件。 return Redirect("/Productmanage/OutBill/list"); } } catch (Exception ex) { return Content(ex.Message); } }
