方法一,利用第三方jar包:jxl.jar
无论方法一方法二最后最好用转换成流传给前端。
1 public void createExcel(){
2 try{
3 //打开文件
4 WritableWorkbook workbook = Workbook.createWorkbook(new File("test.xls"));
5 //生成名为“第一页”的工作表,参数0表示这是第一页
6 WritableSheet sheet = workbook.createSheet("第一页", 0);
7 //在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
8 //以及单元格内容为test
9 Label label = new Label(0,0,"test");
10 //将定义好的单元格添加到工作表中
11 sheet.addCell(label);
12 /*生成一个保存数字的单元格
13 * 必须使用Number的完整包路径,否则有语法歧义
14 * 单元格位置是第二列,第一行,值为789.123*/
15 jxl.write.Number number = new jxl.write.Number(1,0,756);
16
17 sheet.addCell(number);
18
19 sheet.insertColumn(1);
20
21 workbook.copySheet(0, "第二页", 1);
22
23 WritableSheet sheet2 = workbook.getSheet(1);
24 Range range = sheet2.mergeCells(0, 0, 0, 8);
25 sheet2.unmergeCells(range);
26
27 sheet2.addImage(new WritableImage(5, 5, 10, 20, new File("F:\\09.png")));
28
29
30 CellView cv = new CellView();
31
32 WritableCellFormat cf = new WritableCellFormat();
33 cf.setBackground(Colour.BLUE);
34
35 cv.setFormat(cf);
36 cv.setSize(6000);
37 cv.setDimension(10);
38
39 sheet2.setColumnView(2, cv);
40
41 workbook.write();
42 workbook.close();
43
44 }catch(Exception e){}
45 }
同时,读取Excel中的内容为:
1 public void displayExcel(){
2 try {
3 Workbook wb = Workbook.getWorkbook(new File("test.xls"));
4
5 Sheet s = wb.getSheet(0);
6 System.out.println(s.getCell(0, 0).getContents());
7 } catch (BiffException e) {
8 // TODO Auto-generated catch block
9 e.printStackTrace();
10 } catch (IOException e) {
11 // TODO Auto-generated catch block
12 e.printStackTrace();
13 }
14
15 }
方法二,利用jar包:poi-3.2-FINAL-20081019.jar
1 public void exportExcel(){
2
3 HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
4
5 HSSFFont font = wb.createFont();
6 font.setFontHeightInPoints((short)24);
7 font.setFontName("宋体");
8 font.setColor(HSSFColor.BLACK.index);
9 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
10
11 HSSFCellStyle style = wb.createCellStyle();
12 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
13 style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
14 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
15 style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
16 style.setFont(font);
17
18 HSSFSheet sheet = wb.createSheet("test");//创建工作表,名称为test
19
20 int iRow = 0;//行号
21 int iMaxCol = 17;//最大列数
22 HSSFRow row = sheet.createRow(iRow);
23 HSSFCell cell = row.createCell((short)0);
24 cell.setCellValue(new HSSFRichTextString("测试excel"));
25 cell.setCellStyle(style);
26 sheet.addMergedRegion(new Region(iRow,(short)0,iRow,(short)(iMaxCol-1)));
27
28 ByteArrayOutputStream os = new ByteArrayOutputStream();
29
30 try{
31 wb.write(os);
32 }catch(IOException e){
33 e.printStackTrace();
34 //return null;
35 }
36
37 byte[] xls = os.toByteArray();
38
39 File file = new File("test01.xls");
40 OutputStream out = null;
41 try {
42 out = new FileOutputStream(file);
43 try {
44 out.write(xls);
45 } catch (IOException e) {
46 // TODO Auto-generated catch block
47 e.printStackTrace();
48 }
49 } catch (FileNotFoundException e1) {
50 // TODO Auto-generated catch block
51 e1.printStackTrace();
52 }
53
54
55 }
这里补上相关的jar包:jxl.jar, poi.jar 的下载地址:
三、Excel导入到数据库
1、添加POI jar包到项目的lib目录下
2、Excel文件目录:d://excel.xls
3、数据库字段为:num1 num2 num3 num4 num5 num6
4、数据库名:blog
5、表名:test
6、编写类:连接mysql的字符串方法、插入的方法、实体类
1 import java.io.FileInputStream;
2 import java.io.FileNotFoundException;
3 import java.io.IOException;
4 import org.apache.commons.logging.Log;
5 import org.apache.commons.logging.LogFactory;
6 import org.apache.poi.hssf.usermodel.HSSFCell;
7 import org.apache.poi.hssf.usermodel.HSSFRow;
8 import org.apache.poi.hssf.usermodel.HSSFSheet;
9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10
11 public class TestExcel {
12 //记录类的输出信息
13 static Log log = LogFactory.getLog(TestExcel.class);
14 //获取Excel文档的路径
15 public static String filePath = "D://excel.xls";
16 public static void main(String[] args) {
17 try {
18 // 创建对Excel工作簿文件的引用
19 HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
20 // 在Excel文档中,第一张工作表的缺省索引是0
21 // 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
22 HSSFSheet sheet = wookbook.getSheet("Sheet1");
23 //获取到Excel文件中的所有行数
24 int rows = sheet.getPhysicalNumberOfRows();
25 //遍历行
26 for (int i = 0; i < rows; i++) {
27 // 读取左上端单元格
28 HSSFRow row = sheet.getRow(i);
29 // 行不为空
30 if (row != null) {
31 //获取到Excel文件中的所有的列
32 int cells = row.getPhysicalNumberOfCells();
33 String value = "";
34 //遍历列
35 for (int j = 0; j < cells; j++) {
36 //获取到列的值
37 HSSFCell cell = row.getCell(j);
38 if (cell != null) {
39 switch (cell.getCellType()) {
40 case HSSFCell.CELL_TYPE_FORMULA:
41 break;
42 case HSSFCell.CELL_TYPE_NUMERIC:
43 value += cell.getNumericCellValue() + ",";
44 break;
45 case HSSFCell.CELL_TYPE_STRING:
46 value += cell.getStringCellValue() + ",";
47 break;
48 default:
49 value += "0";
50 break;
51 }
52 }
53 }
54 // 将数据插入到mysql数据库中
55 String[] val = value.split(",");
56 TestEntity entity = new TestEntity();
57 entity.setNum1(val[0]);
58 entity.setNum2(val[1]);
59 entity.setNum3(val[2]);
60 entity.setNum4(val[3]);
61 entity.setNum5(val[4]);
62 entity.setNum6(val[5]);
63 TestMethod method = new TestMethod();
64 method.Add(entity);
65 }
66 }
67 } catch (FileNotFoundException e) {
68 e.printStackTrace();
69 } catch (IOException e) {
70 e.printStackTrace();
71 }
72 }
73 }