Example usage for org.apache.poi.xssf.usermodel XSSFSheet autoSizeColumn

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet autoSizeColumn

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. autoSizeColumn

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet autoSizeColumn.

Prototype

@Override
public void autoSizeColumn(int column)

Source Link

Document

Adjusts the column width to fit the contents.

Usage

From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java

public void dumpFormatErrorToExcelFile(ArrayList<ErrorModel> get_errormodelList)
        throws FileNotFoundException, IOException {
    /***// w  ww  .ja  v a2 s .  c o m
        Dump the error list into Excel file.***/
    XSSFWorkbook ErrorWorkbook = new XSSFWorkbook();
    XSSFSheet ErrorSheet;
    for (int i = 0; i < get_errormodelList.size(); i++) {
        int index = ErrorWorkbook.getSheetIndex(get_errormodelList.get(i).sheet_name);
        if (index == -1) {
            ErrorSheet = ErrorWorkbook.createSheet(get_errormodelList.get(i).sheet_name);
            XSSFRow totalError = ErrorSheet.createRow(0);
            XSSFRow totalWarning = ErrorSheet.createRow(1);
            CreaateHeaderOfErrorList(ErrorWorkbook, totalError.createCell(0), "Total Errors");
            CreaateHeaderOfErrorList(ErrorWorkbook, totalWarning.createCell(0), "Total Warnings");
            if ((get_errormodelList.get(i).error_level).equals("Warning")) {
                totalWarning.createCell(1).setCellValue(1);
            } else
                totalWarning.createCell(1).setCellValue(0);
            if ((get_errormodelList.get(i).error_level).equals("Error")) {
                totalError.createCell(1).setCellValue(1);
            } else
                totalError.createCell(1).setCellValue(0);

            ErrorSheet.createRow(2);
            XSSFRow headerrow = ErrorSheet.createRow(3);
            //create header of every sheet
            Cell Header_referenceCell = headerrow.createCell(0);
            CreaateHeaderOfErrorList(ErrorWorkbook, Header_referenceCell, "Cell Ref");
            Cell Header_sheetname = headerrow.createCell(1);
            CreaateHeaderOfErrorList(ErrorWorkbook, Header_sheetname, "Sheet Name");
            Cell Header_ErrorDesc = headerrow.createCell(2);
            CreaateHeaderOfErrorList(ErrorWorkbook, Header_ErrorDesc, "Error Description");
            Cell Header_ErrorLevel = headerrow.createCell(3);
            CreaateHeaderOfErrorList(ErrorWorkbook, Header_ErrorLevel, "Error Level");
            XSSFRow row = ErrorSheet.createRow(4);
            row = ErrorSheet.createRow(5);

            CreaateStyleOfErrorList(ErrorWorkbook, row, get_errormodelList.get(i).cell_ref,
                    get_errormodelList.get(i).sheet_name, get_errormodelList.get(i).error_desc,
                    get_errormodelList.get(i).error_level);
            ErrorSheet.autoSizeColumn(0);
            ErrorSheet.autoSizeColumn(1);
            ErrorSheet.autoSizeColumn(2);
            ErrorSheet.autoSizeColumn(3);
        } else {
            printinfo(ErrorWorkbook, get_errormodelList.get(i).cell_ref, get_errormodelList.get(i).sheet_name,
                    get_errormodelList.get(i).error_desc, get_errormodelList.get(i).error_level);
        }
    }
    setColorInfoMetaData(ErrorWorkbook);
    try (FileOutputStream ErrorOutputStream = new FileOutputStream(
            "C:/Users/Dharam/Desktop/DIMT_NEW_CODE/ErrorSheet.xlsx")) {
        ErrorWorkbook.write(ErrorOutputStream);
    }
}

From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java

