POI 小例(转载)（JAVA）
具体的需求：在jsp页面上显示从数据库中取出的行列集，然后下面一个按钮，当点击按钮时直接调出Excel文件，excel文件中的内容和jsp页面上的内容一样。并且要求对excel中的内容的字体，字号，等进行格式化。解决此问题，首先连库，输出
package cn.ityc.data;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConn ...{
private static Connection conn;
private Statement stmt;
private ResultSet rs;
private Connection DBConn()...{
try ...{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/excel","root","111111");
} catch (InstantiationException e) ...{
e.printStackTrace();
} catch (IllegalAccessException e) ...{
e.printStackTrace();
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
return conn;
}
public static DBConn getInstance()...{
DBConn dbconn = new DBConn();
return dbconn;
}
public boolean isExist(String sql)...{
boolean isExist = false;
try ...{
Connection conn = DBConn.getInstance().DBConn();
Statement stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next())...{
isExist = true;
}
} catch (SQLException e) ...{
e.printStackTrace();
}
return isExist;
}
public boolean update(String sql)...{
boolean isUpdate = false;
try ...{
conn = DBConn.getInstance().DBConn();
stmt = conn.createStatement();
int num = stmt.executeUpdate(sql);
if(num>0)...{
isUpdate = true;
}
} catch (SQLException e) ...{
e.printStackTrace();
}
return isUpdate;
}
public ResultSet query(String sql)...{
try ...{
conn = DBConn.getInstance().DBConn();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) ...{
e.printStackTrace();
}
return rs;
}
}
我采用了jdbc，连接数据库mysql数据库
然后用一个Getlist.java将结果集ResultSet转化成list
package cn.ityc.data;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.ityc.struts.form.GradeForm;
public class GetList ...{
public static List<GradeForm> getData(String sql)...{
List<GradeForm> list = new ArrayList<GradeForm>();
ResultSet rs = DBConn.getInstance().query(sql);
try ...{
while(rs.next())...{
GradeForm gradeForm = new GradeForm();
String name = rs.getString("name");
String course = rs.getString("course");
BigDecimal grade = rs.getBigDecimal("grade");
gradeForm.setName(name);
gradeForm.setCourse(course);
gradeForm.setGrade(grade);
list.add(gradeForm);
}
} catch (SQLException e) ...{
e.printStackTrace();
}
return list;
}
}
然后用一个测试输出的主方法类
package cn.ityc.data;
import java.util.ArrayList;
import java.util.List;
import cn.ityc.struts.form.GradeForm;
public class TestDemo ...{
/** *//**
* @param args
*/
public static void main(String[] args) ...{
String sql = "select * from excel";
List<GradeForm> list = (ArrayList<GradeForm>)GetList.getData(sql);
for(int i=0;i<list.size();i++)...{
System.out.println(list.get(i).getName());
System.out.println(list.get(i).getCourse());
System.out.println(list.get(i).getGrade());
}
}
}
当然，我建的mysql的数据库是
--
-- 表的结构 `excel`
--
CREATE TABLE `excel` (
`id` int(10) NOT NULL,
`name` varchar(30) collate gb2312_bin default NULL,
`course` varchar(30) collate gb2312_bin default NULL,
`grade` decimal(10,0) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin;
--
-- 导出表中的数据 `excel`
--
INSERT INTO `excel` (`id`, `name`, `course`, `grade`) VALUES
(1, 0x7573657231, 0xe789a9e79086, 91),
(2, 0x7573657232, 0xe789a9e79086, 90),
(3, 0x7573657233, 0xe58c96e5ada6, 91);
然后就可以在java的控制台进行最简单的数据库数据读取了。
那么如何将这些写入excel呢？我在网上找了半天找了一个poi的三个jar包，可以对excel就行一系列的操作
package cn.ityc.data;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
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.Region;
import cn.ityc.struts.form.GradeForm;
public class Excel ...{
private String sheetName;
private HSSFWorkbook hwb;
private short rowNumber;
private short cellNumber;
FileOutputStream fileOut;
public void produce()...{
try ...{
fileOut = new FileOutputStream("aa.xls");
} catch (FileNotFoundException e1) ...{
e1.printStackTrace();
}
hwb = new HSSFWorkbook();
sheetName = "sheet1";
HSSFSheet sheet = this.createSheet();
HSSFRow row = sheet.createRow((short)0);
sheet.addMergedRegion(new Region(0,(short)0,0,(short)2));//
HSSFCell cell1 = row.createCell((short)0);
HSSFCell cell2 = row.createCell((short)1);
HSSFCell cell3 = row.createCell((short)2);
// 定义表头的内容
cell1.setCellValue("姓名");
// cell2.setCellValue("课程");
// cell3.setCellValue("分数");
MyHSSFCellStyle mystyle = new MyHSSFCellStyle();
mystyle.setFontName("楷体");
mystyle.setFontSize((short)800);
mystyle.setHwb(hwb);
HSSFCellStyle style = mystyle.getHSSFCellStyle();
// style.setWrapText(true);
cell1.setCellStyle(style);
cell2.setCellStyle(style);
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)800);
ps.setFitWidth((short)800);
String sql="select * from excel";
List<GradeForm> list = GetList.getData(sql);
for(int i=0;i<GetList.getData(sql).size();i++) ...{
// 定义数据从第二行开始
row = sheet.createRow((short)i+1);
cell1 = row.createCell((short)0);
cell2 = row.createCell((short)1);
cell3 = row.createCell((short)2);
cell1.setCellValue(list.get(i).getName());
cell2.setCellValue(list.get(i).getCourse());
String grade = list.get(i).getGrade()+"";
cell3.setCellValue(grade);
mystyle = new MyHSSFCellStyle();
mystyle.setFontName("楷体");
mystyle.setFontSize((short)300);
mystyle.setHwb(hwb);
style = mystyle.getHSSFCellStyle();
cell1.setCellStyle(style);
cell2.setCellStyle(style);
}
try ...{
hwb.write(fileOut);
fileOut.close();
} catch (IOException e) ...{
e.printStackTrace();
}
try ...{
Open.open("aa.xls");
} catch (IOException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private HSSFSheet createSheet()...{
HSSFSheet sheet = hwb.createSheet(sheetName);
return sheet;
}
private HSSFRow createRow()...{
HSSFRow row = this.createSheet().createRow(rowNumber);
return row;
}
private HSSFCell createCell()...{
HSSFCell cell = this.createRow().createCell(cellNumber);
return cell;
}
}
其中此类用到下面的辅助的类，大家一看就会明白
package cn.ityc.data;
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.HSSFWorkbook;
public class MyHSSFCellStyle ...{
private HSSFWorkbook hwb;
private String fontName;
private short fontSize;
public String getFontName() ...{
return fontName;
}
public void setFontName(String fontName) ...{
this.fontName = fontName;
}
public short getFontSize() ...{
return fontSize;
}
public void setFontSize(short i) ...{
this.fontSize = i;
}
public HSSFCellStyle getHSSFCellStyle()...{
HSSFCellStyle style=hwb.createCellStyle();
style.setAlignment(style.ALIGN_CENTER);
HSSFFont hf=hwb.createFont();
hf.setFontName(this.getFontName());
hf.setFontHeight(this.getFontSize());
style.setFont(hf);
return style;
}
public HSSFWorkbook getHwb() ...{
return hwb;
}
public void setHwb(HSSFWorkbook hwb) ...{
this.hwb = hwb;
}
} 对excel操作，有看头~~
页:
[1]