51Testing软件测试论坛

标题: 使用Apache的POI将内容写入Excel [打印本页]

作者: 元芳你怎么看 时间: 2018-8-7 17:12
标题: 使用Apache的POI将内容写入Excel
package com.excel.poi.apache;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import javax.swing.plaf.synth.Region;

import java.awt.Color;
import java.awt.color.*;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
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.ss.util.WorkbookUtil;
import org.apache.poi.openxml4j.opc.*;

public class WriteExcelTools {

// public String writepath = ReadExcelTools.readpath;
// public static XSSFWorkbook writeworkbook =ReadExcelTools.readFormula;
// public static Sheet writesheet = null;
//
// 将浮点数据写入指定单元格
public static void writeAppointCell(XSSFWorkbook wb, int sheetNum, int rowNum, int cellNum, double cellContent) {
XSSFSheet sheet = wb.getSheetAt(sheetNum);
// System.out.println(sheet);
XSSFRow row = sheet.getRow(rowNum);
// System.out.println(rowNum);
// System.out.println(row);
// System.out.println(cellNum);
XSSFCell cell = row.getCell(cellNum);
// System.out.println(cell);
cell.setCellValue(cellContent);
}

// 按行写入数据
public static void writeLineCel(XSSFWorkbook wb, int sheetNum, int rowNum, ArrayList<String> list) {
XSSFSheet sheet = wb.getSheetAt(sheetNum);
XSSFRow row = sheet.createRow(rowNum);

for (int i = 0; i < list.size(); i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(list.get(i));
}
}

//复制sheet页
@SuppressWarnings("deprecation")
public static void copySheet(XSSFWorkbook wb, XSSFSheet fromsheet, XSSFSheet newSheet, int firstrow, int lasttrow) {
// 复制一个单元格样式到新建单元格
if ((firstrow == -1) || (lasttrow == -1) || lasttrow < firstrow) {
return;
}
// // 复制合并的单元格
// Region region = null;
// for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
// region = fromsheet.getMergedRegion(i);
// if ((region.getRowFrom() >= firstrow) && (region.getRowTo() <= lasttrow)) {
// newSheet.addMergedRegion(region);
// }
// }
XSSFRow fromRow = null;
XSSFRow newRow = null;
XSSFCell newCell = null;
XSSFCell fromCell = null;
// 设置列宽
for (int i = firstrow; i < lasttrow+1; i++) {
fromRow = fromsheet.getRow(i);
if (fromRow != null) {
for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
int colnum = fromsheet.getColumnWidth((short) j);
if (colnum > 100) {
newSheet.setColumnWidth((short) j, (short) colnum);
}
if (colnum == 0) {
newSheet.setColumnHidden((short) j, true);
} else {
newSheet.setColumnHidden((short) j, false);
}
}
break;
}
}
// 复制行并填充数据
for (int i = 0; i < lasttrow+1; i++) {
fromRow = fromsheet.getRow(i);
if (fromRow == null) {
continue;
}
newRow = newSheet.createRow(i - firstrow);
newRow.setHeight(fromRow.getHeight());
int firstCellNum = fromRow.getFirstCellNum();
int physicalNumberOfCells =fromRow.getPhysicalNumberOfCells();
// System.out.println(physicalNumberOfCells);
for (int j = firstCellNum;j<physicalNumberOfCells;j++){
fromCell = fromRow.getCell(j);
if(fromCell == null){
continue;
}
newCell = newRow.createCell(j);
newCell.setCellStyle(fromCell.getCellStyle());
int cType = fromCell.getCellType();
newCell.setCellType(cType);
switch(cType){
case XSSFCell.CELL_TYPE_STRING:newCell.setCellValue(fromCell.getRichStringCellValue());break;
case XSSFCell.CELL_TYPE_NUMERIC:newCell.setCellValue(fromCell.getNumericCellValue());break;
case XSSFCell.CELL_TYPE_FORMULA:newCell.setCellValue(fromCell.getCellFormula());break;
case XSSFCell.CELL_TYPE_BOOLEAN:newCell.setCellValue(fromCell.getBooleanCellValue());break;
case XSSFCell.CELL_TYPE_ERROR:newCell.setCellValue(fromCell.getErrorCellValue());break;
default:newCell.setCellValue(fromCell.getRichStringCellValue());break;
}
}
}
}

//比对两个单元格的数值是否相等；
public static void compareCell(XSSFWorkbook wb, XSSFCell cellF, XSSFCell cellP) {
double fvalue = Double.parseDouble(cellF.getStringCellValue());
double pvalue = Double.parseDouble(cellP.getStringCellValue());
XSSFCellStyle style = wb.createCellStyle();
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);;
style.setFillForegroundColor(new XSSFColor(Color.RED));;
if (fvalue != pvalue) {
System.out.println(cellF+"*****"+cellP);
cellF.setCellStyle(style);
cellP.setCellStyle(style);
}
}

public static void main(String[] args) {

XSSFWorkbook wb = new XSSFWorkbook();
try {
wb = ReadExcelTools.readFile("D:/Practice/jieguodemo.xlsx");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}

XSSFCell cellF = wb.getSheetAt(1).getRow(5).getCell(5);
System.out.println(cellF);
XSSFCell cellP = wb.getSheetAt(1).getRow(9).getCell(5);
System.out.println(cellP);

compareCell(wb,cellF,cellP);

// writeAppointCell(wb, 0, 14, 2, 9999);

FileOutputStream os;
try {
os = new FileOutputStream("D:/Practice/jieguodemo.xlsx");
wb.write(os);
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

}

}

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)