java通过poi读取.xls,官方下载地址:
https://poi.apache.org/download.html
直接下载地址:
https://mvnrepository.com/artifact/org.apache.poi/poi
update 2018/10/26,贴上java code
1 读取excel:
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.xssf.usermodel.XSSFWorkbook; import org.json.JSONArray; import org.json.JSONObject; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; /** * Description: Excel操作 */ public class ExcelUtil { private static final String EXCEL_XLS = "xls"; private static final String EXCEL_XLSX = "xlsx"; /** * 判断Excel的版本,获取Workbook * * @param in * @param file * @return * @throws IOException */ public static Workbook getWorkbook(InputStream in, File file) throws IOException { Workbook wb = null; if (file.getName().endsWith(EXCEL_XLS)) { //Excel 2003 wb = new HSSFWorkbook(in); } else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010 wb = new XSSFWorkbook(in); } return wb; } /** * 判断文件是否是excel * * @throws Exception */ public static void checkExcelValid(File file) throws Exception { if (!file.exists()) { throw new Exception("文件不存在"); } if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) { throw new Exception("文件不是Excel"); } } /** * 读取Excel测试,兼容 Excel 2003/2007/2010 * * @throws Exception */ public static void main(String[] args) throws Exception { int count = 0; try { // 同时支持Excel 2003、2007 File excelFile = new File(args[0]); // 创建文件对象 FileInputStream in = new FileInputStream(excelFile); // 文件流 checkExcelValid(excelFile); Workbook workbook = getWorkbook(in, excelFile); //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的 // int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量 /** * 设置当前excel中sheet的下标:0开始 */ Sheet sheet = workbook.getSheetAt(0); // 遍历第一个Sheet //获取总行数 System.out.println("当前sheet:1,总行数:" + sheet.getLastRowNum()); JSONArray rootArray = new JSONArray(); JSONObject levelObject = null; JSONArray tollgateArray = null; for (Row row : sheet) { // 跳过第一的标题目录 // if(count < 1 ) { // count++; // continue; // } //获取总列数(空格的不计算) // int columnTotalNum = row.getPhysicalNumberOfCells(); System.out.println("第 " + count + " 行:" + row.getCell(0)); if (count % 3 == 1) { levelObject = new JSONObject(); rootArray.put(levelObject); tollgateArray = new JSONArray(); levelObject.put("tollgate", tollgateArray); } JSONObject tollgateObject = new JSONObject(); tollgateArray.put(tollgateObject); int end = row.getLastCellNum(); // 具体解析excel内容 // for (int i = (count % 3 == 1 ? 1 : 2); i < end; i++) { // Cell cell = row.getCell(i); // switch (i) { // case 0: // break; // case 1: // String duration = (String) getValue(cell); // levelObject.put("duration", //Integer.parseInt(duration.substring(0, duration.length() - 1))); // break; // case 2: // tollgateObject.put("pic1", (String) getValue(cell)); // break; // case 3: // tollgateObject.put("pic2", (String) getValue(cell)); // break; // case 4: // String coordinates = (String) getValue(cell); // coordinates = coordinates.replaceAll("X", "\"X\""); // coordinates = coordinates.replaceAll("Y", "\"Y\""); // coordinates = "[" + coordinates + "]"; // JSONArray pointsArray = new JSONArray(coordinates); // tollgateObject.put("points", pointsArray); // break; // case 5: // String touchEffectArea = (String) getValue(cell); // String[] area = touchEffectArea.split("\\*"); // levelObject.put("effectWidth", //Integer.parseInt(area[0])); // levelObject.put("effectHeight", //Integer.parseInt(area[1])); // break; // } // } count++; } System.out.println(rootArray.toString()); writeFile(excelFile.getParent(), rootArray.toString()); } catch (Exception e) { System.out.println("````````````` " + count); e.printStackTrace(); } } /** * 将读取内容写入json文件 */ private static void writeFile(String directory, String stream) throws Exception { FileOutputStream outputStream = null; try { System.out.println(directory + File.separator + "configs.json"); outputStream = new FileOutputStream(directory + File.separator + "configs.json"); byte[] streamByte = stream.getBytes(); outputStream.write(streamByte, 0, streamByte.length); } catch (Exception e) { throw e; } finally { if (null != outputStream) { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } } /** * 读取单元格内容 */ private static Object getValue(Cell cell) { Object obj = null; switch (cell.getCellType()) { case BOOLEAN: obj = cell.getBooleanCellValue(); break; case ERROR: obj = cell.getErrorCellValue(); break; case NUMERIC: obj = cell.getNumericCellValue(); break; case STRING: obj = cell.getStringCellValue(); break; default: break; } return obj; } }
2.将内容写入到excel文件
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
/**
* Created by Administrator on 2018/10/25.
* 此类用途:随机打乱excel行 创建excel文件并写入
*/
public class ExcelRandom {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static void main(String[] args) throws Exception {
int count = 0;
try {
// 同时支持Excel 2003、2007
File excelFile = new File(args[0]); // 创建文件对象
checkExcelValid(excelFile);
Workbook workbook = getWorkbook(new FileInputStream(excelFile), excelFile);
//Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的
/**
* 设置当前excel中sheet的下标:0开始
*/
for (int sheetIndex = 0, sheetCount = workbook.getNumberOfSheets(); sheetIndex < sheetCount; sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex); // 遍历第i个Sheet
//获取总行数
System.out.println("当前sheet:" + sheetIndex + ",总行数:" + sheet.getLastRowNum());
JSONArray rootArray = new JSONArray();
CellStyle style = null;
for (Row row : sheet) {
JSONArray rowArray = new JSONArray();
// StringBuffer buffer = new StringBuffer();
for (int i = 1, size = row.getLastCellNum(); i < size; i++) {
Cell cell = row.getCell(i);
if (null != cell) {
//获取单元格显示格式
if (null == style && ((sheet.getLastRowNum() >= 4 && row.getRowNum() >= 2)))
style = cell.getCellStyle();
String text = (String) getValue(cell);
// buffer.append(text);
rowArray.put(i, text);
} else {
// System.out.println("============cell == null, columnIndex = " + (i + 1) + ", totalColumn = " + size);
}
}
// System.out.println(buffer);
rootArray.put(rowArray);
}
// System.out.println(rootArray.toString());
writeFile(excelFile.getName(), excelFile.getParent(), rootArray, sheet.getSheetName(), style);
}
} catch (Exception e) {
System.out.println("error " + (count + 1) + " " + e.toString());
}
}
/**
* 判断Excel的版本,获取Workbook
*
* @param in
* @param file
* @return
* @throws IOException
*/
public static Workbook getWorkbook(InputStream in, File file) throws IOException {
Workbook wb = null;
if (file.getName().endsWith(EXCEL_XLS)) { //Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
/**
* 判断文件是否是excel
*
* @throws Exception
*/
public static void checkExcelValid(File file) throws Exception {
if (!file.exists()) {
throw new Exception("文件不存在");
}
if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
throw new Exception("文件不是Excel");
}
}
private static Object getValue(Cell cell) {
Object obj = null;
switch (cell.getCellType()) {
case BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case ERROR:
obj = cell.getErrorCellValue();
break;
case NUMERIC:
obj = cell.getNumericCellValue();
break;
case STRING:
obj = cell.getStringCellValue();
break;
default:
break;
}
return obj;
}
private static void writeFile(String sourceName, String directory, JSONArray array, String sheetName, CellStyle style) throws Exception {
if (null != array && array.length() > 0) {
int point = sourceName.lastIndexOf(".");
File excelFile = new File(directory + File.separator + sourceName.substring(0, point) + "_生成结果" + sourceName.substring(point)); // 创建文件对象
if (excelFile.exists())
excelFile.delete();
Workbook workbook;
if (sourceName.endsWith(EXCEL_XLS)) {
workbook = new HSSFWorkbook();
} else if(sourceName.endsWith(EXCEL_XLSX)) {
workbook = new XSSFWorkbook();
} else {
throw new Exception("文件格式不正确");
}
//创建sheet对象,此处主要作用是创建excel空白文件,其它方法如:File.createNewFile()是无法创建能够识别的正确excel文件
OutputStream outputStream = new FileOutputStream(excelFile);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
//随机排序行
JSONArray resultArray = new JSONArray();
int index = 0;
for (int i = 0, length = array.length(); i < length; i++)
{
int indexRandom = (int) (Math.random() * array.length());
resultArray.put(index++, array.get(indexRandom));
array.remove(indexRandom);
}
//将数据写入到excel文档中
Sheet sheet = workbook.createSheet(sheetName);//创建sheet
CellStyle cellStyle = workbook.createCellStyle();//CellStyle只能创建,无法复用其它workbook的style对象
cellStyle.cloneStyleFrom(style);
if (null == cellStyle.getAlignment())//手动设置单元格的水平对齐方式
cellStyle.setAlignment(HorizontalAlignment.LEFT);
if (null == cellStyle.getVerticalAlignment())//手动设置单元格的垂直对齐方式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//开始逐行逐个单元格写入数据
for (int i = 0, size = resultArray.length(); i < size; i++) {
Row row = sheet.createRow(i);
JSONArray rowJson = resultArray.getJSONArray(i);
System.out.println("第 " + i + " 行:" + rowJson.toString());
if (null != rowJson && rowJson.length() > 0) {
for (int j = 0, length = rowJson.length(); j < length; j++) {
Cell cell = row.createCell(j);
if (!rowJson.isNull(j) && rowJson.get(j) != JSONObject.NULL) {
cell.setCellStyle(cellStyle);
cell.setCellValue((String) rowJson.get(j));
}
}
}
}
//将生成的sheet写入到创建的excel文件中
OutputStream outputStream1 = new FileOutputStream(excelFile);
workbook.write(outputStream1);
outputStream1.close();
}
}
}
以上为通过poi jar系列包读取和写入excel示例。仅供交流所用
poi完整系列jar资源下载地址:https://download.csdn.net/download/xianglongjifei/10691018