package com.ruoyi.web.core.config;
|
|
import com.ruoyi.common.config.RuoYiConfig;
|
import com.ruoyi.common.utils.poi.ExcelUtil;
|
import com.ruoyi.project.domain.BaseFinancesubject;
|
import com.ruoyi.project.domain.TestExcelVo;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.FileInputStream;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
public class Test {
|
|
public static void main(String[] args) throws Exception {
|
|
// mergeExcel("E:\\OPO\\WEB\\Upload\\download\\3683d6af-c2c6-4225-821c-e54f6b0cda3f_VIEW数据.xlsx", "E:\\OPO\\WEB\\Upload\\download\\18ae2e71-730a-4948-929c-549df35104e3_VIEW数据.xlsx", "E:\\OPO\\WEB\\Upload\\download\\数据测试.xlsx");
|
//
|
// ArrayList list=new ArrayList();
|
// list.add("3683d6af-c2c6-4225-821c-e54f6b0cda3f_VIEW数据.xlsx");
|
// list.add("bcfc7779-d7f1-416b-9c60-61315a59699e_VIEW数据.xlsx");
|
//
|
// mergeExcel2(list,"E:\\OPO\\WEB\\Upload\\download\\数据测试.xlsx");
|
}
|
|
private static void createTableWithBorders(Sheet sheet, int startRow, int startCol, int numRows, int numCols, List<String> list, Workbook workbook) {
|
for (int i = startRow; i < startRow + numRows; i++) {
|
Row row = sheet.createRow(i);
|
if (i == 4) {
|
CellStyle cellStyle = workbook.createCellStyle();
|
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 设置前景填充颜色为黄色
|
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式
|
|
// 应用样式到行
|
row.setRowStyle(cellStyle);
|
}
|
for (int j = startCol; j <= startCol + numCols; j++) {
|
sheet.setColumnWidth(j, (j + 1) * 256);
|
Cell cell = row.createCell(j);
|
// cell.setCellValue("Table" + (i - startRow) + (j - startCol));
|
cell.setCellValue(list.get(j - startCol));
|
// Apply borders to the cell
|
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
|
cellStyle.setBorderTop(BorderStyle.THIN);
|
cellStyle.setBorderBottom(BorderStyle.THIN);
|
cellStyle.setBorderLeft(BorderStyle.THIN);
|
cellStyle.setBorderRight(BorderStyle.THIN);
|
cell.setCellStyle(cellStyle);
|
}
|
}
|
}
|
|
|
public static void mergeExcel(String file1, String file2, String outputFile) throws IOException {
|
try {
|
InputStream is1 = new FileInputStream(file1);
|
InputStream is2 = new FileInputStream(file2);
|
Workbook workbook1 = new XSSFWorkbook(is1);
|
Workbook workbook2 = new XSSFWorkbook(is2);
|
Workbook resultWorkbook = new XSSFWorkbook();
|
|
Sheet sheet1 = workbook1.getSheetAt(0);
|
Sheet sheet2 = workbook2.getSheetAt(0);
|
|
// 复制第一个文件的Sheet
|
Sheet newSheet1 = resultWorkbook.createSheet("Sheet1");
|
for (Row row : sheet1) {
|
Row newRow = newSheet1.createRow(row.getRowNum());
|
int columns = row.getLastCellNum();
|
for (int i = 0; i < columns; i++) {
|
newSheet1.setColumnWidth(i, 6000);
|
Cell cell = row.getCell(i);
|
Cell newCell = newRow.createCell(cell.getColumnIndex());
|
newCell.setCellValue(cell.getStringCellValue());
|
}
|
}
|
|
// 复制第二个文件的Sheet
|
Sheet newSheet2 = resultWorkbook.createSheet("Sheet2");
|
for (Row row : sheet2) {
|
Row newRow = newSheet2.createRow(row.getRowNum());
|
for (Cell cell : row) {
|
Cell newCell = newRow.createCell(cell.getColumnIndex());
|
newCell.setCellValue(cell.getStringCellValue());
|
}
|
}
|
|
// 写入到新的Excel文件
|
FileOutputStream out = new FileOutputStream(outputFile);
|
resultWorkbook.write(out);
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
|
public static void mergeExcel2(List<String> files, String outputFile) {
|
try {
|
Workbook resultWorkbook = new XSSFWorkbook();
|
for (String file : files) {
|
InputStream is1 = new FileInputStream("E:\\OPO\\WEB\\Upload\\download\\" + file);
|
Workbook workbook1 = new XSSFWorkbook(is1);
|
Sheet sheet1 = workbook1.getSheetAt(0);
|
// 复制第一个文件的Sheet
|
Sheet newSheet1 = resultWorkbook.createSheet(file.substring(0, file.indexOf(".")));
|
for (Row row : sheet1) {
|
Row newRow = newSheet1.createRow(row.getRowNum());
|
int columns = row.getLastCellNum();
|
for (int i = 0; i < columns; i++) {
|
newSheet1.setColumnWidth(i, 6000);
|
Cell cell = row.getCell(i);
|
Cell newCell = newRow.createCell(cell.getColumnIndex());
|
newCell.setCellValue(cell.getStringCellValue());
|
}
|
}
|
|
}
|
FileOutputStream out = new FileOutputStream(outputFile);
|
resultWorkbook.write(out);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
