//判断程序运行的操作系统
String rootPath="";
if("\\".equals(File.separator)){//windos
File directory = new File("");//设定为当前文件夹
rootPath = directory.getCanonicalPath();
rootPath = rootPath.replace("/", "\\");
rootPath = rootPath+"\\"+fileName+".xlsx";
}else if("/".equals(File.separator)){//linux
//服务器中的excel生成的路径
rootPath = filePath+"/"+fileName+".xlsx";
}
System.out.println(rootPath);
FileOutputStream file=new FileOutputStream(rootPath);//创建文件输出流
BufferedOutputStream out=new BufferedOutputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook();
for(ExcelBean bean : listBean){
//显示的导出表的标题
String title=bean.getTitle();
//导出表的列名
String[] rowName=bean.getRowName() ;
String rowTitleName=bean.getTitleName();
List<String[]> dataList = bean.getDataList();
XSSFSheet sheet = workbook.createSheet(title); // 创建工作表
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
XSSFCellStyle columnTopStyle = excelType.getColumnTopStyle(workbook);//获取列头样式对象
XSSFCellStyle style = excelType.getStyle(workbook); //单元格样式对象
// 定义所需列数
int columnNum = rowName.length;
XSSFRow rowRowTitle;
XSSFRow rowRowName;
boolean titleName=rowTitleName!=null;
if(titleName){
rowRowTitle= sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)
rowRowName= sheet.createRow(1); // 在索引2的位置创建行(最顶端的行开始的第二行)
XSSFCell cell = rowRowTitle.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(columnTopStyle);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnNum-1);
sheet.addMergedRegion(region);
cell.setCellValue(rowTitleName);
}else{
rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)
}
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
XSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle);
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);//遍历每个对象
XSSFRow row;
if(titleName){
row = sheet.createRow(i+2);//创建所需的行数(从第二行开始写数据)
}else{
row = sheet.createRow(i+1);//创建所需的行数(从第二行开始写数据)
}
for(int j=0; j<obj.length; j++){
XSSFCell cell = null; //设置单元格的数据类型
if(j == 0){
cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i+1);
// cell.setCellStyle(style); //设置单元格样式
}else{
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(obj[j]) && obj[j] != null){
cell.setCellValue(obj[j].toString()); //设置单元格的值
// cell.setCellStyle(style); //设置单元格样式
}
if(j==obj.length-1){
}else{
// cell.setCellStyle(style);
}
}
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
XSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
if (currentRow.getCell(colNum) != null) {
XSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = 0;
try {
length = currentCell.getStringCellValue().getBytes().length;
} catch (Exception e) {
e.printStackTrace();
}
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
}
if(workbook !=null){
try{
workbook.write(out);
}catch (IOException e) {
e.printStackTrace();
}
}
return rootPath;
}
public CreatExcelUtil(){
}
public CreatExcelUtil(String filepath) {
if(filepath==null){
return;
}
String ext = filepath.substring(filepath.lastIndexOf("."));
try {
InputStream is = new FileInputStream(filepath);
if(".xls".equals(ext)){
wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(ext)){
wb = new XSSFWorkbook(is);
}else{
wb=null;
}
} catch (FileNotFoundException e) {
logger.error("FileNotFoundException", e);
} catch (IOException e) {
logger.error("IOException", e);
}
}
java生成excel
最新推荐文章于 2024-07-24 09:54:02 发布