java通过poi操作excel
HSSFWorkbook 工作簿对象
HSSFSheet 表
HSSFRow 行
row.createCell row.getCell 创建与获取
demo如下
1 package excelDao; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileOutputStream; 6 import java.io.InputStream; 7 import java.text.DecimalFormat; 8 9 import org.apache.poi.hssf.usermodel.HSSFCell; 10 import org.apache.poi.hssf.usermodel.HSSFRow; 11 import org.apache.poi.hssf.usermodel.HSSFSheet; 12 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 13 14 public class ExameRecord { 15 //考试记录 16 public static void main(String[] args){ 17 File resultFileinput = null; 18 HSSFWorkbook wbinput = null; 19 HSSFRow rowinput = null; 20 HSSFCell cellinput = null; 21 22 int a = 0; //row 23 int x = 0; //考试表row 24 try{ 25 InputStream studyRecord = new FileInputStream("D:/Users/KBLW/Desktop/测试文档/考试记录.xls"); //读取excel 26 HSSFWorkbook wbdu = new HSSFWorkbook(studyRecord); 27 HSSFSheet sheet1 = wbdu.getSheetAt(0); 28 int rowcount = sheet1.getLastRowNum(); //行数 29 30 resultFileinput = new File("D:/Users/KBLW/Desktop/测试文档/exameRecord.xls"); //创建excel 31 wbinput = new HSSFWorkbook(); 32 HSSFSheet exameSheet = wbinput.createSheet("Sheet1"); 33 //HSSFSheet exameHisSheet = wbinput.createSheet("Sheet2"); 34 35 for(;a<rowcount;a++){ 36 int binput = 0; 37 HSSFSheet sheet = wbdu.getSheetAt(0); 38 HSSFRow row = sheet.getRow(a); 39 40 HSSFCell cell0 = row.getCell(0); //学号 41 HSSFCell cell1 = row.getCell(1); //科目一 42 HSSFCell cell2 = row.getCell(2); //科目二 43 HSSFCell cell3 = row.getCell(3); //科目三 44 HSSFCell cell4 = row.getCell(4); //科目四 45 46 47 //HSSFRow rowinputExameHis = exameSheet.createRow(n);//学生id,考试科目,考试第几次,考试是否通过,考试时间,是否有效,是否参加考试 48 49 if(cell1.getCellType()==3){ //getCellType 获得cell值类型 0是数值 1字符串 2字符型 3空值 4布尔 5error 50 continue; 51 }else{ 52 HSSFRow rowinputExame = exameSheet.createRow(x);//学生id,考试科目,考试第几次,考试是否通过,考试时间,是否有效,是否参加考试 53 String amiss1 = cell1.getStringCellValue(); //获取String值 54 String miss1[] =amiss1.split("/"); 55 int i = miss1.length; 56 if(i<1||i==1){ 57 if(binput==0){ 58 HSSFCell cellinputExame0 = rowinputExame.createCell(binput); 59 id(cell0,cellinputExame0); 60 binput++; 61 } 62 if(binput==1){ 63 HSSFCell cellinputExame1 = rowinputExame.createCell(binput); 64 cellinputExame1.setCellValue("一"); 65 binput++; 66 } 67 if(binput==2){ 68 HSSFCell cellinputExame2 = rowinputExame.createCell(binput); 69 cellinputExame2.setCellValue(i); 70 binput++; 71 } 72 73 if(binput==3){ 74 HSSFCell cellinputExame3 = rowinputExame.createCell(binput); 75 if(miss1[i-1].charAt(0)=='合'){ 76 cellinputExame3.setCellValue("过"); 77 }else 78 cellinputExame3.setCellValue("不"); 79 binput++; 80 } 81 if(binput==4){ 82 HSSFCell cellinputExame4 = rowinputExame.createCell(binput); 83 if(miss1[i-1].charAt(0)=='合'){ 84 cellinputExame4.setCellValue(miss1[i-1].substring(2)); 85 }else if(miss1[i-1].charAt(0)=='不'){ 86 cellinputExame4.setCellValue(miss1[i-1].substring(3)); 87 } 88 binput++; 89 } 90 if(binput==5){ 91 HSSFCell cellinputExame5 = rowinputExame.createCell(binput); 92 cellinputExame5.setCellValue("有效"); 93 binput++; 94 } 95 if(binput==6){ 96 HSSFCell cellinputExame6 = rowinputExame.createCell(binput); 97 if(miss1[i-1].charAt(0)=='弃'){ 98 cellinputExame6.setCellValue("弃"); 99 }else cellinputExame6.setCellValue("考"); 100 } 101 x++; 102 }else{ 103 int c=1;//次数 104 for(int s=i;s>1;s--){ 105 HSSFRow rowinputExame1 = exameSheet.createRow(x);//学生id,考试科目,考试第几次,考试是否通过,考试时间,是否有效,是否参加考试 106 binput=0; 107 HSSFCell cellinputExame0s = rowinputExame1.createCell(binput); 108 id(cell0,cellinputExame0s); 109 HSSFCell cellinputExame1s = rowinputExame1.createCell(binput+1); 110 cellinputExame1s.setCellValue("一"); 111 HSSFCell cellinputExame2s = rowinputExame1.createCell(binput+2); 112 cellinputExame2s.setCellValue(c); 113 HSSFCell cellinputExame3s = rowinputExame1.createCell(binput+3); 114 cellinputExame3s.setCellValue("不"); 115 HSSFCell cellinputExame4s = rowinputExame1.createCell(binput+4); 116 cellinputExame4s.setCellValue(miss1[c-1].substring(3)); 117 HSSFCell cellinputExame5s = rowinputExame1.createCell(binput+5); 118 cellinputExame5s.setCellValue("无效"); 119 HSSFCell cellinputExame6s = rowinputExame1.createCell(binput+6); 120 if(miss1[c-1].charAt(0)=='弃'){ 121 cellinputExame6s.setCellValue("弃"); 122 }else cellinputExame6s.setCellValue("考"); 123 x++; 124 c++; 125 } 126 HSSFRow rowinputExame2 = exameSheet.createRow(x);//学生id,考试科目,考试第几次,考试是否通过,考试时间,是否有效,是否参加考试 127 binput=0; 128 HSSFCell cellinputExame0s = rowinputExame2.createCell(binput); 129 id(cell0,cellinputExame0s); 130 HSSFCell cellinputExame1s = rowinputExame2.createCell(binput+1); 131 cellinputExame1s.setCellValue("一"); 132 HSSFCell cellinputExame2s = rowinputExame2.createCell(binput+2); 133 cellinputExame2s.setCellValue(i); 134 HSSFCell cellinputExame3s = rowinputExame2.createCell(binput+3); 135 if(miss1[i-1].charAt(0)=='合'){ 136 cellinputExame3s.setCellValue("过"); 137 }else cellinputExame3s.setCellValue("不"); 138 HSSFCell cellinputExame4s = rowinputExame2.createCell(binput+4); 139 if(miss1[i-1].charAt(0)=='合'){ 140 cellinputExame4s.setCellValue(miss1[i-1].substring(2)); 141 }else if(miss1[i-1].charAt(0)=='不'){ 142 cellinputExame4s.setCellValue(miss1[i-1].substring(3)); 143 } 144 HSSFCell cellinputExame5s = rowinputExame2.createCell(binput+5); 145 cellinputExame5s.setCellValue("有效"); 146 HSSFCell cellinputExame6s = rowinputExame2.createCell(binput+6); 147 if(miss1[i-1].charAt(0)=='弃'){ 148 cellinputExame6s.setCellValue("弃"); 149 }else cellinputExame6s.setCellValue("考"); 150 x++; 151 } 152 } 153 } 154 155 FileOutputStream fosinput = new FileOutputStream(resultFileinput); //保存输入 注意 新建的excel需要保存后才能读取 156 wbinput.write(fosinput); 157 fosinput.flush(); 158 fosinput.close(); 159 }catch(Exception e){ 160 e.printStackTrace(); 161 } 162 } 163 164 165 //id 166 public static String id(HSSFCell cell,HSSFCell cellinput){ 167 DecimalFormat df = new DecimalFormat("0"); 168 cellinput.setCellValue(df.format(cell.getNumericCellValue())); 169 return null; 170 } 171 }
在读取excel时 会遇到类型问题 需要进行单元格类型判断 然后根据不同类型进行处理 当然 也可以在getCell时就进行类型转换
cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型
cell.setCellType(HSSFCell.CELL_TYPE_NUMER);//number类型
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo);//合并从第rowFrom行columnFrom列
插入图片正在摸索
