I have a large excel file. I want to filter a column "Mainly used for" for values "mainly used for mobile". Then I need to store the corresponding values in the "Number Series" column in a list. I have a code to start with. However I am not able to do the filtering part and storing it to an array list. Could you please help me out here.
我有一个很大的excel文件。我想过滤“主要用于”的列“主要用于移动”。然后我需要将相应的值存储在列表的“数字系列”列中。我有一个代码开始。但是我无法进行过滤部分并将其存储到数组列表中。你能帮帮我吗？
I did some digging and have modified the code. However I have not been able to meet my requirement. I have following problems -
我做了一些挖掘并修改了代码。但是我无法满足我的要求。我有以下问题 -
*The code only selects two columns and displays their contents. Not able to filter :(
*代码只选择两列并显示其内容。无法过滤:(
*The excel has column names with spaces. So I am getting the error. As the excel is generated by the user, we have no control over column names. How to deal with the column name with spaces ??
* excel具有带空格的列名。所以我收到了错误。由于excel是由用户生成的，因此我们无法控制列名。如何用空格处理列名？
*Excel has alpha-numeric values, how to deal with them?
* Excel有字母数字值，如何处理它们？
Could you please help me out here.
你能帮帮我吗？
package com.excel;
import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;*/
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
public class Test {
public static void main(String[] args) throws Exception {
File excel = new File("D:\\FileDownload\\example.xls");
//File excel = new File("D:\\FileDownload\\Sample_Filtered.xls");
FileInputStream fis = new FileInputStream(excel);
//XSSFWorkbook wb = new XSSFWorkbook(fis);
HSSFWorkbook wb = new HSSFWorkbook(fis);
//org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(fis);
HSSFSheet ws = wb.getSheetAt(0);
// org.apache.poi.ss.usermodel.Sheet ws = wb.getSheetAt(0);
ws.setForceFormulaRecalculation(true);
int rowNum = ws.getLastRowNum() + 1;
int colNum = ws.getRow(0).getLastCellNum();
int mainlyUsedForHeaderIndex = -1, mobileSeriesHeaderIndex = -1;
//Read the headers first. Locate the ones you need
HSSFRow rowHeader = ws.getRow(0);
for (int j = 0; j < colNum; j++) {
HSSFCell cell = rowHeader.getCell(j);
String cellValue = cellToString(cell);
if("Mainly used for".equalsIgnoreCase(cellValue)) {
//if("MainlyFor".equalsIgnoreCase(cellValue)) {
mainlyUsedForHeaderIndex = j;
} else if("Number Series".equalsIgnoreCase(cellValue)) {
//else if("MobileSeries".equalsIgnoreCase(cellValue)) {
mobileSeriesHeaderIndex = j;
}
}
if(mainlyUsedForHeaderIndex == -1 || mobileSeriesHeaderIndex == -1) {
throw new Exception("Could not find header indexes\n Mainly used for : " + mainlyUsedForHeaderIndex + " | Number Series: " + mobileSeriesHeaderIndex);
}else{
System.out.println("Indexes are found!!!");
}
//createnew workbook
XSSFWorkbook workbook = new XSSFWorkbook();
//Create a blank sheet
XSSFSheet sheet = workbook.createSheet("data");
for (int i = 1; i < rowNum; i++) {
HSSFRow row = ws.getRow(i);
//row = sheet.createRow(rowNum++);
String MainlyUsed = cellToString(row.getCell(mainlyUsedForHeaderIndex));
String ForMobile = cellToString(row.getCell(mobileSeriesHeaderIndex));
int cellIndex = 0;
XSSFRow newRow = sheet.createRow(i-1);
newRow.createCell(cellIndex++).setCellValue(MainlyUsed);
newRow.createCell(cellIndex++).setCellValue(ForMobile );
}
FileOutputStream fos = new FileOutputStream(new File("D:\\FileDownload\\test1.xlsx"));
System.out.println("File generated");
workbook.write(fos);
fos.close();
}
public static String cellToString(HSSFCell cell) {
int type;
Object result = null;
type = cell.getCellType();
switch (type) {/*
case HSSFCell.CELL_TYPE_NUMERIC:
result = BigDecimal.valueOf(cell.getNumericCellValue())
.toPlainString();
break;
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();*/
case HSSFCell.CELL_TYPE_BLANK:
result="";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
//
result = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR:
//
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
//
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING:
result= cell.getRichStringCellValue();
// result = cell.getStringCellValue();
break;
}
return result.toString();
}
}
1 个解决方案
#1
2
I am able to meet my requirement using following entirely different approach.
我可以使用完全不同的方法来满足我的要求。
package com.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class ExcelRead {
public static void main(String[] args) throws IOException{
String fileName = "D:\\FileDownload\\example.xls";
String cellContent = "Mainly used for mobile";
int rownr=0;
int colnr = 0; //column from which you need data to store in array list
InputStream input = new FileInputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook(input);
HSSFSheet sheet = wb.getSheetAt(0);
List MobileSeries=new ArrayList();
MobileSeries = findRow(sheet, cellContent);
if(MobileSeries !=null){
for(Iterator iter=MobileSeries.iterator();iter.hasNext();){
System.out.println(iter.next());
}
}
//output(sheet, rownr, colnr);
finish();
}
private static void output(HSSFSheet sheet, int rownr, int colnr) {
/*
* This method displays the total value of the month
*/
HSSFRow row = sheet.getRow(rownr);
HSSFCell cell = row.getCell(colnr);
System.out.println("Your total is: " + cell);
}
private static List findRow(HSSFSheet sheet, String cellContent) {
List MobileSeries=new ArrayList();
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
//System.out.println("Row numbers are"+row.getRowNum());
int rownumber=row.getRowNum();
//return row.getRowNum();
HSSFRow row1 = sheet.getRow(rownumber);
HSSFCell cell1 = row1.getCell(0);
MobileSeries.add(cell1);
}
}
}
}
return MobileSeries;
}
private static void finish() {
System.exit(0);
}
}
#1
2
I am able to meet my requirement using following entirely different approach.
我可以使用完全不同的方法来满足我的要求。
package com.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class ExcelRead {
public static void main(String[] args) throws IOException{
String fileName = "D:\\FileDownload\\example.xls";
String cellContent = "Mainly used for mobile";
int rownr=0;
int colnr = 0; //column from which you need data to store in array list
InputStream input = new FileInputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook(input);
HSSFSheet sheet = wb.getSheetAt(0);
List MobileSeries=new ArrayList();
MobileSeries = findRow(sheet, cellContent);
if(MobileSeries !=null){
for(Iterator iter=MobileSeries.iterator();iter.hasNext();){
System.out.println(iter.next());
}
}
//output(sheet, rownr, colnr);
finish();
}
private static void output(HSSFSheet sheet, int rownr, int colnr) {
/*
* This method displays the total value of the month
*/
HSSFRow row = sheet.getRow(rownr);
HSSFCell cell = row.getCell(colnr);
System.out.println("Your total is: " + cell);
}
private static List findRow(HSSFSheet sheet, String cellContent) {
List MobileSeries=new ArrayList();
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
//System.out.println("Row numbers are"+row.getRowNum());
int rownumber=row.getRowNum();
//return row.getRowNum();
HSSFRow row1 = sheet.getRow(rownumber);
HSSFCell cell1 = row1.getCell(0);
MobileSeries.add(cell1);
}
}
}
}
return MobileSeries;
}
private static void finish() {
System.exit(0);
}
}