Java操作ExcelのPOI:javaはexcelファイルを読み、印刷設定を書きます.


Java操作ExcelのPOI:javaはexcelファイルを読み、印刷設定を書きます.
POIのjarパッケージのダウンロードアドレス:http://poi.apache.org/download.html
注意:プロジェクトにpoi 4.0.1のjarパッケージを導入し、他のバージョンには間違いがないことを確保していません.
HSSFCelSteyle.VERICAL_CENTERでは定義されていない解決方法を参考にしてください.https://blog.csdn.net/weixin_4320921/articale/detail/86522238.HSSFCelSteyle.ALIGN_LEFT、CENTER、RIGHTなどの定義されていない解決方法は参考にしてください.https://blog.csdn.net/weixin_4320921/articale/detail/86522115.HSSFCel.Cell.Cell_TYPE_STRING、BOOLIEAN、NUMERICなど定義されていない解決方法は参考にしてください.https://blog.csdn.net/weixin_4320921/articale/detail/86519522.
1、javaはexcelファイルとページに対して印刷設定を書きます.
import static org.apache.poi.hssf.usermodel.HeaderFooter.fontSize;

import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
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.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class Demo_Write {
     
	/**
	 *   poi     excel  
	 * 
	 * @author lingtouyang csdn   ▄┻┳═  ☆
	 *
	 */
	public static void main(String[] args) throws Exception {
     
		//      
		Workbook wb = new HSSFWorkbook(); //          
		//   sheet 
		Sheet sheet1 = wb.createSheet("   sheet "); //      sheet 
		Sheet sheet2 = wb.createSheet("   sheet "); //      sheet 
		//      
		Row row1 = sheet1.createRow(0); //      
		//      
		Cell cell1 = row1.createCell(0); //            ,   
		Cell cell2 = row1.createCell(1); //            
		Cell cell3 = row1.createCell(2); //            
		Cell cell4 = row1.createCell(3); //            
		Cell cell5 = row1.createCell(4); //            
		Cell cell6 = row1.createCell(5); //            
		Cell cell7 = row1.createCell(6); //            

		//       
		cell1.setCellValue(1.2); //                   (  \     )
		cell2.setCellValue(false); //                   
		cell3.setCellValue("       "); //                    
		//             
		cell4.setCellValue(new Date()); //            new Date()    ( 1970.1.1       )
		cell5.setCellValue(Calendar.getInstance()); //          Calendar.getInstance()      
		//                        
		CreationHelper creationHelper = wb.getCreationHelper(); // wb    ,          
		CellStyle cellStyle = wb.createCellStyle(); //         
		cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));//       

		cell6.setCellValue(new Date()); //          new Date()      
		cell6.setCellStyle(cellStyle); //                

		cell7.setCellValue(Calendar.getInstance()); //          Calendar.getInstance()      
		cell7.setCellStyle(cellStyle); //                

		//      
		Row row2 = sheet1.createRow(1); //      
		//     
		row2.setHeightInPoints(30);
		//      
		Cell cell2_1 = row2.createCell(0); //            ,   
		Cell cell2_2 = row2.createCell(1); //            
		Cell cell2_3 = row2.createCell(2); //            
		Cell cell2_5 = row2.createCell(4); //            
		Cell cell2_6 = row2.createCell(5); //            
		Cell cell2_7 = row2.createCell(6); //            
		//      
		//          ,     
		cell2_1.setCellValue("    ");//          
		CellStyle cellStyle2 = wb.createCellStyle();//        
		cellStyle2.setAlignment(HorizontalAlignment.LEFT);//              ,      ,    
		cellStyle2.setVerticalAlignment(VerticalAlignment.TOP);//            ,       ,    
		cell2_1.setCellStyle(cellStyle2);//   :        
		//        
		cellStyle2.setBorderBottom(BorderStyle.DASH_DOT);//      ,    
		cellStyle2.setBottomBorderColor(IndexedColors.GREEN.getIndex());//         ,    
		//            
		cell2_2.setCellValue("   ");
		CellStyle cellStyle3=wb.createCellStyle();
		cellStyle3.setFillPattern(FillPatternType.SPARSE_DOTS);//        
		cellStyle3.setFillForegroundColor(IndexedColors.GOLD.getIndex());//        
		cell2_2.setCellStyle(cellStyle3);
		//     
		cell2_3.setCellValue("           ");
		sheet1.addMergedRegion(new CellRangeAddress(//                      :
				1,	//   
				1,	//   
				2, 	//   
				3	//   
				));
		//       
		Font font=wb.createFont();	//         
		font.setFontHeightInPoints((short) 30); 	//      
		font.setFontName("  ");		//    
		font.setItalic(true);		//    
		font.setBold(false); 		//    
		font.setColor(IndexedColors.BLUE.getIndex()); 	//      
		font.setStrikeout(true);	//   
		
		CellStyle cellStyle4=wb.createCellStyle();//    
		cellStyle4.setFont(font);//          
		cell2_5.setCellValue("      ");
		cell2_5.setCellStyle(cellStyle4);//         
		//       , excel  Alt+Enter  
		CellStyle cellStyle5=wb.createCellStyle();
		cellStyle5.setWrapText(true);//      
		cell2_6.setCellValue("    
!"
); cell2_6.setCellStyle(cellStyle5); // CellStyle cellStyle6=wb.createCellStyle(); cellStyle6.setDataFormat(wb.createDataFormat().getFormat("#,##0.000")); cell2_7.setCellValue(11111111.1); cell2_7.setCellStyle(cellStyle6); // HSSFPrintSetup hps=(HSSFPrintSetup) sheet1.getPrintSetup();// hps.setPaperSize((short) 9);// a4 hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);// a4 hps.setFitHeight((short)10);// 10 hps.setFitWidth((short)9);// hps.setLandscape(true);// hps.setLeftToRight(true);// , sheet1.setHorizontallyCenter(true);// sheet1.setVerticallyCenter(true);// // HSSFHeader header=(HSSFHeader) sheet1.getHeader(); header.setCenter(" ");// , fontSize((short) 16); // HSSFFooter footer=(HSSFFooter) sheet1.getFooter(); footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages()); // sheet sheet1.setAutobreaks(true); // sheet1.setAutobreaks(false); int i=sheet1.getLastRowNum(); if(i!=0 && i%1==0) { sheet1.setRowBreak(i);// } // ( ) sheet1.setRepeatingRows(new CellRangeAddress(0,1,0,7));// // ( 。 75%) sheet1.setZoom(75); // sheet1.setColumnWidth(7, 20);// 20 // excel sheet1.setDisplayGridlines(false); FileOutputStream fileOutStream = new FileOutputStream("G:\\ POI .xlsx"); // wb.write(fileOutStream); // System.out.println(" !"); if (null != fileOutStream) { fileOutStream.close(); // } if (null != wb) { wb.close(); // } } }
2、javaはexcelファイルを読みます.
  • マニュアル読取り方式:
  • import java.io.FileInputStream;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    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.poifs.filesystem.POIFSFileSystem;
    
    /**
     *   poi  excel  
     * 
     * @author lingtouyang csdn   ▄┻┳═  ☆
     *
     */
    public class Demo_Read {
         
    
    	public static void main(String[] args) throws Exception {
         
    
    		/**
    		 *       excel    
    		 */
    
    		InputStream is = new FileInputStream("G:\\ POI       .xlsx");//      ,         
    		POIFSFileSystem fs = new POIFSFileSystem(is);
    		HSSFWorkbook wb = new HSSFWorkbook(fs);
    
    		//   sheet 
    		for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
         
    			HSSFSheet hssfSheet = wb.getSheetAt(sheetNum); ///      Sheet 
    			System.out.println(wb.getSheetName(sheetNum));
    			if (hssfSheet == null) {
         
    				continue;
    			}
    			//    Row
    			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
         
    				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
    				if (hssfRow == null) {
         
    					continue;
    				}
    				//    Cell
    				for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
         
    					HSSFCell hssfCell = hssfRow.getCell(cellNum);
    					if (hssfCell == null) {
         
    						continue;
    					}
    					System.out.print(" " + getValue(hssfCell));
    				}
    				System.out.println();
    			}
    		}
    		if (null != is) {
         
    			is.close(); //        
    		}
    		if (null != wb) {
         
    			wb.close(); //            
    		}
    	}
    
    	/**
    	 *                
    	 * 
    	 * @param cell
    	 * @return
    	 */
    	private static String getValue(HSSFCell cell) {
         
    		//    yyyy-MM-dd
    		// HH:mm:ss      ,         。    Demo_Write         ,       。  ,         ,         
    		if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
    				|| "yyyy-MM-dd HH:mm:ss".equals(cell.getCellStyle().getDataFormatString())) {
         
    			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
    		} else {
         
    			switch (cell.getCellType()) {
         
    			case BOOLEAN: // Boolean     
    				return String.valueOf(cell.getBooleanCellValue());
    			case NUMERIC: //        
    				return String.valueOf(cell.getNumericCellValue());
    			default: //            
    				return String.valueOf(cell.getStringCellValue());
    			}
    		}
    	}
    
    }
    
    
    
  • Excel Extractorを使ってエクセルファイルのテキストを抽出します.
  • import java.io.FileInputStream;
    import java.io.InputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.hssf.extractor.ExcelExtractor;
    
    public class Demo_ReadByExcelExtractor {
         
    	/**
    	 *   poi  excel  
    	 * 
    	 * @author lingtouyang csdn   ▄┻┳═  ☆
    	 *
    	 */
    	public static void main(String[] args) throws Exception {
         
    
    		/*
    		 *   ExcelExtractor  excel      
    		 */
    		InputStream is = new FileInputStream("G:\\ POI       .xlsx");//      ,         
    		POIFSFileSystem fs = new POIFSFileSystem(is);
    		HSSFWorkbook wb = new HSSFWorkbook(fs);
    
    		ExcelExtractor excelExtractor = new ExcelExtractor(wb);
    		excelExtractor.setIncludeSheetNames(false); //      sheet    ,       ,         api
    		System.out.println(excelExtractor.getText());
    
    		if (null != excelExtractor) {
         
    			excelExtractor.close(); //            
    		}
    		if (null != is) {
         
    			is.close(); //        
    		}
    		if (null != wb) {
         
    			wb.close(); //            
    		}
    	}
    
    }