private static void setColorInfoMetaData(XSSFWorkbook ErrorWorkbook) {
    //Set Colour used information on first sheet.
    XSSFSheet setInfoSheet = ErrorWorkbook.getSheetAt(0);
    XSSFRow colourInfoRow;/*from   w w  w.  j  av a 2s  .c o m*/
    XSSFRow errorColourRow;
    XSSFRow warningColourRow;
    if (setInfoSheet.getPhysicalNumberOfRows() > 5) {
        colourInfoRow = setInfoSheet.getRow(5);
    } else {
        colourInfoRow = setInfoSheet.createRow(5);
    }
    Cell colorInfoCell = colourInfoRow.createCell(6);
    Cell RGBCell = colourInfoRow.createCell(7);
    CreaateHeaderOfErrorList(ErrorWorkbook, colorInfoCell, "Used Color");
    CreaateHeaderOfErrorList(ErrorWorkbook, RGBCell, "RGB Value");
    setInfoSheet.autoSizeColumn(6);
    setInfoSheet.autoSizeColumn(7);
    if (setInfoSheet.getPhysicalNumberOfRows() > 6) {
        errorColourRow = setInfoSheet.getRow(6);
    } else {
        errorColourRow = setInfoSheet.createRow(6);
    }
    if (setInfoSheet.getPhysicalNumberOfRows() > 7) {
        warningColourRow = setInfoSheet.getRow(7);
    } else {
        warningColourRow = setInfoSheet.createRow(7);
    }
    //error color style
    XSSFCellStyle errorStyle = ErrorWorkbook.createCellStyle();
    errorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    errorStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(225, 171, 171)));
    errorColourRow.createCell(6).setCellStyle(errorStyle);
    errorColourRow.getCell(6).setCellValue("Error");
    errorColourRow.createCell(7).setCellValue("225, 171, 171");
    //warning color style
    XSSFCellStyle warningStyle = ErrorWorkbook.createCellStyle();
    warningStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    warningStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(155, 194, 230)));
    warningColourRow.createCell(6).setCellStyle(warningStyle);
    warningColourRow.getCell(6).setCellValue("Warning");
    warningColourRow.createCell(7).setCellValue("155, 194, 230");
}

From source file:listfiles.ListFiles.java

/**
 * @param args the command line arguments
 *//*from w w  w. j a  v  a  2s. c o m*/
