Java生成Excel表格
简单的示范代码如下：
package xls.test;
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class XlsExample {
public static void main(String[] args) {
try {
WritableWorkbook book = Workbook.createWorkbook(new File("test.xls"));
WritableSheet sheet = book.createSheet("sheet0", 0);
int col = 0;
int row = 0;
sheet.addCell(new Label(col++, row, "语文"));
sheet.addCell(new Label(col++, row, "数学"));
sheet.addCell(new Label(col++, row, "英语"));
sheet.addCell(new Label(col++, row, "物理"));
sheet.addCell(new Label(col++, row, "化学"));
sheet.addCell(new Label(col++, row, "生物"));
for(row = 1;row < 10;){
col = 0;
sheet.addCell(new Number(col++, row, (int)(Math.random()*100)));
sheet.addCell(new Number(col++, row, (int)(Math.random()*100)));
sheet.addCell(new Number(col++, row, (int)(Math.random()*100)));
sheet.addCell(new Number(col++, row, (int)(Math.random()*100)));
sheet.addCell(new Number(col++, row, (int)(Math.random()*100)));
sheet.addCell(new Number(col++, row, (int)(Math.random()*100)));
row++;
}
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
运行结果如下：
语文 数学 英语 物理 化学 生物 15 3 15 23 12 76 35 67 59 45 29 76 30 9 10 49 38 9 88 27 63 39 20 18 11 78 28 12 65 68 30 89 43 28 53 84 21 55 40 97 78 18 56 31 74 10 41 86 13 40 27 54 26 8
使用的java包：jxl-2.6.12.jar
这些代码还是可以派上用场的，一个朋友在做日志分析，要将几十个像下面格式日志信息导入到excel中：
2013-10-20 日志记录 ======================================== ---------- 00:00:00 ---------- 机器人已经收到命令 ---------- 00:00:04 ---------- 收到机器人起始抓表完成！ ---------- 00:00:11 ---------- 收到机器人命令完成！ ---------- 00:00:12 ---------- 机器人执行从1到1取表位抓到上表位命令成功！
导出的格式如下所示：
xxxx 00:00:01 00:00:02 xxxx 00:00:02 00:00:23 10:00:00
下面是示例代码，以后有类似的可以拿来参考一下：
1 import java.io.BufferedReader; 2 import java.io.File; 3 import java.io.FileInputStream; 4 import java.io.IOException; 5 import java.io.InputStreamReader; 6 import java.util.Map.Entry; 7 import java.util.regex.Matcher; 8 import java.util.regex.Pattern; 9 import java.util.ArrayList; 10 import java.util.Iterator; 11 import java.util.Map; 12 import java.util.List; 13 import java.util.HashMap; 14 import java.util.Set; 15 16 import jxl.Workbook; 17 import jxl.write.Label; 18 import jxl.write.WritableSheet; 19 import jxl.write.WritableWorkbook; 20 import jxl.write.WriteException; 21 import jxl.write.biff.RowsExceededException; 22 public class LogProcess { 23 String regx="\\-+ \\d{2}:\\d{2}:\\d{2} \\-+"; 24 Pattern p = Pattern.compile(regx); 25 public Map<String,List<String>> map = new HashMap<String,List<String>>(); 26 public void processLog(BufferedReader br) throws IOException{ 27 String temp1=null; 28 temp1=br.readLine(); 29 while(temp1 != null){ 30 Matcher m = p.matcher(temp1); 31 boolean match = m.matches(); 32 if(match) 33 break; 34 temp1=br.readLine(); 35 } 36 while(temp1!=null){ 37 Matcher m = p.matcher(temp1); 38 boolean match = m.matches(); 39 if(!match){ 40 ;//System.out.println("输入格式不正确!"); 41 }else{ 42 ;//System.out.println("输入格式正确!"+ temp1); 43 } 44 String temp2=br.readLine(); 45 if( temp2 != null && !temp2.isEmpty()){ 46 if(map.containsKey(temp2)){ 47 temp1 = temp1.replaceAll("-", "").trim(); 48 map.get(temp2).add(temp1); 49 50 }else{ 51 List<String> tmpList = new ArrayList<String>(); 52 temp1 = temp1.replaceAll("-", "").trim(); 53 tmpList.add(temp1); 54 map.put(temp2, tmpList); 55 } 56 } 57 temp1 = br.readLine(); 58 temp1 = br.readLine(); 59 } 60 } 61 public void writeToExcel(String fileName,String path) throws IOException, RowsExceededException, WriteException{ 62 String xlsName = path + fileName+".xls"; 63 WritableWorkbook book = Workbook.createWorkbook(new File(xlsName)); 64 WritableSheet sheet = book.createSheet("sheet0", 0); 65 int col = 0; 66 int row = 0; 67 Set<Map.Entry<String, List<String>>> set = map.entrySet(); 68 Iterator<Entry<String, List<String>>> it = set.iterator(); 69 while(it.hasNext()){ 70 Map.Entry<String, List<String>> entry = it.next(); 71 String key = entry.getKey(); 72 sheet.addCell(new Label(col, row, key)); 73 col++; 74 List<String> list = entry.getValue(); 75 StringBuffer bf = new StringBuffer(); 76 System.out.println(key + ":" + bf.toString()); 77 } 78 Set<Map.Entry<String, List<String>>> set2 = map.entrySet(); 79 Iterator<Entry<String, List<String>>> it2 = set2.iterator(); 80 col = 0; 81 row = 1; 82 while(it2.hasNext()){ 83 int newrow = 1; 84 Map.Entry<String, List<String>> entry = it2.next(); 85 String key = entry.getKey(); 86 List<String> list = entry.getValue(); 87 StringBuffer bf = new StringBuffer(); 88 for(int i = 0; i < list.size(); i++){ 89 bf.append(list.get(i)); 90 bf.append("\t"); 91 sheet.addCell(new Label(col, newrow++,list.get(i))); 92 } 93 col++; 94 System.out.println(key + ":" + bf.toString()); 95 } 96 book.write(); 97 book.close(); 98 } 99 public void writeToExcel2(String fileName,String path) throws IOException, RowsExceededException, WriteException{ 100 String xlsName = path + fileName+".xls"; 101 File file = new File(xlsName); 102 if(file.exists()) 103 file.delete(); 104 WritableWorkbook book = Workbook.createWorkbook(file); 105 WritableSheet sheet = book.createSheet("sheet0", 0); 106 int col = 0; 107 int row = 0; 108 Set<Map.Entry<String, List<String>>> set = map.entrySet(); 109 Iterator<Entry<String, List<String>>> it = set.iterator(); 110 while(it.hasNext()){ 111 Map.Entry<String, List<String>> entry = it.next(); 112 String key = entry.getKey(); 113 sheet.addCell(new Label(col, row, key)); 114 row++; 115 List<String> list = entry.getValue(); 116 StringBuffer bf = new StringBuffer(); 117 System.out.println(key + ":" + bf.toString()); 118 } 119 Set<Map.Entry<String, List<String>>> set2 = map.entrySet(); 120 Iterator<Entry<String, List<String>>> it2 = set2.iterator(); 121 col = 1; 122 row = 0; 123 while(it2.hasNext()){ 124 int newcol = 1; 125 Map.Entry<String, List<String>> entry = it2.next(); 126 String key = entry.getKey(); 127 List<String> list = entry.getValue(); 128 StringBuffer bf = new StringBuffer(); 129 for(int i = 0; i < list.size(); i++){ 130 bf.append(list.get(i)); 131 bf.append("\t"); 132 sheet.addCell(new Label(newcol++, row,list.get(i))); 133 } 134 row++; 135 //System.out.println(key + ":" + bf.toString()); 136 } 137 book.write(); 138 book.close(); 139 } 140 public static void main(String[] args) { 141 try { 142 String path = "E:\\workspace\\log\\"; 143 String outputPath = "E:\\workspace\\excel\\"; 144 File file=new File(path); 145 File files[] = file.listFiles(); 146 for(int i = 0; i < files.length; i++){ 147 String fileName = files[i].getName(); 148 System.out.println(fileName); 149 InputStreamReader insReader = new InputStreamReader(new FileInputStream(files[i]),"utf-8"); 150 BufferedReader br = new BufferedReader(insReader); 151 LogProcess logProcess = new LogProcess(); 152 logProcess.processLog(br); 153 try{ 154 logProcess.writeToExcel2(fileName,outputPath); 155 }catch(Exception ex){ 156 System.out.println("write excel exception."); 157 logProcess.writeToExcel(fileName,outputPath); 158 } 159 } 160 } catch (Exception e) { 161 e.printStackTrace(); 162 } 163 } 164 }
