如何使用Apache POI在Excel 2007文档中预定义自动筛选区域？
提供一个小的代码示例或链接会很不错。
7个回答
21
保存筛选区域的第一个和最后一个单元格，并执行：
sheet.setAutoFilter(new CellRangeAddress(firstCell.getRow(), lastCell.getRow(), firstCell.getCol(), lastCell.getCol()));
>x (x, y)
0123456
0|--hhh--| h = header
1|--+++--| + = values
2|--+++--| - = empty fields
3|--+++--|
4|-------|
第一个单元格将成为第一个+（2,1）单元格上方的标题。最后一个单元格将是最后一个+（5,3）单元格。
- Victor
2
7
我发现获取CellRange很困难，因为我的列和行值都是变量。所以我使用了这个技巧。
将lastCellReference变量初始化为A1，即第一行第一列的单元格
String lastCellReference = "A1";
每次创建单元格时，请更新lastCellReference以保持最新值。
cell = row.createCell((short) columnCount);
cell.setCellValue(rs.getInt(i));
lastCellReference=cell.getReference();
A1将始终是第一个单元格，然后是lastCellReference
sheet1.setAutoFilter(CellRangeAddress.valueOf("A1:"+ lastCellReference));
- Anuja
5
//include poi-3.7.jar,ojdbc.jar in classpath
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.io.*;
import java.net.*;
import javax.servlet.*;
import javax.servlet.http.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
public class ExcelCreator{
public static void createExcel(String anySql,OutputStream out) throws Exception{
Connection conn=getOracleConnection();
String userSql1="select * from tab";
String userSql2="select * from tab";
String[] sqls={userSql1,userSql2,anySql};
String[] workSheetNames={"User","Manager","Any"};
HSSFWorkbook wb = new HSSFWorkbook();
CellStyle style1 = wb.createCellStyle();
CellStyle style2 = wb.createCellStyle();
for(int i=0;i<lt;sqls.length;i++){
if(sqls[i]==null||sqls[i].length()==0){
continue;
}
HSSFSheet sheet = wb.createSheet(workSheetNames[i]);
Statement st = conn.createStatement();
System.out.println("--------------------------");
System.out.println(sqls[i]);
ResultSet rs = st.executeQuery(sqls[i]);
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
int rowCounter=0;
HSSFRow rowHeader = sheet.createRow(rowCounter);
for(int columnCounter=0; columnCounter<lt;numberOfColumns;columnCounter++){
rowHeader.createCell(columnCounter).setCellValue(rsMetaData.getColumnName(columnCounter+1));
setFilledColorStyle(rowHeader.getCell(columnCounter),style2);
}
sheet.setAutoFilter(org.apache.poi.ss.util.CellRangeAddress.valueOf("A1:"+ (Character.toString((char)( 65+numberOfColumns-1)))+"1"));
rowCounter++;
while(rs.next()) {
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow(rowCounter);
for(int columnCounter=0; columnCounter<lt;numberOfColumns;columnCounter++){
row.createCell(columnCounter).setCellValue(rs.getString(columnCounter+1));
setThinBorderStyle(row.getCell(columnCounter),style1);
}
rowCounter++;
}
rs.close();
st.close();
}
// Write the output
wb.write(out);
out.close();
conn.close();
System.out.println("Created Successfully");
}
public static void main(String[] args) throws Exception{
OutputStream out = new FileOutputStream("c:/admin.xls");
createExcel(" select * from tab", out);
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:XE";
String username = "system";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
//This method set the thin border style
private static void setThinBorderStyle(Cell cell, CellStyle style) {
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.RED.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.RED.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.RED.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.RED.getIndex());
cell.setCellStyle(style);
}
//This method set the dashed border style
private static void setDashedBorderStyle(Cell cell, CellStyle style) {
style.setBorderBottom(CellStyle.BORDER_DASHED);
style.setBottomBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderLeft(CellStyle.BORDER_DASHED);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(CellStyle.BORDER_DASHED);
style.setRightBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderTop(CellStyle.BORDER_DASHED);
style.setTopBorderColor(IndexedColors.GREEN.getIndex());
cell.setCellStyle(style);
} // This method set the dotted border style
private static void setFilledColorStyle(Cell cell, CellStyle style) {
// style.setFillBackgroundColor(new HSSFColor.YELLOW().getIndex());
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );
// style.setBorderLeft(CellStyle.BORDER_DOTTED);
// style.setLeftBorderColor(IndexedColors.BLUE.getIndex());
// style.setBorderRight(CellStyle.BORDER_DOTTED);
// style.setRightBorderColor(IndexedColors.BLUE.getIndex());
// style.setBorderTop(CellStyle.BORDER_DOTTED);
// style.setTopBorderColor(IndexedColors.BLUE.getIndex());
cell.setCellStyle(style);
}
}
- Indranil Nag
1
感谢您的分享。您还可以将“import org.apache.poi.ss.util.CellRangeAddress”添加到导入部分，并在“sheet.setAutoFilter”行内消除“org.apache.poi.ss.util。”。 - ian0411
4
sets Auto filter
sheet.setAutoFilter(CellRangeAddress.valueOf("A1:N1"));
- swamy
2
这项功能自apache POI的3.7-beta3-20100811版本起可用。
- endryha
0
当您的行存在且已填充时，使用第一行但最后一列设置自动筛选器
final XSSFRow firstRow = sheet.getRow(0);
final short lastCellNum = firstRow.getLastCellNum();
final XSSFCell lastCell = firstRow.getCell(lastCellNum - 1);
final String lastCellReference = lastCell.getReference();
sheet.setAutoFilter(CellRangeAddress.valueOf("A1:" + lastCellReference));
- Pipo
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
sheet.setAutoFilter(new CellRangeAddress(0, 3, 2, 4));。 - RorygetRow和getCol替换为getRowIndex和getColumnIndex。 - Matt Pennington