public static void main(String[] args) {
    // TODO code application logic here
    BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
    String folderPath = "";
    String fileName = "DirectoryFiles.xlsx";

    try {
        System.out.println("Folder path :");
        folderPath = reader.readLine();
        //System.out.println("Output File Name :");
        //fileName = reader.readLine();

        XSSFWorkbook wb = new XSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream(folderPath + "\\" + fileName);
        XSSFSheet sheet1 = wb.createSheet("Files");
        int row = 0;
        Stream<Path> stream = Files.walk(Paths.get(folderPath));
        Iterator<Path> pathIt = stream.iterator();
        String ext = "";

        while (pathIt.hasNext()) {
            Path filePath = pathIt.next();
            Cell cell1 = checkRowCellExists(sheet1, row, 0);
            Cell cell2 = checkRowCellExists(sheet1, row, 1);
            row++;
            ext = FilenameUtils.getExtension(filePath.getFileName().toString());
            cell1.setCellValue(filePath.getFileName().toString());
            cell2.setCellValue(ext);

        }
        sheet1.autoSizeColumn(0);
        sheet1.autoSizeColumn(1);

        wb.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    System.out.println("Program Finished");
}

From source file:localization.checkURL.java

public static void check(String desktopFile, String serverFile, String inputFolder) {

    try {/*from  w ww. j  ava 2  s.  co  m*/
        String desktopFolder = desktopFile.substring(desktopFile.lastIndexOf("\\") + 1, desktopFile.length());
        desktop = desktopFolder.substring(0, desktopFolder.indexOf("_"));
        String serverFolder = serverFile.substring(serverFile.lastIndexOf("\\") + 1, serverFile.length());
        server = serverFolder.substring(0, serverFolder.indexOf("_"));

        pubList = new ArrayList<>();
        searchFile(inputFolder);
        String parFolder = inputFolder.substring(0, inputFolder.lastIndexOf("\\"));
        HOName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.lastIndexOf("\\") + 5);
        lang = inputFolder.substring(inputFolder.lastIndexOf("\\") + 1, inputFolder.length());
        outputFileName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.length());
        outputFilePath = parFolder + "\\" + outputFileName + "_" + lang + ".xlsx";

        valueArray = new String[pubList.size() + 1][6];
        valueArray[0][0] = "Language";
        valueArray[0][1] = "HO#";
        valueArray[0][2] = "Publication Name";
        valueArray[0][3] = "Type";
        valueArray[0][4] = "Topic Name";
        valueArray[0][5] = "URL";
        for (int i = 0; i < pubList.size(); i++) {
            String fullPath = pubList.get(i);
            valueArray[i + 1][0] = lang.toUpperCase().trim();
            valueArray[i + 1][1] = HOName.trim();
            valueArray[i + 1][4] = fullPath.substring(fullPath.lastIndexOf("\\") + 1, fullPath.length());
            if (fullPath.contains("\\topic\\")) {
                valueArray[i + 1][3] = "topic";
                valueArray[i + 1][2] = fullPath
                        .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\topic\\")).trim();
            } else if (fullPath.contains("\\map\\")) {
                valueArray[i + 1][3] = "map";
                valueArray[i + 1][2] = fullPath
                        .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\map\\")).trim();
            }
        }

        /* for(int i = 0; i < valueArray.length; i++){
            for(int j = 0; j < valueArray[i].length; j++){
                System.out.print(valueArray[i][j] + ",");
            }
            System.out.println();
        }*/

        File inputDesktopFile = new File(desktopFile);
        File inputServerFile = new File(serverFile);
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputDesktopFile));
        XSSFSheet mysheet = workbook.getSheetAt(0);
        int desktopRowNum = mysheet.getLastRowNum();

        XSSFWorkbook serverWorkbook = new XSSFWorkbook(new FileInputStream(inputServerFile));
        XSSFSheet serverSheet = serverWorkbook.getSheetAt(0);
        int serverRowNum = serverSheet.getLastRowNum();

        for (int k = 1; k < valueArray.length; k++) {
            //System.out.println(valueArray[k][3]);
            if (valueArray[k][3].equals("topic") && (!valueArray[k][4].trim().startsWith("cfg"))) {
                //System.out.println(k + " " +valueArray[k][3]);
                String source = valueArray[k][4].trim();
                for (int i = 1; i < desktopRowNum + 1; i++) {
                    Row desktopRow = mysheet.getRow(i);
                    if (desktopRow != null) {
                        String targetString = desktopRow.getCell(2).getStringCellValue().trim();
                        if (source.contains(targetString)) {
                            String desktopURL = desktopRow.getCell(0).getStringCellValue().trim();
                            desktopURL = desktopURL.replace(".com/en\\", ".com/" + lang.toLowerCase() + "\\");
                            desktopURL = desktopURL.replace("http://" + desktop, "http://" + desktop + "uat");
                            valueArray[k][5] = desktopURL;
                        }
                    }
                }

                for (int j = 0; j < serverRowNum + 1; j++) {
                    Row serverRow = serverSheet.getRow(j);
                    if (serverRow != null) {
                        String targetString1 = serverRow.getCell(2).getStringCellValue().trim();
                        if (source.contains(targetString1)) {
                            String serverURL = serverRow.getCell(0).getStringCellValue().trim();
                            serverURL = serverURL.replace("/en\\", "/" + lang.toLowerCase() + "\\");
                            serverURL = serverURL.replace("http://" + server, "http://" + server + "uat");
                            // System.out.println(serverURL);
                            if (valueArray[k][5] != null) {
                                valueArray[k][5] = valueArray[k][5] + "\n" + serverURL;
                            } else {
                                valueArray[k][5] = serverURL;
                            }

                        }
                    }
                }
            }

            XSSFWorkbook outputworkbook = new XSSFWorkbook();
            XSSFSheet outputsheet = outputworkbook.createSheet("sheet1");
            XSSFCellStyle outputstyle = outputworkbook.createCellStyle();
            outputstyle.setWrapText(true);
            int outputRowNum = 0;
            int outputCellNum = 0;
            for (int i = 0; i < valueArray.length; i++) {
                Row outputRow = outputsheet.createRow(outputRowNum++);
                for (int j = 0; j < valueArray[1].length; j++) {
                    Cell outputCell = outputRow.createCell(outputCellNum++);
                    if (valueArray[i][j] != null) {
                        outputCell.setCellValue(valueArray[i][j]);
                    } else {
                        outputCell.setCellValue("N/A");
                    }
                    if (j == 5) {
                        //outputsheet.autoSizeColumn(4);
                        outputCell.setCellStyle(outputstyle);
                    }
                }
                outputCellNum = 0;
            }
            outputsheet.autoSizeColumn(2);
            outputsheet.autoSizeColumn(4);
            outputsheet.autoSizeColumn(5);
            FileOutputStream out = new FileOutputStream(new File(outputFilePath));
            outputworkbook.write(out);
            out.close();
        }
    } catch (Exception e) {
        try {
            File file = new File(userDir + "\\log.txt");
            if (!file.exists()) {
                file.createNewFile();
            }
            FileWriter fw = new FileWriter(file.getAbsoluteFile());
            BufferedWriter bw = new BufferedWriter(fw);
            bw.write(e.getMessage());
            bw.write(e.getLocalizedMessage());
            bw.close();
            fw.close();
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    }
}

