Poi Operations common excel operations

Poi Operations common excel operations

I have written a similar article before. This time I re-wrote the code for adding some styles.

 

Package excel; import java. io. file; import java. io. fileInputStream; import java. io. fileNotFoundException; import java. io. fileOutputStream; import java. io. IOException; import java. lang. reflect. method; import java. util. list; import org. apache. poi. hssf. usermodel. HSSFFont; import org. apache. poi. hssf. usermodel. HSSFPalette; 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. hssf. util. cellRangeAddress; import org. apache. poi. ss. usermodel. cell; import org. apache. poi. ss. usermodel. cellStyle; import org. apache. poi. ss. usermodel. row; import org. apache. poi. ss. usermodel. sheet;/*** read data from excel/write data into excel with a header, the content of each column in the header corresponds to the attributes of the object class ** @ author nagsh **/public class ExcelManage {private HSSFWorkbook workbook = nul L;/*** determines whether the file exists. * @ param fileDir File path * @ return */public boolean fileExist (String fileDir) {boolean flag = false; file File = new file (fileDir); flag = File. exists (); return flag;}/*** checks whether the sheet of the object exists. * @ param fileDir file path * @ param sheetName table index name * @ return */public boolean sheetExist (String fileDir, String sheetName) {boolean flag = false; file file = new File (fileDir); if (file. exists () {// file storage Create a workbook try {workbook = new HSSFWorkbook (new FileInputStream (file); // Add a Worksheet (when the xls file generated when no sheet is added is opened, an error is reported) HSSFSheet sheet = workbook. getSheet (sheetName); if (sheet! = Null) flag = true;} catch (Exception e) {e. printStackTrace () ;}} else {// the file does not exist. flag = false;} return flag;}/*** create a new excel file. * @ param fileDir excel path * @ param sheetName the table index to be created * @ param titleRow the first row of excel is the table header */public void createExcel (String fileDir, String sheetName, string titleRow []) {// create a workbook = new HSSFWorkbook (); // Add a Worksheet (when the xls file generated when no sheet is added is opened, an error is reported.) Sheet sheet1 = workbook. creat ESheet (sheetName); // create a file FileOutputStream out = null; try {// Add the header Row row = workbook. getSheet (sheetName ). createRow (0); // create the first line for (int I = 0; I <titleRow. length; I ++) {Cell cell = row. createCell (I); cell. setCellValue (titleRow [I]);} out = new FileOutputStream (fileDir); workbook. write (out);} catch (Exception e) {e. printStackTrace ();} finally {try {out. close ();} catch (IOException e) {e. printStac KTrace () ;}}/ *** delete an object. * @ param fileDir File path */public boolean deleteExcel (String fileDir) {boolean flag = false; file File = new File (fileDir ); // determine whether a directory or file exists if (! File. exists () {// returns false return flag;} else {// determines whether it is a file if (file. isFile () {// call the file deletion method when the file is used. delete (); flag = true;} return flag ;} /*** merge cells ** @ param workbook * @ param fileDir * @ param sheetName * @ param rowStart merge cells start row number starting from 1 * @ param rowEnd merge cells end row number starting from 1 * @ param ColumnStart merge cells start column number from 1 * @ param ColumnEnd merge cells end column number from 1 */public void mergedCell (HSSFWorkbook workbook, String fileDir, String sheetName, int rowStart, int rowEnd, int ColumnStart, int ColumnEnd) {try {FileOutputStream out = null; HSSFSheet sheet = workbook. getSheet (sheetName); sheet. addMergedRegion (new CellRangeAddress (short) rowStart-1, (short) rowEnd-1, (short) ColumnStart-1); out = new FileOutputStream (fileDir); workbook. write (out);} catch (FileNotFoundException e) {e. printStackTrace ();} ca Tch (IOException e) {e. printStackTrace ();}} /*** delete a row [permanently delete] * @ param workbook * @ param fileDir * @ param sheetName * @ param rowNum row number */public void removeRow (HSSFWorkbook workbook, String fileDir, string sheetName, int rowNum) {try {// stream FileOutputStream out = null; HSSFSheet sheet = workbook. getSheet (sheetName); int rowCount = sheet. getLastRowNum (); sheet. shiftRows (rowNum, rowCount,-1); // move up out = new FileOutputStream (fileDir); workbook. write (out);} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();}} /*** Delete row [delete data] * @ param workbook * @ param fileDir * @ param sheetName * @ param rowNum row number */public void removeRowData (HSSFWorkbook workbook, String fileDir, string sheetName, int rowNum) {try {// stream FileOutputStream out = null; HSSFSheet sheet = workbook. ge TSheet (sheetName); Row row = sheet. getRow (rowNum-1); sheet. removeRow (row); out = new FileOutputStream (fileDir); workbook. write (out);} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace () ;}}/*** write * @ param fileDir file path * @ param sheetName table Index * @ param object */public void writeToExcel (String fileDir, string sheetName, Object object) {// create workbookFi Le file = new File (fileDir); try {workbook = new HSSFWorkbook (new FileInputStream (file);} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();} // stream FileOutputStream out = null; HSSFSheet sheet = workbook. getSheet (sheetName); // get the total number of rows in the table int rowCount = sheet. getLastRowNum () + 1; // you need to add one // get the number of columns in the table header int columnCount = sheet. getRow (0 ). getLastCellNum (); try {Row r Ow = sheet. createRow (rowCount); // The latest row to be added // obtain the object field through reflection, insert the corresponding header // obtain the class object Class _ = object of the object. getClass (); // get the object HSSFRow titleRow = sheet. getRow (0); if (titleRow! = Null) {for (int columnIndex = 0; columnIndex <columnCount; columnIndex ++) {// traverse the header String title = titleRow. getCell (columnIndex ). toString (). trim (). toString (). trim (); String UTitle = Character. toUpperCase (title. charAt (0) + title. substring (1, title. length (); // uppercase; String methodName = get + UTitle; Method = class _. getDeclaredMethod (methodName); // set the method to be executed String data = method. invoke (object ). toString (); // execute the get method, that is, the data Cell to be inserted is cell = row. createCell (columnIndex); cell. setCellValue (data) ;}} out = new FileOutputStream (fileDir); workbook. write (out);} catch (Exception e) {e. printStackTrace ();} finally {try {out. close ();} catch (IOException e) {e. printStackTrace ();}}} /*** add data to a row * @ param workbook this parameter is used to ensure the consistency of the workbook * @ param fileDir file path * @ param sheetName Index * @ param rowNumber the row to be added * @ param datas the added data [column, data column number-data * @ param style */public void writeOneRow (HSSFWorkbook workbook, String fileDir, String sheetName, int rowNumber, List
 
  
Datas) {try {// stream FileOutputStream out = null; HSSFSheet sheet = workbook. getSheet (sheetName); sheet. autoSizeColumn (1, true); // set the adaptive width // The Row row to be added last = sheet. getRow (rowNumber-1); if (row = null) {row = sheet. createRow (rowNumber-1);} // write data for (PoiCell data: datas) {Cell cell = row. createCell (data. getColumn ()-1); // sets the cell style. setCellStyle (data. getStyle (); // sets the data cell. setCellValue (data. getData ();} out = new FileOu TputStream (fileDir); workbook. write (out);} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace () ;}}/*** write the hexadecimal color code into the style to set the color * @ param style to ensure that the style is uniform * @ param color: 66 FFDD * @ param index 1-48 cannot be used repeatedly * @ return */public CellStyle getColorStyle (CellStyle style, String color, short index) {if (color! = & Color! = Null) {// convert to the RGB code int r = Integer. parseInt (color. substring (), 16); // convert to hexadecimal int g = Integer. parseInt (color. substring (2, 4), 16); int B = Integer. parseInt (color. substring (), 16); // custom cell color HSSFPalette = workbook. getCustomPalette (); palette. setColorAtIndex (short) index, (byte) r, (byte) g, (byte) B); style. setFillPattern (CellStyle. SOLID_FOREGROUND); style. setFillForegroundColor (index);} return style;}/*** set the border * @ param style * @ return */public CellStyle setBorder (CellStyle style) {style. setBorderBottom (CellStyle. BORDER_THIN); // the style of the lower border. setBorderLeft (CellStyle. BORDER_THIN); // style on the left border. setBorderTop (CellStyle. BORDER_THIN); // upper border style. setBorderRight (CellStyle. BORDER_THIN); // return style on the right border;}/*** set the font * @ param style * @ return */public CellStyle setFont (CellStyle style) {HSSFFont font = workbook. createFont (); font. setFontHeightInPoints (short) 12); font. setFontName (); font. setBold (true); style. setFont (font); return style ;}}
 

Add a code to set automatic filtering:

 

CellRangeAddress c = (CellRangeAddress) CellRangeAddress. valueOf ("A2: L2 ");
Sheet. setAutoFilter (c );

This not only uses the HSSF package, but also uses this

Import org. apache. poi. ss. util. CellRangeAddress;