ExcelPackage导入导出,命名空间一定要是EPPlus
1.引入EPPlus.dll,旧版的是OfficeOpenXml.dll,最好使用EPPlus
2.调用 string path = UploadExecl(batchUpload.BinaryExcel, "xlsx");,获取上传的xlsx路径
3. 下载Execl
3.1 如果是<a> 标签的连接,可以将方法直接写在 href上就能直接下载
<a href="/FangAn/DetailAuditOutPut/" target="_blank" style="color:#fff;"><el-button type="primary">导出Execl</el-button></a>
后台方法调用:
byte[] result = GetExcelByte(model);
返回值为 File();
return File(result, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", model.OrderName + ".xlsx");
3.2 如果是js异步操作,需要下载的话:
后台方法调用:
byte[] result = GetExcelByte(dt, modelReturn.errMessage);
string basestr = Convert.ToBase64String(result);
返回值为base64的字符串
return basestr;
而前台,在需要多加一步操作,可以直接下载:
//res.data 为异步返回值,就是basestr
window.location.href = "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + res.data;
/// <summary>
/// datatable导出
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public byte[] GetExcelByte(DataTable dt, string err)
{
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("候车亭批量导入");
workSheet.Cells[1, 1].Value = "媒体类型*";
workSheet.Cells[1, 2].Value = "类型子类*";
workSheet.Cells[1, 3].Value = "媒体位置*";
for (int i = 1; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
workSheet.Cells[i + 1, 1].Value = dr[0];
workSheet.Cells[i + 1, 2].Value = dr[1];
workSheet.Cells[i + 1, 3].Value = dr[2];
}
return package.GetAsByteArray();
}
}
/// <summary>
/// execl转成table
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable ExcelToTable(string path)
{
DataTable vTable = new DataTable();
FileInfo existingFile = new FileInfo(path);
try
{
FileInfo file = new FileInfo(path);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int vSheetCount = package.Workbook.Worksheets.Count;
//获取总Sheet页
int maxColumnNum = worksheet.Dimension.End.Column;//最大列
int minColumnNum = worksheet.Dimension.Start.Column;//最小列
int maxRowNum = worksheet.Dimension.End.Row;//最小行
int minRowNum = worksheet.Dimension.Start.Row;//最大行
DataColumn vC;
for (int j = 1; j <= maxColumnNum; j++)
{
vC = new DataColumn("A_" + j, typeof(string));
vTable.Columns.Add(vC);
}
for (int n = 1; n <= maxRowNum; n++)
{
DataRow vRow = vTable.NewRow();
for (int m = 1; m <= maxColumnNum; m++)
{
vRow[m - 1] = worksheet.Cells[n, m].Value;
}
vTable.Rows.Add(vRow);
}
}
}
catch (Exception vErr)
{
Console.WriteLine(vErr.Message);
}
return vTable;
}
/// <summary>
/// 把二进制流转成文件
/// </summary>
/// <param name="path">二进制流,类似(data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,)开头的字符串</param>
/// <param name="path">文件扩展名</param>
/// <returns></returns>
public string UploadExecl(string path, string extension)
{
string sPath = host.ContentRootPath + "\\BatchUpload";//保存的路径
if (!Directory.Exists(sPath))
{
Directory.CreateDirectory(sPath);
}
var regex = new Regex(@"data:(?<mime>[\w/\-\.]+);(?<encoding>\w+),(?<data>.*)", RegexOptions.Compiled);
var match = regex.Match(path);
var mimeType = match.Groups["mime"].Value;
var encodingCode = match.Groups["encoding"].Value;
var data = match.Groups["data"].Value;
byte[] targetFileByte = Convert.FromBase64String(data);
string[] mimeExtension = mimeType.Split('/');
string fileExtension = extension;
Random random = new Random();
//文件保存
string fileName = string.Format("{0:yyyyMMddHHmmss}{1}", DateTime.Now, random.Next());
string filePath = string.Format("{0}\\{1}.{2}", sPath, fileName, fileExtension);
FileStream file = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write);
file.Write(targetFileByte,0, targetFileByte.Length);
file.Close();
return filePath;
}
最新文章
- [Django]模型提高部分--聚合(group by)和条件表达式+数据库函数
- LCA
- .NET运用AJAX 总结及其实例
- JS原生方法实现瀑布流布局
- Iterator 迭代器(一)
- 中国地图 xaml Canvas
- CSS从大图中抠取小图完整教程(background-position应用)
- HTML解析引擎:Jumony
- Vijos1675 NOI2005 聪聪和可可 记忆化搜索
- ajax实现下拉列表联动
- tp5.1入口文件隐藏
- Docker中运行EOS FOR MAC
- JQuery each遍历A标签获取href 和 里面指定的值
- android 基础题
- [daily][dpdk] 内核模块(网卡驱动)无法卸载
- 叶亚明:合格CTO的六要素(转)
- Django商城项目笔记No.7用户部分-注册接口-判断用户名和手机号是否存在
- python的内置模块re模块方法详解以及使用
- android动手写控件系列——老猪叫你写相机
- linux的0号进程和1号进程
热门文章
- [swscaler @ ...] deprecated pixel format used, make sure you did set range correctly
- linux系统登陆过程
- 吴裕雄 Bootstrap 前端框架开发——Bootstrap 辅助类:将页面元素所包含的文本内容替换为背景图
- 单词「TJOI 2013」(AC自动机)
- 1_02_MSSQL课程_T_SQL语句入门
- 第2节 storm实时看板案例:12、实时看板综合案例代码完善;13、今日课程总结
- storm的JavaAPI运行报错
- updatexml()报错注入
- java实现在线预览 - -之poi实现word、excel、ppt转html
- leetcode347 Top K Frequent Elements