From source file:lospolloshermanos.SalesTablePan.java

public void PrintSales() {

    if (items != null && categories != null) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        //Create a blank sheet
        XSSFSheet itemssheet = workbook.createSheet("Item-wise");
        XSSFSheet categorysheet = workbook.createSheet("Cateogry-wise");
        XSSFRow row;//from  ww  w.j  a v  a 2 s.  c o m
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        Cell cell;
        row = itemssheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("Meal Name");
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("Quantity");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue("Sub Total");
        font.setBold(false);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 2; i <= no_of_items + 1; i++) {
            try {
                row = itemssheet.createRow(i);
                String QtyTot = items.getString("QtyTot");
                String SubTot = items.getString("SubTot");
                cell = row.createCell(0);
                cell.setCellValue(items.getString("MName"));
                cell = row.createCell(1);
                if (QtyTot != null)
                    cell.setCellValue(QtyTot);
                else
                    cell.setCellValue("0");
                cell = row.createCell(2);
                if (SubTot != null)
                    cell.setCellValue(SubTot);
                else
                    cell.setCellValue("0.00");
                items.next();
            } catch (SQLException ex) {
                Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        row = itemssheet.createRow(no_of_items + 3);
        font.setBold(true);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("Grand Total");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(GrandTot + "");

        row = categorysheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("Category Name");
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("No of items sold");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue("Sub Total");
        font.setBold(false);
        for (int i = 2; i <= no_of_cats + 1; i++) {
            try {
                row = categorysheet.createRow(i);

                cell = row.createCell(0);
                cell.setCellValue(categories.getString("CName"));
                cell = row.createCell(1);
                String QtyTot = categories.getString("QtyTot");
                String SubTot = categories.getString("SubTot");
                if (QtyTot != null)
                    cell.setCellValue(QtyTot);
                else
                    cell.setCellValue("0");
                cell = row.createCell(2);
                if (SubTot != null)
                    cell.setCellValue(SubTot);
                else
                    cell.setCellValue("0.00");
                categories.next();
            } catch (SQLException ex) {
                Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        row = categorysheet.createRow(no_of_cats + 3);
        font.setBold(true);
        cell = row.createCell(0);
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("Grand Total");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(GrandTot + "");
        font.setBold(false);

        itemssheet.autoSizeColumn(0);
        itemssheet.autoSizeColumn(1);
        itemssheet.autoSizeColumn(2);
        categorysheet.autoSizeColumn(0);
        categorysheet.autoSizeColumn(1);
        categorysheet.autoSizeColumn(2);
        try {
            items.first();
            categories.first();
            FileOutputStream out = new FileOutputStream(
                    new File("C:/Program Files/RMS/Sales_from_" + Date1 + "_to_" + Date2 + ".xlsx"));
            workbook.write(out);
            out.close();
        } catch (Exception e) {
        }
    }
}

From source file:Main.Database.java

/**
 * @param dbTable - the database table which contains job results from a
 * particular job board.//w  w  w .j  a  v a 2s .  c om
 * @param excelSheetIndex - the sheet index on the Excel File which will
 * house the results from the database.
 *
 * This method appends results from the specific database table onto the
 * index of the excel sheet.
 */
/*
 public void appendDBTableToExcel(String dbTable, int excelSheetIndex) {
 try {
 connect = connectDatabase();
 String sqlQuery = "Select *from " + dbTable;
 // create the java statement
 selectStmt = connect.prepareStatement(sqlQuery);
 resultSet = selectStmt.executeQuery();
 //call upon the excel file.
 excelFile = new File("C:\\Users\\jason\\Desktop\\Job Scraper\\JobScraper\\src\\main\\java\\Main\\links.xlsx");
 excelInputStream = new FileInputStream(excelFile);
 Workbook workbook = create(excelInputStream);
        
 CreationHelper createHelper = workbook.getCreationHelper();
 Sheet sheet = workbook.getSheetAt(excelSheetIndex);
 excelOutputStream = new FileOutputStream(excelFile);
 //set link style to blue
 CellStyle hlinkstyle = workbook.createCellStyle();
 Font hlinkfont = workbook.createFont();
 hlinkfont.setUnderline(XSSFFont.U_SINGLE);
 hlinkfont.setColor(HSSFColor.BLUE.index);
 hlinkstyle.setFont(hlinkfont);
        
 // iterate through the java database,grabbing the details of the job.
 System.out.println("I can see contents of database");
 while (resultSet.next()) {
 this.title = resultSet.getString("title");
 this.link = resultSet.getString("link");
 this.date = resultSet.getString("date");
 System.out.println(this.title + " / " + this.link + " / " + this.date );
 //append database Information onto Excel
 Row row = sheet.createRow(rowNumber);
 Cell titleCell = row.createCell(0);
 titleCell.setCellValue(title);
 Cell linkCell = row.createCell(1);
 linkCell.setCellValue(link);
 Cell dateCell = row.createCell(2);
 dateCell.setCellValue(date);
        
 //Make the link a clickable blue url.
 XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
 hyperLink.setAddress(link);
 linkCell.setHyperlink(hyperLink);
 linkCell.setCellStyle(hlinkstyle);
 rowNumber++;
 }
 //autosizes the columns for clarity
 for (int i = 0; i < 3; i+=2) {
 sheet.autoSizeColumn(i);
 }
 selectStmt.close();
 excelOutputStream = new FileOutputStream(excelFile);
 workbook.write(excelOutputStream);
 excelOutputStream.close();
            
            
 System.out.println("Printed out " + dbTable);
 } catch (SQLException ex) {
 getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
 } catch (FileNotFoundException ex) {
 getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
 } catch (IOException ex) {
 getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
 } catch (InvalidFormatException ex) {
 Logger.getLogger(IndeedScraper.class.getName()).log(Level.SEVERE, null, ex);
 }
 }
 */
public void appendDBTableToExcel(String dbTable) {
    rowNumber = 0;
    try {
        connect = connectDatabase();
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("Select * from " + dbTable);

        File file = new File("exceldatabase.xlsx");
        if (file.exists()) {
            closeDBSession();
            callExistingExcel(dbTable);
        } else {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet spreadsheet = workbook.createSheet(dbTable);
            while (resultSet.next()) {
                XSSFRow row = spreadsheet.createRow(rowNumber);
                XSSFCell titleCell = row.createCell(0);
                titleCell.setCellValue(resultSet.getString("title"));
                XSSFCell linkCell = row.createCell(1);
                linkCell.setCellValue(resultSet.getString("link"));
                XSSFCell dateCell = row.createCell(2);
                dateCell.setCellValue(resultSet.getString("date"));
                System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link")
                        + resultSet.getString("date"));
                rowNumber++;
            }
            //autosizes the columns for clarity
            for (int i = 0; i < 3; i += 2) {
                spreadsheet.autoSizeColumn(i);
            }
            FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("exceldatabase.xlsx written successfully");
        }
    } catch (SQLException ex) {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:Main.Database.java

private void callExistingExcel(String dbTable) throws FileNotFoundException, IOException, SQLException {
    rowNumber = 0;/*from  w w w. j  a va  2s . c  o  m*/
    connect = connectDatabase();
    Statement statement = connect.createStatement();
    ResultSet resultSet = statement.executeQuery("Select * from " + dbTable);

    File file = new File("exceldatabase.xlsx");
    FileInputStream fIP = new FileInputStream(file);
    //Get the workbook instance for XLSX file 
    XSSFWorkbook workbook = new XSSFWorkbook(fIP);
    try {
        if (file.isFile() && file.exists()) {
            System.out.println("openworkbook.xlsx file open successfully.");
            XSSFSheet spreadsheet = workbook.createSheet(dbTable);
            while (resultSet.next()) {
                XSSFRow row = spreadsheet.createRow(rowNumber);
                XSSFCell titleCell = row.createCell(0);
                titleCell.setCellValue(resultSet.getString("title"));
                XSSFCell linkCell = row.createCell(1);
                linkCell.setCellValue(resultSet.getString("link"));
                XSSFCell dateCell = row.createCell(2);
                dateCell.setCellValue(resultSet.getString("date"));
                System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link")
                        + resultSet.getString("date"));
                rowNumber++;
            }
            //autosizes the columns for clarity
            for (int i = 0; i < 3; i += 2) {
                spreadsheet.autoSizeColumn(i);
            }
            FileOutputStream out = new FileOutputStream("exceldatabase.xlsx");
            workbook.write(out);
            out.close();
            System.out.println("Successfully written");
        } else {
            System.out.println("Error to open openworkbook.xlsx file.");
        }
    } catch (IllegalArgumentException ex) {
    }
}

From source file:mvjce.Excel_operations.java

public static void insert_internals(XSSFWorkbook workbook, XSSFSheet spreadsheet) {
    try {//from  ww w.jav  a  2s .  co m
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Sample_data", "root", "root");
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet detail = st.executeQuery(
                "select s.USN,s.Name,i.sub1_int1,i.sub1_int2,i.sub1_int3,i.sub2_int1,i.sub2_int2,i.sub2_int3,i.sub3_int1,i.sub3_int2,i.sub3_int3,i.sub4_int1,i.sub4_int2,i.sub4_int3,\n"
                        + "i2.sub5_int1,i2.sub5_int2,i2.sub5_int3,i2.sub6_int1,i2.sub6_int2,i2.sub6_int3,i2.sub7_int1,i2.sub7_int2,i2.sub7_int3,i2.sub8_int1,i2.sub8_int2,i2.sub8_int3\n"
                        + "from internals as i\n" + "join Student_info as s\n" + "on i.USN=s.USN\n"
                        + "join internals2 as i2\n" + "on i2.USN=s.USN\n" + "where s.Class='" + Writesheet.sec
                        + "' and s.semester=" + Writesheet.sem_string + ";");
        int i = 1, j = 6;
        XSSFFont font = workbook.createFont();
        font.setFontName("Arial");
        font.setBold(true);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);
        style.setFont(font);
        XSSFCell cell;
        while (detail.next()) {
            XSSFRow row = spreadsheet.createRow((short) j);
            cell = (XSSFCell) row.createCell((short) 0);
            cell.setCellValue(i);
            cell.setCellStyle(style);
            for (int k = 1; k <= 26; k++) {
                cell = (XSSFCell) row.createCell((short) k);
                cell.setCellValue(detail.getString(k));
                cell.setCellStyle(style);
            }
            i++;
            j++;
        }
        spreadsheet.autoSizeColumn(2);
        spreadsheet.autoSizeColumn(1);
    } catch (Exception e) {
        System.out.println(e);
    }
}

From source file:net.mcnewfamily.rmcnew.writer.AbstractXlsxWriter.java

License:Open Source License

protected void writeRecords(Records records, String sheetName) {
    if (records != null && !records.isEmpty()) {
        XSSFSheet finalManifestSheet = records.toSheetEssence(sheetName).toXSSFSheet(workbook);
        for (int columnIndex = 0; columnIndex < 13; columnIndex++) {
            finalManifestSheet.autoSizeColumn(columnIndex);
        }//from  w  ww  .  j  av  a 2  s.c  o m
    } else {
        throw new IllegalArgumentException("Cannot create XLSX sheet from null or empty Records!");
    }
}

From source file:net.mcnewfamily.rmcnew.writer.AbstractXlsxWriter.java

License:Open Source License

protected void writeSummaryTable(Manifest manifest, String sheetName) {
    if (manifest != null) {
        XSSFSheet manifestCountsSheet = manifest.toSheetEssence(sheetName).toXSSFSheet(workbook);
        for (int columnIndex = 0; columnIndex < 4; columnIndex++) {
            manifestCountsSheet.autoSizeColumn(columnIndex);
        }/*from   w  w w .j av a2s .c  o m*/
    } else {
        throw new IllegalArgumentException("Cannot create XLSX sheet from null or empty manifest!");
    }
}