将数据导出到csv文件中。用excel打开
/**
* 导出
*
* @param authState 企业认证状态:0-未认证; 1-认证中; 2-认证成功; 3-认证拒绝;
* @param isLicense 是否为持牌机构 0=不是,1=是 2=未维护
* @param keyword 关键词
* @param response 响应对象
* @return
*/
public void export(Integer authState, Integer isLicense, String keyword, HttpServletResponse response) {
//查询所有要导出的数据
List<EnterpriseDTO> enterpriseDTOS = enterpriseMapper.listExport(authState, isLicense, keyword, IsDel.NOT_DEL);
//csv表示excel
response.setContentType("text/csv");
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=enterprise.csv");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
OutputStream outputStream = response.getOutputStream();
//BOM 。为了解决将数据导入csv文件后,用excel打开乱码的问题。
//所以先向csv文件输出BOM做标识。可参考下面的参考文章
outputStream.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
outputStream.write("企业名称,企业ID,认证状态,是否为持牌机构,创建用户,电话号码,邮箱地址,创建时间\r\n".getBytes());
byte[] splitBytes = ",".getBytes();
byte[] changeLineBytes = "\r\n".getBytes();
byte[] nullString = "".getBytes();
byte[] isBytes = "是".getBytes();
byte[] isNotBytes = "否".getBytes();
byte[] nullBytes = "未维护".getBytes();
if (enterpriseDTOS != null && enterpriseDTOS.size() > 0) {
enterpriseDTOS.forEach(enterpriseDTO -> {
try {
outputStream.write(enterpriseDTO.getName().getBytes());
outputStream.write(splitBytes);
outputStream.write(enterpriseDTO.getEnterpriseId().getBytes());
outputStream.write(splitBytes);
outputStream.write(AuthStateEnum.getValueByKey(enterpriseDTO.getAuthState()).getBytes());
outputStream.write(splitBytes);
if (enterpriseDTO.getIsLicense() != null) {
if (EnterpriseConstant.IS_LICENSE == enterpriseDTO.getIsLicense()) {
outputStream.write(isBytes);
} else if (EnterpriseConstant.NOT_IS_LICENSE == enterpriseDTO.getIsLicense()) {
outputStream.write(isNotBytes);
} else {
outputStream.write(nullBytes);
}
} else {
outputStream.write(nullBytes);
}
outputStream.write(splitBytes);
if (StringUtils.isNotBlank(enterpriseDTO.getCreateBy())) {
outputStream.write(enterpriseDTO.getCreateBy().getBytes());
} else {
outputStream.write(nullString);
}
outputStream.write(splitBytes);
if (StringUtils.isNotBlank(enterpriseDTO.getContactsMobilePhone())) {
outputStream.write(enterpriseDTO.getContactsMobilePhone().getBytes());
} else {
outputStream.write(nullString);
}
outputStream.write(splitBytes);
if (StringUtils.isNotBlank(enterpriseDTO.getContactsEmail())) {
outputStream.write(enterpriseDTO.getContactsEmail().getBytes());
} else {
outputStream.write(nullString);
}
outputStream.write(splitBytes);
outputStream.write(simpleDateFormat.format(new Date(enterpriseDTO.getCreateTime())).getBytes());
outputStream.write(changeLineBytes);
} catch (IOException e) {
log.error("export client failed", e);
throw new BusinessException(BusinessExceptionCode.INTERNAL_ERR, "企业信息导出异常");
}
});
}
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error("export client failed", e);
throw new BusinessException(BusinessExceptionCode.INTERNAL_ERR, "企业信息导出异常");
}
}
参考文章:
Java IO流将数据输出到.csv文件中,excel打开时乱码问题的解决_tianqiWu的博客-CSDN博客
当用excel打开csv文件后,发现有些数字内容变成科学计数法显示。并且数字的后几位都显示为0了。和查到的数据不同。
此时可在要输出的数据前后加"\t"。这样就会被设置为文本格式。数据就会正常显示
例如:
sb.append("\t"+"123456789999999"+"\t");