java操作excel的问题
发布网友
发布时间：2022-04-20 21:09
共5个回答
热心网友
时间：2023-08-03 20:38
可以是一下GCExcel这个组件，相较POI，GCExcel在功能上更加完善，并且完全参照Excel的规范。如果想要删除行不留空白行，直接在GCExcel调用删除命令就可以了，不需要再做上移的操作，更加易于理解。删除行时只需要输入行的名称或者索引，像这样：
worksheet2.getRange("A3:A5").getEntireRow().delete();
或worksheet2.getRange("2:4").delete(); 支持删除多行。
参考文档：网页链接
热心网友
时间：2023-08-03 20:38
关键字自己判断，给你写了一个删除行，并且上移的方法
public static void removeRow(HSSFSheet sheet, int rowIndex) {int lastRowNum=sheet.getLastRowNum();
if(rowIndex>=0&&rowIndex<lastRowNum){
sheet.shiftRows(rowIndex+1,lastRowNum, -1);
}
if(rowIndex==lastRowNum){
HSSFRow removingRow=sheet.getRow(rowIndex);
if(removingRow!=null){
sheet.removeRow(removingRow);
}
}}
热心网友
时间：2023-08-03 20:39
style.setWrapText(true);
关于一行拆分几行，什么意思？那不就是把几行合并成一行么，单元格的合并？追问你这文不对题
热心网友
时间：2023-08-03 20:39
热心网友
时间：2023-08-03 20:40
所以你需要一个poi的操作类（这个可以找到很多的，我收藏的一个给你）
/***
*/
package com.b510.common;
/**
* @author Hongten
* @created 2014-5-21
*/
public class Common {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static final String LIB_PATH = "lib";
public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
}
/**
*
*/
package com.b510.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import com.b510.common.Common;
import com.b510.excel.util.Util;
import com.b510.excel.vo.Student;
/**
* @author Hongten
* @created 2014-5-20
*/
public class ReadExcel {
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public List<Student> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null;
} else {
String postfix = Util.getPostfix(path);
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path);
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path);
}
} else {
System.out.println(path + Common.NOT_EXCEL_FILE);
}
}
return null;
}
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<Student> readXlsx(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
student = new Student();
XSSFCell no = xssfRow.getCell(0);
XSSFCell name = xssfRow.getCell(1);
XSSFCell age = xssfRow.getCell(2);
XSSFCell score = xssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
public List<Student> readXls(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
student = new Student();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
/**
*
*/
package com.b510.excel.client;
import java.io.IOException;
import java.util.List;
import com.b510.common.Common;
import com.b510.excel.ReadExcel;
import com.b510.excel.vo.Student;
/**
* @author Hongten
* @created 2014-5-21
*/
public class Client {
public static void main(String[] args) throws IOException {
String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH;
String excel2010 = Common.STUDENT_INFO_XLSX_PATH;
// read the 2003-2007 excel
List<Student> list = new ReadExcel().readExcel(excel2003_2007);
if (list != null) {
for (Student student : list) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
System.out.println("======================================");
// read the 2010 excel
List<Student> list1 = new ReadExcel().readExcel(excel2010);
if (list1 != null) {
for (Student student : list1) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
}
}
/**
*
*/
package com.b510.excel.util;
import com.b510.common.Common;
/**
* @author Hongten
* @created 2014-5-21
*/
public class Util {
/**
* get postfix of the path
* @param path
* @return
*/
public static String getPostfix(String path) {
if (path == null || Common.EMPTY.equals(path.trim())) {
return Common.EMPTY;
}
if (path.contains(Common.POINT)) {
return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
}
return Common.EMPTY;
}
}
/**
*
*/
package com.b510.excel.vo;
/**
* Student
*
* @author Hongten
* @created 2014-5-18
*/
public class Student {
/**
* id
*/
private Integer id;
/**
* 学号
*/
private String no;
/**
* 姓名
*/
private String name;
/**
* 学院
*/
private String age;
/**
* 成绩
*/
private float score;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public float getScore() {
return score;
}
public void setScore(float score) {
this.score = score;
}
}
