Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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|-------|
- //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);
- }
- }
- sets Auto filter
- sheet.setAutoFilter(CellRangeAddress.valueOf("A1:N1"));
- String lastCellReference = "A1";
- cell = row.createCell((short) columnCount);
- cell.setCellValue(rs.getInt(i));
- lastCellReference=cell.getReference();
- sheet1.setAutoFilter(CellRangeAddress.valueOf("A1:"+ lastCellReference));
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement