Apache POI HSSF, XSSF and EXCEL


Want to use HSSF and XSSF read and write spreadsheets in a hurry?
HSSF is for less than 2007 year Excel, XSSF is for more than 2007 year Excel.

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class testHSSF {   

    public static void main(String[] args) throws IOException {  

        Workbook wb = new HSSFWorkbook();

 CreationHelper helper = wb.getCreationHelper(); 

//Create Sheet  

Sheet sheet01 = wb.createSheet("HSSF_Sheet_01");

Sheet sheet02 = wb.createSheet("HSSF_Sheet_02");

Row row = null;

Cell cell = null;

  for(int i=0;i<60;i=i+2){

    row = sheet01.createRow(i);
    row.setHeightInPoints(20);//row.setHeight((short)500);//row.setZeroHeight(true);
            for(int j=0;j<25;j++){       
                //set each column size
                sheet01.autoSizeColumn(j+1, true);
                //create style
                CellStyle cellStyle = SummaryHSSF.createStyleCell(wb);
                //get cell of row
                cell = row.createCell(j);
                if(j==0){
 
                    cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    cellStyle.setFont(createFonts(wb));
                    cell.setCellStyle(cellStyle);                   
                    cell.setCellValue(true);
                }else if(j==1){
                    cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "#,##0.0000");                  
                    cellStyle.setFont(createFonts(wb));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(new Double(2008.2008));
                }else if(j==2){
                    cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);                    
                    cellStyle.setFont(createFonts(wb));
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(helper.createRichTextString("RichString"+i+j));                   
                }else if(j==3){
                    cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "MM-yyyy-dd");
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(new Date());
                }else if(j==24){
                    cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    cellStyle.setFont(createFonts(wb));
                    cell.setCellFormula("SUM(E"+(i+1)+":X"+(i+1)+")");                  
                }else{                  
                    cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    cellStyle = SummaryHSSF.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.ORANGE.getIndex(),CellStyle.SOLID_FOREGROUND);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(1);
                }
            }
        }

        OutputStream os = new FileOutputStream(new File("c://SummaryHSSF.xls"));
        wb.write(os);
        os.close();     
    }
    /**
     * border
     * @param wb
     * @return
     */
    public static CellStyle createStyleCell(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());        
        return cellStyle;
    }
    /**
     * set local
     * CellStyle.ALIGN_CENTER
     * CellStyle.VERTICAL_CENTER
     * @param cellStyle
     * @param halign
     * @param valign
     * @return
     */
    public static CellStyle setCellStyleAlignment(CellStyle cellStyle,short halign,short valign){
        
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return cellStyle;
    }
    /**
     * cell format
     * 如#,##0.00,m/d/yy(HSSFDataFormat,XSSFDataFormat)
     * @param cellStyle
     * @param fmt
     * @return
     */
    public static CellStyle setCellFormat(CreationHelper helper,CellStyle cellStyle,String fmt){
        
        cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));
        return cellStyle;
    }
    /**
     * beforeground and background colour
     * @param cellStyle
     * @param bg IndexedColors.ORANGE.getIndex();
     * @param fg IndexedColors.ORANGE.getIndex();
     * @param fp CellStyle.SOLID_FOREGROUND
     * @return
     */
    public static CellStyle setFillBackgroundColors(CellStyle cellStyle,short bg,short fg,short fp){
        //cellStyle.setFillBackgroundColor(bg);
        cellStyle.setFillForegroundColor(fg);
        cellStyle.setFillPattern(fp);
        return cellStyle;
    }
    /**
     * set font
     * @param wb
     * @return
     */
    public static Font createFonts(Workbook wb){
        //create Font object
        Font font = wb.createFont();
        //setFontName
        font.setFontName("Calibri");
        
        font.setColor(HSSFColor.BLUE.index);
        
        font.setItalic(true);
        
        font.setFontHeight((short)300);
        return font;
    }
}
//------------------------------
    public class ReadExcel { 
        public static void main(String[] args) throws Exception { 
            InputStream is = new FileInputStream(new File("D://testHSSF.xls")); 

            Workbook wb = WorkbookFactory.create(is); 
            Sheet sheet = wb.getSheetAt(0); 
            // 
            for(Row row : sheet){ 
                for(Cell cell : row){ 
                    
                    switch(cell.getCellType()){//Cell.CELL_TYPE_STRING 
                        case Cell.CELL_TYPE_BOOLEAN: 
                             
                            System.out.print(cell.getBooleanCellValue()+" "); 
                            break; 
                        case Cell.CELL_TYPE_NUMERIC: 
                            
                            if(DateUtil.isCellDateFormatted(cell)){ 
                                 
                                System.out.print(cell.getDateCellValue()+" "); 
                            }else{ 
                                 
                                System.out.print(cell.getNumericCellValue()+" "); 
                            } 
                            break; 
                        case Cell.CELL_TYPE_FORMULA: 
                            //formula 
                            System.out.print(cell.getCellFormula()+" "); 
                            break; 
                        case Cell.CELL_TYPE_STRING: 
                            //get String 
                            System.out.print(cell.getRichStringCellValue().toString()+" "); 
                            break;                   
                    } 
                } 
                System.out.println(""); 
            } 
        } 
    }

