List<?> dataList = arumDataService.getDataList(dto);
List<List<String>> head = arumDataService.getDataListExcel(dto);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
EasyExcel.write(byteArrayOutputStream,null).head(head)
//给定工作表名称
.sheet("居民用户工单")
//给定导出数据
.doWrite(dataList);
ByteArrayInputStream swapStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
// Load Excel workbook
Workbook workbook = new Workbook(swapStream);
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Add VBA Module
int idx = workbook.getVbaProject().getModules().add(worksheet);
// Access the VBA Module, set its name and codes
VbaModule module = workbook.getVbaProject().getModules().get(idx);
module.setName("TestModule");
String macroCode = "Sub AdjustImagesAndTextAutoSize()\n" +
" Dim ws As Worksheet\n" +
" Set ws = ActiveSheet\n" +
"\n" +
" Dim dataRange As Range\n" +
" Set dataRange = ws.UsedRange\n" +
"\n" +
" Dim cell As Range\n" +
" For Each cell In dataRange\n" +
" If Left(cell.Value, 4) = \"http\" Then\n" +
" Dim imageUrls() As String\n" +
" imageUrls = Split(cell.Value, \",\")\n" +
"\n" +
" Dim imageUrl As Variant\n" +
" For Each imageUrl In imageUrls\n" +
" Dim pic As Picture\n" +
" Set pic = ws.Pictures.Insert(imageUrl)\n" +
"\n" +
" With pic\n" +
" .ShapeRange.LockAspectRatio = msoFalse\n" +
" .ShapeRange.Width = 25\n" +
" .ShapeRange.Height = 50\n" +
" .Top = cell.Top\n" +
" .Left = cell.Left\n" +
" End With\n" +
"\n" +
" cell.Value = \"\"\n" +
" Next imageUrl\n" +
" Else\n" +
" cell.WrapText = True\n" +
" cell.EntireRow.AutoFit\n" +
" cell.EntireRow.RowHeight = 50\n" +
" cell.EntireColumn.AutoFit\n" +
" End If\n" +
" Next cell\n" +
"End Sub";
module.setCodes(macroCode);
// Save the workbook
//workbook.save("output.xlsm", SaveFormat.XLSM);
ByteArrayOutputStream byteArrayOutputStream1 = new ByteArrayOutputStream();
workbook.save(byteArrayOutputStream1,SaveFormat.XLSM);
ByteArrayInputStream swapStream1 = new ByteArrayInputStream(byteArrayOutputStream1.toByteArray());
//上传到阿里云 或者fastdfs都行
if(fastEnable) {
StorePath storePath = fastFileStorageClient.uploadFile(swapStream1, swapStream1.available(), "xls", null);
url = storePath.getFullPath();
}else{
UploadAliYunOSS.uploadStreamFileFromAliYunOSS(swapStream1, "test.xls");
}
java导出excel 如果直接把图片写到excel会增大excel文件的大小,如果数据量非常多,会超级慢,所以可以 ,往excel中加入宏方法,导出excel 按alt+f8运行宏 就行了
Aspose.Cells for Java jar包下载,导入到项目里