将数据库数据导出到服务器文件并下载(Excel)
服务器文件就在 C#实现从服务器下载文件 这篇文章中。
后端代码:
public async Task<Stream> Export(FileExportDto input)
{
var list = await _repositoryComponent.GetListAsync();
var CRPList = await _repositoryComponent.GetListAsync();
var proList = await _repositoryProduct.GetListAsync();
//ComponentCategoryTemplate,定义一个类,字段要和服务器中的文件(Excel)列对应。
var components = new List<ComponentTemplate>();
var component = new ComponentTemplate();
//有关联表,需要拼装数据。
foreach (var item in list)
{
component = new ComponentTemplate();
var crp = CRPList.Where(x => x.ComponentId == item.Id);
if (crp != null)
{
foreach (var ite in crp)
{
var pros = proList.FirstOrDefault(x => x.Id == ite.ProductionId);
component.Products = component.Products + pros.Name + $"{"["+pros.Code+"]"} " + Environment.NewLine;
}
}
component.Code = item.Code;
..., //主要是将查到的数据拼装成想要的数据
component.UseDate = item.UseDate != null ? Convert.ToDateTime(item.UseDate.ToString()).ToString("yyyy-MM-dd") : null;
componentCategories.Add(componentCategory);
}
/*若只有一张表,原模原样导出来。在这转换一下,传dtoList就行。
var dtoList = ObjectMapper.Map<List<EquipmentModel>, List<EquipmentModel>>(equipments);
*/
var stream = ExcelHelper.ExcelExportStream(componentCategories,input.TemplateKey,input.RowIndex);
return stream;
}
需要用到的工具类:
public static Stream ExcelExportStream<T>(List<T> dataList, string templateName, int rowIndex = 0, FileImageConfig imageConfig=null)
{
// 判断实体T 的结构与模板的结构是否相同
//获取文件模板
var templatePath = Path.Combine(Directory.GetCurrentDirectory(),
path2: $"import_templates\\import_templates_{templateName}.xls");
MemoryStream fileMemoryStream;
if (File.Exists(templatePath))
{
var fileStream = File.OpenRead(templatePath);
var sheet = ExcelHelper.GetWorkbookByStream(
fileStream,
$"import_templates_{templateName}.xls")
.GetSheetAt(0) // 获取工作表
.CheckColumnAccordTempleModel<T>(rowIndex) // 校验工作表
.WriteData(dataList, rowIndex); // 写入数据
if (imageConfig != null)
{
// 添加图片,还没测试。
var drawing = sheet.CreateDrawingPatriarch();
var pic = sheet.Workbook.AddPicture(imageConfig.ImageBytes, PictureType.PNG);
var anchor = new HSSFClientAnchor();
anchor.SetAnchor(
imageConfig.Col1,
imageConfig.Row1,
imageConfig.X1,
imageConfig.Y1,
imageConfig.Col2,
imageConfig.Row2,
imageConfig.X2,
imageConfig.Y2);
anchor.AnchorType = AnchorType.MoveAndResize;
drawing.CreatePicture(anchor, pic);
}
// 写入数据
fileMemoryStream = sheet.Workbook
.ConvertToBytes()
.BytesToStream();
}
else
{
throw new ArgumentException($"import_templates_{templateName}.xls文件模板不存在");
}
return fileMemoryStream;
}
public static IWorkbook GetWorkbookByStream(Stream stream, string fileName)
{
// 判断是不是文件
if (fileName.EndsWith(".xlsx"))
{
return new XSSFWorkbook(stream);
}
else if (fileName.EndsWith(".xls"))
{
return new HSSFWorkbook(stream);
}
else
{
return null;
}
}
public static ISheet CheckColumnAccordTempleModel<T>(this ISheet sheet,int validDataIndex=0)
{
if(sheet==null) throw new ArgumentNullException(paramName: nameof(CheckColumnAccordTempleModel), "工作表为空");
var cellCount = sheet.GetRow(validDataIndex).PhysicalNumberOfCells;
var properties = typeof(T).GetProperties();
// 逻辑优化,如果实体包含扩展内容,则不需要再判断
if(properties.Any(a=>a.Name== "Properties"))
{
return sheet;
}
else
{
var modelPropsCount = typeof(T).GetProperties().Length;
return cellCount == modelPropsCount ? sheet : throw new ArgumentNullException(paramName: nameof(CheckColumnAccordTempleModel), "导入文件与对应的模板不符,请检查");
};
}
public static ISheet WriteData<T>(this ISheet sheet, List<T> datas, int rowIndex = 0)
{
// 判断T中是否有扩展属性
var props = typeof(T).GetProperties();
if (props.Count(a => a.Name != "Properties") != sheet.GetRow(rowIndex).PhysicalNumberOfCells)
{
throw new ArgumentException("模板与数据格式不符");
}
bool isGetStyle = false;
ICellStyle cellStyle = null;
var rowNumber = 1;
ICell theCell;
cellStyle = sheet.Workbook.CreateCellStyle(); //这个放在for循环里,当数据量大时,会报Excel 4000的错误(好像是)。
datas?.ForEach(a =>
{
var colIndex = 0;
var row = sheet
.CreateRow(rowIndex);
foreach (var prop in props)
{
row
.CreateCell(colIndex)
.SetCellValue(prop.GetValue(a)?.ToString()=="0" && colIndex == 0 ? rowNumber.ToString(): prop.GetValue(a)?.ToString());
//上面这个就是给第一列加上自然序列。
theCell = row.GetCell(colIndex);
if (isGetStyle == false)
{
//加Excel单元格样式的,添加进去的数据得有框线吧
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
isGetStyle = true;
}
theCell.CellStyle = cellStyle;
colIndex++;
}
isGetStyle = false;
rowNumber++;
rowIndex++;
});
return sheet;
}
前端代码:
// 文件导出公共组件,引用并传参就能用。
import './style/index.js';
export default {
name: 'SmExport',
components: {},
props: {
axios: { type: Function, default: () => null },
templateName: { type: String, default: null }, // 导出文件唯一对应标识(将数据导入对应模板)
downloadFileName: { type: String, default: null}, // 下载文件的名称
url: { type: String, default: '' }, // 文件导出地址
rowIndex: { type: Number, default: 0}, //导入的数据对应模板的有效数据起始行
},
data() {
return {
fileType: 'application/vnd.ms-excel',
};
},
methods: {
isCanDownload(){
let _this = this;
this.$confirm({
title: "文件导出",
content: h => <div style="color:red;">{"确认要导出吗?"}</div>,
okType: 'warning',
onOk() {
_this.export();
},
onCancel() { },
});
},
// 文件导出
async export(parameter) {
//根据key去设置rowIndex。待做
return await this.axios(
{ url: this.url,
method: 'post',
responseType:'arraybuffer',
data:{
'templateKey':this.templateName,
'rowIndex':this.rowIndex,
},}
).then(response=>{
if (response != null && requestIsSuccess(response)) {
if (response.data.byteLength != 0){
//第三个参数就是下载的文件名而已
FileSaver.saveAs(new Blob([response.data], { type: this.fileType }), (`${this.downloadFileName}` ? `${this.downloadFileName}` : `${this.templateName}`));
this.$message.success(`文件导出成功`);
}
}
});
},
},
render() {
return (
<div>
<a-button
ghost
type="primary"
onClick={() => this.isCanDownload()}
>
<a-icon type="export" />
文件导出
</a-button>
</div>
);
},
};