使用 java 和 Apache POI 比较 xls 表的特定行或列 [英] Compare specific row or column of xls sheet using java and Apache POI
本文介绍了使用 java 和 Apache POI 比较 xls 表的特定行或列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的jsp文件是(上传excel文件):
My jsp file is (to upload the excel file) :
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<form method="post" action="UploadAction" enctype="multipart/form-data">
Upload the *.xls file: <input type="file" name="file" value="file">
<br><br>
<input type="submit" value="Submit">
</body>
</html>
我的 Servlet 代码是(处理 excel 文件)
My Servlet code is ( to process the excel file)
import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.File;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Iterator;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class UploadAction extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException
{
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
PrintWriter out = response.getWriter();
Connection conn = null;
PreparedStatement pstmt = null;
String query = "INSERT INTO mydoct(ID) VALUES (?)";
if(isMultipart)
{
try
{
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List items = upload.parseRequest(request);
Iterator iter = items.iterator();
while (iter.hasNext())
{
FileItem item = (FileItem) iter.next();
if(!item.isFormField())
{
String fileName = item.getName();
String root = getServletContext().getRealPath("/");
File path = new File(root + "/MyDoctors");
if (!path.exists()) {
boolean status = path.mkdirs();
}
File uploadedFile = new File(path + "/" + fileName);
String fpath = uploadedFile.getAbsolutePath();
//out.println(fpath);
int mid = fileName.lastIndexOf(".");
String ext = fileName.substring(mid+1,fileName.length());
//out.println(ext);
if((ext.toLowerCase().equals("xls"))||(ext.toLowerCase().equals("xlsx")))
{
item.write(uploadedFile);
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
conn = DriverManager.getConnection(url,"root","root");
pstmt = conn.prepareStatement(query);
FileInputStream file = new FileInputStream(new File(fpath));
HSSFWorkbook wb = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = wb.getSheetAt(0);
//Find the total number of rows.
int rows = sheet.getPhysicalNumberOfRows();
out.println(rows);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
//while(rowIterator.hasNext()) {
for(int i = 1; i<=rows; i++){
Row row = rowIterator.next();
//find the total no columns in a particular raw.
HSSFRow rcol = sheet.getRow(i);
int cells = 0;
if (row != null) {
cells = row.getPhysicalNumberOfCells();
//out.println(cells);
}
//For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
//while(cellIterator.hasNext()) {
for(int j = 0; j<cells; j++){
Cell cell = cellIterator.next();
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
out.print(cell.getBooleanCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
out.print(cell.getStringCellValue() + "\t\t");
break;
}
}
}
file.close();
FileOutputStream fos = new FileOutputStream(new File(fpath));
wb.write(fos);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}else {
out.println("invalid file");
}
}
}catch(Exception ex)
{
ex.printStackTrace();
}
}
}
}
我想读取第 5、6 和 7 行的第 2 和第 3 列.请帮帮我.......
I want to read the 2nd and 3rd columns of 5th, 6th and 7th rows. Please help me..........
推荐答案
如Apache POI 文档，有两种广泛的方式来访问行和单元格.一种是遍历所有这些(非常适合您想要所有东西的情况)，另一种是按索引获取行和单元格(当您需要特定单元格时效果很好)
As clearly shown in the Apache POI Documentation, there are two broad ways to access rows and cells. One is to iterate over all of them (well suited to cases when you want everything), the other is to fetch rows and cells by index (works well when you want specific cells)
你已经说过你想要第 5、6 和 7 行的第 2 和第 3 列.这很简单:
You've said you want the 2nd and 3rd columns of rows 5, 6 and 7. That's very easy:
Sheet s = workbook.getSheetAt(0);
// Rows and Cells are 0 based not 1 based
Row r5 = s.getRow(4);
Row r6 = s.getRow(5);
Row r7 = s.getRow(6);
if (r5 == null || r6 == null || r7 == null) {
throw new IllegalArgumentException("At least one required row is empty");
}
Cell c5_2 = r5.getCell(1);
Cell c5_3 = r5.getCell(2);
Cell c6_2 = r6.getCell(1);
Cell c6_3 = r6.getCell(2);
Cell c7_2 = r7.getCell(1);
Cell c7_3 = r7.getCell(2);
// Now use them
// eg
for (Cell cell : new Cell[] { c5_2, c5_3, c6_2, c6_3 } ) {
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
out.print(cell.getBooleanCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
out.print(cell.getStringCellValue() + "\t\t");
break;
}
}
这篇关于使用 java 和 Apache POI 比较 xls 表的特定行或列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