At Servlet
  1. // clear response  
  2. response.reset();  
  3. // set header of response 
  4. response.addHeader("Content-Disposition", "attachment;filename=excel00.xls");  
  5. // one way
    OutputStream out = new BufferedOutputStream(response.getOutputStream());  response.setContentType("application/octet-stream");  HSSFWorkbook workbook = new HSSFWorkbook();  HSSFSheet sheet = workbook.createSheet("sheet"); 
    ......
    workbook.write(out);
  6. // two way
    WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
    WritableSheet s = w.createSheet("Demo", 0);
           
       //..
     w.write();
     w.close();
     
  7. out.flush();  
  8. out.close(); 


Ref: p o i. a p a c h e . o r g / s p r e a d s h e e t / q u i c k - g u i d e . h t m l  
l a n g h u a 9 5 2 7 . i t e y e . c o m / b l o g / 3 8 8 0 0 5

评论

发表评论

此博客中的热门博文

XML, XSL, HTML

The code in this section is that an XSLT transform is used to translate XML input data to HTML output. Ref: https://docs.oracle.com/javase/tutorial/jaxp/xslt/transformingXML.html Note - The article1a.xsl , which is from in XSLT examples . The following code is for joy.  import java.awt.Desktop; import java.io.*; import java.net.URISyntaxException; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerConfigurationException; import javax.xml.transform.TransformerException; import javax.xml.transform.TransformerFactory; import javax.xml.transform.dom.DOMSource; import javax.xml.transform.stream.StreamResult; import javax.xml.transform.stream.StreamSource; // For write operation import org.w3c.dom.Document; import org.w3c.dom.Element; import org.xml.sax.SAXException; public class Stylizer { ...
阅读全文

Input in element.eleme.io

Input Input data using mouse or keyboard. Input is a controlled component, it always shows Vue binding value . Under normal circumstances, input event should be handled. Its handler should update component's binding value (or use v-model ). Otherwise, input box's value will not change. Do not support v-model modifiers. ¶ Basic usage < el-input placeholder = "Please input" v-model = "input" > </ el-input > < script > export default { data() { return { input : '' } } } </ script > Hide ¶ Disabled Disable the Input with the disabled attribute. < el-input placeholder = "Please input" v-model = "input" :disabled = "true" > </ el-input > < script > export default { data() { return { input : '' } } } </ script > ¶ Clearable Make the Input clearable with the...
阅读全文

Data URI是由RFC 2397 ACE

https://www.rfc-editor.org/rfc/rfc2397 http://codingpy.com/article/how-to-turn-your-browser-into-code-editor/ https://ace.c9.io/build/kitchen-sink.html https://c9.io/ https://angular-ui.github.io/ The "data" URL scheme Status of this Memo This document specifies an Internet standards track protocol for the Internet community, and requests discussion and suggestions for improvements. Please refer to the current edition of the "Internet Official Protocol Standards" (STD 1) for the standardization state and status of this protocol. Distribution of this memo is unlimited. Copyright Notice Copyright (C) The Internet Society (1998). All Rights Reserved. 1 . Abstract A new URL scheme, "data", is defined. It allows inclusion of small data items as "immediate" data, as if it had been included externally. 2 . Description Some applications that use URLs also have a need to embed (small...
阅读全文