package com.poi.excel2;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
*
* @author Hongten</br>
*
* 参考地址：http://hao0610.iteye.com/blog/1160678
*
*/
public class XlsMain {
public static void main(String[] args) throws IOException {
XlsMain xlsMain = new XlsMain();
List<String []> list=null;
String filepath="C:/007.xlsx";
try
{
//读取的excel07数据
list = xlsMain.readXls(filepath);
}
catch (Exception e)
{
//读取的excel03数据
System.out.println("改为03的执行！");
list = xlsMain.readXls03(filepath.substring(0, filepath.length()-1));
}
try {
//
XlsDto2Excel.xlsDto2Excel07(list);
} catch (Exception e) {
e.printStackTrace();
}
// for (int i = 0; i < list.size(); i++) {
// String result[]=list.get(i);
// System.out.println(result[0] + " " + result[1] + " "
// + result[2] + " " + result[3] + " "
// + result[4] );
// }
}
/**
* 读取xls文件内容
*
* @return List<String[]>
* @throws IOException
* 输入/输出(i/o)异常
*/
private List<String []> readXls(String filepath) throws IOException {
InputStream is = new FileInputStream(filepath);
XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
//XlsDto xlsDto = null;
String [] arr=null;
List<String []> list = new ArrayList<String []>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
XSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
arr=new String[5];
//xlsDto = new XlsDto();
// 循环列Cell
// 0学号 1姓名 2学院 3课程名 4 成绩
// for (int cellNum = 0; cellNum <=4; cellNum++) {
XSSFCell xh = hssfRow.getCell(0);
if (xh == null) {
continue;
}
arr[0]=getValue(xh);
//xlsDto.setXh(getValue(xh));
XSSFCell xm = hssfRow.getCell(1);
if (xm == null) {
continue;
}
arr[1]=getValue(xm);
XSSFCell yxsmc = hssfRow.getCell(2);
if (yxsmc == null) {
continue;
}
arr[2]=getValue(yxsmc);
XSSFCell kcm = hssfRow.getCell(3);
if (kcm == null) {
continue;
}
arr[3]=getValue(kcm);
XSSFCell cj = hssfRow.getCell(4);
if (cj == null) {
continue;
}
arr[4]=getValue(cj);
list.add(arr);
}
}
return list;
}
/**
* 得到Excel表中的值
*
* @param xh
* Excel中的每一个格子
* @return Excel中每一个格子中的值
*/
@SuppressWarnings("static-access")
private String getValue(XSSFCell xh) {
if (xh.getCellType() == xh.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(xh.getBooleanCellValue());
} else if (xh.getCellType() == xh.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(xh.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(xh.getStringCellValue());
}
}
private List<String []> readXls03(String filepath) throws IOException {
InputStream is = new FileInputStream(filepath);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
//XlsDto xlsDto = null;
String arr[]=null;
List<String []> list = new ArrayList<String []>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
arr=new String[5];
//xlsDto = new XlsDto();
// 循环列Cell
// 0学号 1姓名 2学院 3课程名 4 成绩
// for (int cellNum = 0; cellNum <=4; cellNum++) {
HSSFCell xh = hssfRow.getCell(0);
if (xh == null) {
continue;
}
//xlsDto.setXh(getValue(xh));
arr[0]=getValue03(xh);
HSSFCell xm = hssfRow.getCell(1);
if (xm == null) {
continue;
}
//xlsDto.setXm(getValue(xm));
arr[1]=getValue03(xm);
HSSFCell yxsmc = hssfRow.getCell(2);
if (yxsmc == null) {
continue;
}
//xlsDto.setYxsmc(getValue(yxsmc));
arr[2]=getValue03(yxsmc);
HSSFCell kcm = hssfRow.getCell(3);
if (kcm == null) {
continue;
}
// xlsDto.setKcm(getValue(kcm));
arr[3]=getValue03(kcm);
HSSFCell cj = hssfRow.getCell(4);
if (cj == null) {
continue;
}
// xlsDto.setCj(Float.parseFloat(getValue(cj)));
arr[4]=getValue03(cj);
list.add(arr);
//list.add(xlsDto);
}
}
return list;
}
/**
* 得到Excel表中的值
*
* @param hssfCell
* Excel中的每一个格子
* @return Excel中每一个格子中的值
*/
@SuppressWarnings("static-access")
private String getValue03(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
}package com.poi.excel2;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XlsDto2Excel
{
/**
*
* @param xls
* XlsDto实体类的一个对象
* @throws Exception
* 在导入Excel的过程中抛出异常
*/
public static void xlsDto2Excel07(List<String[]> xls) throws Exception
{
// 获取总列数
int CountColumnNum = xls.size();
// 创建Excel文档
XSSFWorkbook hwb = new XSSFWorkbook();
// sheet 对应一个工作页
XSSFSheet sheet = hwb.createSheet("pldrxkxxmb");
XSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
XSSFCell[] firstcell = new XSSFCell[CountColumnNum];
String[] names = new String[CountColumnNum];
names[0] = "学号";
names[1] = "姓名";
names[2] = "学院";
names[3] = "课程名";
names[4] = "成绩";
for (int j = 0; j < CountColumnNum; j++)
{
firstcell[j] = firstrow.createCell(j);
//firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
firstcell[j].setCellValue(new XSSFRichTextString(names[j]));
}
for (int i = 0; i < xls.size(); i++)
{
// 创建一行
XSSFRow row = sheet.createRow(i + 1);
// 得到要插入的每一条记录
String arr[] = xls.get(i);
//xlsDto = xls.get(i);
for (int colu = 0; colu <= 4; colu++)
{
// 在一行内循环
XSSFCell xh = row.createCell(0);
xh.setCellValue(arr[0]);
XSSFCell xm = row.createCell(1);
xm.setCellValue(arr[1]);
XSSFCell yxsmc = row.createCell(2);
yxsmc.setCellValue(arr[2]);
XSSFCell kcm = row.createCell(3);
kcm.setCellValue(arr[3]);
XSSFCell cj = row.createCell(4);
cj.setCellValue(arr[4]);
}
}
// 创建文件输出流，准备输出电子表格
OutputStream out = new FileOutputStream("c:/pldrxkxxmb.xlsx");
hwb.write(out);
out.close();
System.out.println("数据库导出成功");
}
public static void xlsDto2Excel(List<String[]> xls) throws Exception
{
// 获取总列数
int CountColumnNum = xls.size();
// 创建Excel文档
HSSFWorkbook hwb = new HSSFWorkbook();
// sheet 对应一个工作页
HSSFSheet sheet = hwb.createSheet("pldrxkxxmb");
HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
String[] names = new String[CountColumnNum];
names[0] = "学号";
names[1] = "姓名";
names[2] = "学院";
names[3] = "课程名";
names[4] = "成绩";
for (int j = 0; j < CountColumnNum; j++)
{
firstcell[j] = firstrow.createCell(j);
firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
}
for (int i = 0; i < xls.size(); i++)
{
// 创建一行
HSSFRow row = sheet.createRow(i + 1);
// 得到要插入的每一条记录
String arr[] = xls.get(i);
//xlsDto = xls.get(i);
for (int colu = 0; colu <= 4; colu++)
{
// 在一行内循环
HSSFCell xh = row.createCell(0);
xh.setCellValue(arr[0]);
HSSFCell xm = row.createCell(1);
xm.setCellValue(arr[1]);
HSSFCell yxsmc = row.createCell(2);
yxsmc.setCellValue(arr[2]);
HSSFCell kcm = row.createCell(3);
kcm.setCellValue(arr[3]);
HSSFCell cj = row.createCell(4);
cj.setCellValue(arr[4]);
}
}
// 创建文件输出流，准备输出电子表格
OutputStream out = new FileOutputStream("c:/pldrxkxxmb.xls");
hwb.write(out);
out.close();
System.out.println("数据库导出成功");
}
}
