Java利用POI实现数据的Excel导出分列(相同标题换列输出)

标题是“星期 月份”,把数据分列输出不同行并标题不变。谢谢
Java利用POI实现数据的Excel导出分列(相同标题换列输出)

2个回答

图片说明 效果类似这种

首先,需要将excel内容读入并解析,合并得到一个最终输出的列表集合,每一行只包含星期、月份信息;
其次,再将解析结果重新导出写入Excel中。
至于具体实现,首先可以定义一个对象:

 public class ExcelInfo {
    private String week;
    private String month;

    /**
     * @param week
     * @param month
     */
    public ExcelInfo(String week, String month) {
        super();
        this.week = week;
        this.month = month;
    }

    public String getWeek() {
        return week;
    }

    public void setWeek(String week) {
        this.week = week;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

}

其次,就是遍历解析:这里的每一行的数据可以解析称两个对象存入列表中。

 import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtil {
    /**
     * 解析Excel:每一行解析为两个对象,最终结果写入
     * @param in
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     */
    public static List<ExcelInfo> parseExcel(InputStream in) throws InvalidFormatException, IOException{
        Workbook workbook = WorkbookFactory.create(in);
        Sheet sheet= workbook.getSheetAt(0);
        int rows =sheet.getLastRowNum() + 1;//行号id+1
        List<ExcelInfo> result = new ArrayList<ExcelInfo>(rows*2);
        for(int i =1;i<rows;i++){
            Row contentRow = sheet.getRow(i);
            for(int j = 0;j<4;j++){
                Cell week1 = contentRow.getCell(0);
                Cell month1 = contentRow.getCell(1);
                ExcelInfo info1 = new ExcelInfo(week1.getStringCellValue(),month1.getStringCellValue());
                result.add(info1);

                Cell week2 = contentRow.getCell(2);
                Cell month2 = contentRow.getCell(3);
                ExcelInfo info2 = new ExcelInfo(week2.getStringCellValue(),month2.getStringCellValue());
                result.add(info2);
            }
        }

        return result;
    }

    public static void export(String fileName,List<ExcelInfo> list) throws IOException{
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Sheet1");

        //第一行标题
        XSSFRow row0 = sheet.createRow(0);
        XSSFCell cell1 = row0.createCell(0);
        XSSFCell cell2 = row0.createCell(1);
        cell1.setCellValue("星期");
        cell2.setCellValue("月份");

        int len = list.size();
        for(int i =0;i<len;i++){
            ExcelInfo info = list.get(i);
            XSSFRow currentRow = sheet.createRow(i+1);
            XSSFCell week = currentRow.createCell(0);
            XSSFCell month = currentRow.createCell(1);
            week.setCellValue(info.getWeek());
            month.setCellValue(info.getMonth());
        }

        //写入文件中
        File file = new File(fileName);
        OutputStream out = new FileOutputStream(file);
        workbook.write(out);
        out.flush();
        out.close();
    }

    public static void main(String[] args) {
        FileInputStream in = null;
        List<ExcelInfo> list;
        try {
            in = new FileInputStream(new File("D:/initial.xls"));
            list = parseExcel(in);
            export("D:/merge.xls",list);
        } catch (InvalidFormatException | IOException e) {
            e.printStackTrace();
        }finally{
            if(in!=null){
                try {
                    in.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}

直接将你的Excel文件路径代入main方法的参数中,就能输出合并的excel了。

sinat_34093604
吃奶的牛 首先非常感谢您的回答,其实又一次重新学习了一下。虽然并没有解决我的问题,但还是非常感谢!
接近 4 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