准备工作:
1.导入POI包:POI下载地址:http://download.csdn.net/detail/zxm1306192988/9522142(重要)
如下
2.导入导出到Excel工具类ExcelUtil.java,封装了POI对Excel的操作
package net.dqsy.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.HSSFCellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* 描述:Excel写操作帮助类
*
*
* */
public class ExcelUtil {
/**
* 功能:创建HSSFSheet工作簿
* @param wb HSSFWorkbook
* @param sheetName String
* @return HSSFSheet
*/
public static HSSFSheet createSheet(HSSFWorkbook wb,String sheetName){
HSSFSheet sheet=wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(12);
sheet.setGridsPrinted(false);
sheet.setDisplayGridlines(false);
return sheet;
}
/**
* 功能:创建HSSFRow
* @param sheet HSSFSheet
* @param rowNum int
* @param height int
* @return HSSFRow
*/
public static HSSFRow createRow(HSSFSheet sheet,int rowNum,int height){
HSSFRow row=sheet.createRow(rowNum);
row.setHeight((short)height);
return row;
}
public static HSSFCell createCell0(HSSFRow row,int cellNum){
HSSFCell cell=row.createCell(cellNum);
return cell;
}
/**
* 功能:创建CELL
* @param row HSSFRow
* @param cellNum int
* @param style HSSFStyle
* @return HSSFCell
*/
public static HSSFCell createCell(HSSFRow row,int cellNum,CellStyle style){
HSSFCell cell=row.createCell(cellNum);
cell.setCellStyle(style);
return cell;
}
/**
* 功能:创建CellStyle样式
* @param wb HSSFWorkbook
* @param backgroundColor 背景色
* @param foregroundColor 前置色
* @param font 字体
* @return CellStyle
*/
public static CellStyle createCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
CellStyle cs=wb.createCellStyle();
cs.setAlignment(halign);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillBackgroundColor(backgroundColor);
cs.setFillForegroundColor(foregroundColor);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setFont(font);
return cs;
}
/**
* 功能:创建带边框的CellStyle样式
* @param wb HSSFWorkbook
* @param backgroundColor 背景色
* @param foregroundColor 前置色
* @param font 字体
* @return CellStyle
*/
public static CellStyle createBorderCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
CellStyle cs=wb.createCellStyle();
cs.setAlignment(halign);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillBackgroundColor(backgroundColor);
cs.setFillForegroundColor(foregroundColor);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setFont(font);
cs.setBorderLeft(CellStyle.BORDER_DASHED);
cs.setBorderRight(CellStyle.BORDER_DASHED);
cs.setBorderTop(CellStyle.BORDER_DASHED);
cs.setBorderBottom(CellStyle.BORDER_DASHED);
return cs;
}
/**
* 功能:多行多列导入到Excel并且设置标题栏格式
*/
public static void writeArrayToExcel(HSSFSheet sheet,int rows,int cells,Object [][]value){
Row row[]=new HSSFRow[rows];
Cell cell[]=new HSSFCell[cells];
for(int i=0;i<row.length;i++){
row[i]=sheet.createRow(i);
for(int j=0;j<cell.length;j++){
cell[j]=row[i].createCell(j);
cell[j].setCellValue(convertString(value[i][j]));
}
}
}
/**
* 功能:多行多列导入到Excel并且设置标题栏格式
*/
public static void writeArrayToExcel(HSSFWorkbook wb,HSSFSheet sheet,int rows,int cells,Object [][]value){
Row row[]=new HSSFRow[rows];
Cell cell[]=new HSSFCell[cells];
HSSFCellStyle ztStyle = (HSSFCellStyle)wb.createCellStyle();
Font ztFont = wb.createFont();
ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
//ztFont.setItalic(true); // 设置字体为斜体字
// ztFont.setColor(Font.COLOR_RED); // 将字体设置为“红色”
ztFont.setFontHeightInPoints((short)10); // 将字体大小设置为18px
ztFont.setFontName("华文行楷"); // 将“华文行楷”字体应用到当前单元格上
// ztFont.setUnderline(Font.U_DOUBLE);
ztStyle.setFont(ztFont);
for(int i=0;i<row.length;i++){
row[i]=sheet.createRow(i);
for(int j=0;j<cell.length;j++){
cell[j]=row[i].createCell(j);
cell[j].setCellValue(convertString(value[i][j]));
if(i==0)
cell[j].setCellStyle(ztStyle);
}
}
}
/**
* 功能:合并单元格
* @param sheet HSSFSheet
* @param firstRow int
* @param lastRow int
* @param firstColumn int
* @param lastColumn int
* @return int 合并区域号码
*/
public static int mergeCell(HSSFSheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){
return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));
}
/**
* 功能:创建字体
* @param wb HSSFWorkbook
* @param boldweight short
* @param color short
* @return Font
*/
public static Font createFont(HSSFWorkbook wb,short boldweight,short color,short size){
Font font=wb.createFont();
font.setBoldweight(boldweight);
font.setColor(color);
font.setFontHeightInPoints(size);
return font;
}
/**
* 设置合并单元格的边框样式
* @param sheet HSSFSheet
* @param ca CellRangAddress
* @param style CellStyle
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,CellStyle style) {
for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {
HSSFRow row = HSSFCellUtil.getRow(i, sheet);
for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {
HSSFCell cell = HSSFCellUtil.getCell(row, j);
cell.setCellStyle(style);
}
}
}
/**
* 功能:将HSSFWorkbook写入Excel文件
* @param wb HSSFWorkbook
* @param absPath 写入文件的相对路径
* @param wbName 文件名
*/
public static void writeWorkbook(HSSFWorkbook wb,String fileName){
FileOutputStream fos=null;
File f=new File(fileName);
try {
fos=new FileOutputStream(f);
wb.write(fos);
int dialog = JOptionPane.showConfirmDialog(null,
f.getName()+"导出成功!是否打开?",
"温馨提示", JOptionPane.YES_NO_OPTION);
if (dialog == JOptionPane.YES_OPTION) {
Runtime.getRuntime().exec("cmd /c start \"\" \"" + fileName + "\"");
}
} catch (FileNotFoundException e) {
JOptionPane.showMessageDialog(null, "导入数据前请关闭工作表");
} catch ( Exception e) {
JOptionPane.showMessageDialog(null, "没有进行筛选");
} finally{
try {
if(fos!=null){
fos.close();
}
} catch (IOException e) {
}
}
}
public static String convertString(Object value) {
if (value == null) {
return "";
} else {
return value.toString();
}
}
}
3.导出Excel
JButton toExcelButton = new JButton("导出excel");
toExcelButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent arg0) {
// TODO Auto-generated method stub
File selectedFile = getSelectedFile(".xls");
if (selectedFile != null) {
String path = selectedFile.getPath();
// System.out.println(path);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("试卷");
String[] n = { "题干", "A", "B", "C", "D", "Answer" };
Object[][] value = new Object[questionInfos.size() + 1][6];
for (int m = 0; m < n.length; m++) {
value[0][m] = n[m];
}
for (int i = 0; i < questionInfos.size(); i++) {
value[i + 1][0] = questionInfos.get(i).getQuestion();
value[i + 1][1] = questionInfos.get(i).getA();
value[i + 1][2] = questionInfos.get(i).getB();
value[i + 1][3] = questionInfos.get(i).getC();
value[i + 1][4] = questionInfos.get(i).getD();
value[i + 1][5] = questionInfos.get(i).getAnswer();
}
ExcelUtil.writeArrayToExcel(wb, sheet, questionInfos.size() + 1, 6, value);
ExcelUtil.writeWorkbook(wb, path);
}
}
});
4.导入Excel
JButton fromExcelButton = new JButton("导入excel");
fromExcelButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent arg0) {
// TODO Auto-generated method stub
File selectedFile = getSelectedOpenFile(".xls");
if (selectedFile != null) {
String path = selectedFile.getPath();
String result = "success";
try {
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
path));
// 创建对工作表的引用。
// HSSFSheet sheet = workbook.getSheet("Sheet1");
HSSFSheet sheet = workbook.getSheetAt(0);
int j = 1;//从第2行开始堵数据
// 第在excel中读取一条数据就将其插入到数据库中
while (j < sheet.getPhysicalNumberOfRows()) {
HSSFRow row = sheet.getRow(j);
QuestionInfo onequestioninfo = new QuestionInfo(null, null, null, null, null, null,GlobalInfo.selectedpaper);
for (int i = 0; i <= 5; i++) {
HSSFCell cell = row.getCell((short) i);
if (i == 0) {
onequestioninfo.setQuestion(cell.getStringCellValue());
} else if (i == 1){
onequestioninfo.setA(cell.getStringCellValue());
}
else if (i == 2){
onequestioninfo.setB(cell.getStringCellValue());
}
else if (i == 3){
onequestioninfo.setC(cell.getStringCellValue());
}
else if (i == 4){
onequestioninfo.setD(cell.getStringCellValue());
}
else if (i == 5){
onequestioninfo.setAnswer(cell.getStringCellValue());
}
}
j++;
jdbc.addQuestionInfo(onequestioninfo);
}
} catch (FileNotFoundException e2) {
// TODO Auto-generated catch block
System.out.println("notfound");
e2.printStackTrace();
} catch (IOException e3) {
// TODO Auto-generated catch block
System.out.println(e3.toString());
e3.printStackTrace();
}
}
}
});