java excel 导入 导出 修改



注:需poi的jar包

package excel;

import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.Iterator; import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; 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.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Hyperlink; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelTest {

 public static void main(String[] args) throws IOException {  String filePath= "d:/workbook.xls";   //exportExcel(filePath);  //importExcel(filePath);  modifyExcel(filePath);

 }

 private static void modifyExcel(String filePath) throws IOException {   FileInputStream file = new FileInputStream(filePath);   HSSFWorkbook wb = new HSSFWorkbook(file);   HSSFSheet sheet = wb.getSheetAt(0);

  HSSFRow row3 = sheet.createRow(3);   HSSFCell cess30 = row3.createCell(0);   cess30.setCellValue("王五");   FileOutputStream output = new FileOutputStream(filePath);   wb.write(output);   output.close();

 }

 private static void importExcel(String filePath) throws IOException {       Score score = null;    List<Score> scoreList = new ArrayList<Score>();           FileInputStream file = new FileInputStream(filePath);       HSSFWorkbook wb = new HSSFWorkbook(file);        HSSFSheet sheet = wb.getSheetAt(0);

      for (Row row : sheet) {       if(row.getRowNum()<2)       {        continue;       }       score = new Score();       score.setName(row.getCell(0).getStringCellValue());       score.setBanji(row.getCell(1).getStringCellValue());       score.setJishiScore(row.getCell(2).getNumericCellValue());       score.setBishiScore(row.getCell(3).getNumericCellValue());       score.setDate(row.getCell(4).getDateCellValue());       scoreList.add(score);     }       System.out.println(scoreList);              }

 private static void exportExcel(String filePath) throws IOException {  HSSFWorkbook wb = new HSSFWorkbook();      DataFormat format = wb.createDataFormat();      HSSFFont fontStyle = wb.createFont();      CreationHelper creationHelper = wb.getCreationHelper();         HSSFCellStyle cellStyle = wb.createCellStyle();   HSSFCellStyle cellStyle1=wb.createCellStyle();               cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);   cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   //cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);   //cellStyle.setFillForegroundColor(IndexedColors.RED.index);   //cellStyle.setFillBackgroundColor(IndexedColors.RED.index);               /*cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);   cellStyle1.setBottomBorderColor(IndexedColors.RED.getIndex());      cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);   cellStyle1.setLeftBorderColor(IndexedColors.RED.getIndex());      cellStyle1.setBorderRight(CellStyle.BORDER_THIN);   cellStyle1.setRightBorderColor(IndexedColors.RED.getIndex());      cellStyle1.setBorderTop(CellStyle.BORDER_THIN);   cellStyle1.setTopBorderColor(IndexedColors.RED.getIndex());*/         //cellStyle1.setDataFormat(format.getFormat("0.0000"));   //cellStyle1.setDataFormat(format.getFormat("#,##0.0000"));   //cellStyle1.setDataFormat(format.getFormat("m/d/yy h:mm"));

     /*fontStyle.setFontName("楷体");   fontStyle.setFontHeightInPoints((short) 15);   fontStyle.setColor(HSSFColor.RED.index);   //设置粗体   fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   */      fontStyle.setUnderline(Font.U_SINGLE);   cellStyle1.setFont(fontStyle);      Hyperlink link =creationHelper.createHyperlink(Hyperlink.LINK_URL);   System.out.println("Hyperlink.LINK_URL---->"+Hyperlink.LINK_URL);   link.setAddress("http://www.baidu.com");      HSSFSheet sheet = wb.createSheet("成绩表");   HSSFSheet sheet1 = wb.createSheet("sheet1");   sheet1.setSelected(true);   sheet.setDefaultRowHeightInPoints(20);   //sheet.setDefaultColumnWidth(20);      HSSFRow row =sheet.createRow(0);   HSSFCell cell = row.createCell(0);   cell.setCellStyle(cellStyle);   cell.setCellValue("学员考试成绩一览表");   sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

        HSSFRow row1=sheet.createRow(1);      //row1.setRowStyle(cellStyle1);         HSSFCell cell10=row1.createCell(0);         cell10.setCellValue("姓名");                HSSFCell cell11=row1.createCell(1);         cell11.setCellValue("班级");                         HSSFCell cell12=row1.createCell(2);         cell12.setCellValue("笔试成绩");                        HSSFCell cell13=row1.createCell(3);         cell13.setCellValue("机试成绩");                 HSSFCell cell14=row1.createCell(4);         cell14.setCellValue("日期");            HSSFRow row2=sheet.createRow(2);               HSSFCell cell20 = row2.createCell(0);         cell20.setCellValue("李明");         cell20.setHyperlink(link);         cell20.setCellStyle(cellStyle1);                         HSSFCell cell21= row2.createCell(1);         cell21.setCellValue("一年级");         //sheet.setColumnWidth(cell21.getColumnIndex(),256*30);                 HSSFCell cell22 = row2.createCell(2);         cell22.setCellValue(95.12);                         HSSFCell cell23 = row2.createCell(3);         cell23.setCellValue(5656565656666.239);                 HSSFCell cell24 = row2.createCell(4);         cell24.setCellValue(Calendar.getInstance());         System.out.println(Calendar.getInstance());         System.out.println(new Date());         //cell24.setCellStyle(cellStyle1);                   FileOutputStream output = new FileOutputStream(filePath);   wb.write(output);   output.close();           }

}