package com.xiangshui;
import java.io.BufferedInputStream;
import java.io.File;
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 jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
* 读写excel
* 处理excel的相关JAR包
* @author liuqixiang
* 所需JAR 包 jxl.jar,poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar
* jar包位置 我的网盘/编程软件及工具包/工具包/处理excel的相关JAR包/
*
*/
public class ExcelUtils {
/**
* excel2003 即.xls
* @author xiangshuai
* excel 文件读取,并将excel的行记录封装到String[]数组中,将查出来的整个行记录--即string[] 用list进行存储
* @param excelPath excel的全路径
* @param startindex excel开始读取行数 [1,]
* @return List<String[]> 读取的excel的全部数据
*/
public static List<String[]> readExcelToList(String excelPath,int startindex){
List<String[]> list = new ArrayList<String[]>();
BufferedInputStream bis = null;
Workbook workbook = null;
try {
bis = new BufferedInputStream(new FileInputStream(new File(excelPath)));
workbook = Workbook.getWorkbook(bis);
Sheet sheet = workbook.getSheet(1);//workbook.getSheet(0) 获得sheet1,workbook.getSheet(1) 获得sheet2
for(int i=startindex-1;i<sheet.getRows();i++){//控制行
String[] strs = new String[sheet.getColumns()];//new 一个sheet.getColumns()大小的数组,用来存储此行的数据
for(int j=0;j<sheet.getColumns();j++){//便利列
Cell cell = sheet.getCell(j,i);//sheet.getCell(j,i) j:列,i:行
strs[j] = cell.getContents();//将此单元格的内容赋值给String[] 中
}
list.add(strs);
}
} catch (BiffException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(bis!=null){
try {
bis.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(workbook!=null){
workbook.close();
}
}
return list;
}
/**
* excel2003 即.xls
* @author xiangshuai
* excel 文件读取,并将excel的行记录封装到String[]数组中,将查出来的整个行记录--即string[] 用list进行存储
* @param excelPath excel的全路径
* @param startindex excel开始读取行数 [1,]
* @return List<String[]> 读取的excel的全部数据
*/
public static List<String[]> readExcel(String excelPath,int startindex)throws Exception{
List<String[]> list=new ArrayList<String[]>();
InputStream stream=new FileInputStream(new File(excelPath));
Workbook rwb=Workbook.getWorkbook(stream);
Sheet sheet=rwb.getSheet(0);
for (int i = startindex-1; i < sheet.getRows(); i++) {
String[] strs=new String[sheet.getColumns()];
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell=sheet.getCell(j, i);
strs[j]=cell.getContents();
}
list.add(strs);
}
rwb.close();
stream.close();
return list;
}
//excel2003 即.xls 获得第一行的数据 即 描述字段头--表头 如excel中的 第1行 [年度, 目录号, 案卷号, 册序, 序号, 档号, 卷内目录, 页号, 页数]
public static String[] readExcelHead_2003(String path) throws Exception {
try{
InputStream stream=new FileInputStream(new File(path));
Workbook rwb=Workbook.getWorkbook(stream);
Sheet sheet=rwb.getSheet(0);
String[] strs=new String[sheet.getColumns()];
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell=sheet.getCell(j, 0);
strs[j]=cell.getContents();
}
rwb.close();
stream.close();
return strs;
}catch (Exception e) {
return new String[]{""};
}
}
/*
*
*
* */
/**
* @author liuqixiang
* @param path
* excel2007 即.xls 获得第一行的数据 即 描述字段头 如excel中的 第1行--表头 [年度, 目录号, 案卷号, 册序, 序号, 档号, 卷内目录, 页号, 页数],StringUtils.isNull(text):
* 如果cell的值是 "null","Null"或无值或空字符串 均设为 ""
* @return String[]-- excel的字段头 一般就是第一行的记录
* @throws Exception
*/
public static String[] readExcelHead_2007(String path) throws Exception {
String[] strs=null;
InputStream is=new FileInputStream(path);
XSSFWorkbook wb;
XSSFSheet sheet;
XSSFRow row;
try {
wb=new XSSFWorkbook(is);
sheet=wb.getSheetAt(0);
row=sheet.getRow(0);
int cum=row.getLastCellNum();
// int cum1=row.getPhysicalNumberOfCells();
strs=new String[cum];
for (int j = 0; j < cum; j++) {
XSSFCell cell=row.getCell(j);
if(cell==null || "".equals(cell)){
strs[j]="";
}else{
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
String text=cell.getStringCellValue();
if(StringUtils.isNull(text)){
text="";
}
strs[j]=text;
}
}
return strs;
} catch (Exception e) {
e.printStackTrace();
return new String[]{""};
}
}
/**
* excel2003 即.xls
* 向excel文件最后添加一行数据
* @param excelPath excel全路径
* @param datas 数据 --即要插入最后一行的数据
*/
public static void addRow_last(String excelPath,String[] datas){
try {
InputStream is=new FileInputStream(new File(excelPath));
Workbook wb=Workbook.getWorkbook(is);
WritableWorkbook wbe=Workbook.createWorkbook(new File(excelPath),wb);
WritableSheet sheet=wbe.getSheet(0);
int rows=sheet.getRows();
List<Label> labels=new ArrayList<Label>();
for (int i = 0; i < datas.length; i++) {
Label label=new Label(i,rows,datas[i]);
labels.add(label);
}
for (int i = 0; i <labels.size(); i++) {
sheet.addCell(labels.get(i));
}
wbe.write();
wbe.close();
wb.close();
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* excel2003 即.xls --- 有判断 如果excel中的cell (cell-格即具体的某一行,某一列的值) 无值,为
* @author xiangshuai
* excel 文件读取,并将excel的行记录封装到String[]数组中,将查出来的整个行记录--即string[] 用list进行存储 StringUtils.isNull(text):
* 如果cell的值是 "null","Null"或无值或空字符串 均设为 ""
* @param excelPath excel的全路径
* @param startindex excel开始读取行数 [1,]
* @return List<String[]> 读取的excel的全部数据
* StringUtils.isNull(text)
*/
public static List<String[]> readExcel_poi2003(String excelPath,int startindex){
List<String[]> list=new ArrayList<String[]>();
try {
InputStream stream=new FileInputStream(new File(excelPath));
Workbook rwb=Workbook.getWorkbook(stream);
Sheet sheet=rwb.getSheet(0);
for (int i = startindex-1; i < sheet.getRows(); i++) {
String[] strs=new String[sheet.getColumns()];
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell=sheet.getCell(j, i);
if(cell==null){
strs[j]="";
}else{
String text=cell.getContents();
if(StringUtils.isNull(text)){
text="";
}
strs[j]=text;
}
}
if(strs[0]!=null&&!"".equals(strs[0])){
list.add(strs);
}
}
rwb.close();
stream.close();
return list;
} catch (Exception e) {
e.printStackTrace();
return new ArrayList<String[]>();
}
}
/**
* @author xiangshuai
* EXCLE 2003读取
* excel 文件读取 ,从startindex行开始,从第一列到第count列 的excel的行记录封装到String[]数组中,将查出来的整个行记录--即string[] 用list进行存储 StringUtils.isNull(text):
* 如果cell的值是 "null","Null"或无值或空字符串 均设为 ""
* @param excelPath excel路径
* @param startindex 开始读取的行数
* @param count 列数 ,需读取的列数
* @return List<String[]> 读取的excel的全部数据
*/
public static List<String[]> readExcel_poi2003(String excelPath,int startindex,int count){
List<String[]> list=new ArrayList<String[]>();
try {
InputStream stream=new FileInputStream(new File(excelPath));
Workbook rwb=Workbook.getWorkbook(stream);
Sheet sheet=rwb.getSheet(0);
for (int i = startindex-1; i < sheet.getRows(); i++) {
String[] strs=new String[count];
for (int j = 0; j < count; j++) {
Cell cell=sheet.getCell(j, i);
if(cell==null){
strs[j]="";
}else{
String text=cell.getContents();
if(StringUtils.isNull(text)){
text="";
}
strs[j]=text;
}
}
if(strs[0]!=null&&!"".equals(strs[0])){
list.add(strs);
}
}
rwb.close();
stream.close();
return list;
} catch (Exception e) {
e.printStackTrace();
return new ArrayList<String[]>();
}
}
/**
* @author xiangshuai
* EXCLE 2007读取
* excel 文件读取 ,从startindex行开始,从第一列到第count列 的excel的行记录封装到String[]数组中,将查出来的整个行记录--即string[] 用list进行存储 StringUtils.isNull(text):
* 如果cell的值是 "null","Null"或无值或空字符串 均设为 ""
* @param excelPath excel路径
* @param startindex 开始读取的行数
* @param count 列数 ,需读取的列数
* @return List<String[]> 读取的excel的全部数据
*/
public static List<String[]> readExcel_poi2007(String excelPath,int startindex,int count)throws Exception{
List<String[]> list=new ArrayList<String[]>();
InputStream is=new FileInputStream(excelPath);
XSSFWorkbook wb;
XSSFSheet sheet;
XSSFRow row;
try {
wb=new XSSFWorkbook(is);
sheet=wb.getSheetAt(0);
int rowcount=sheet.getLastRowNum();
for (int i = startindex-1; i < rowcount+1; i++) {
row=sheet.getRow(i);
// int cum=row.getLastCellNum();
// int cum1=row.getPhysicalNumberOfCells();
String[] strs=new String[count];
for (int j = 0; j < count; j++) {
XSSFCell cell=row.getCell(j);
if(cell==null){
strs[j]="";
}else{
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
String text=cell.getStringCellValue();
if(StringUtils.isNull(text)){
text="";
}
strs[j]=text;
}
}
if(strs[0]!=null&&!"".equals(strs[0])){
list.add(strs);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
return new ArrayList<String[]>();
}
}
/**
* excel2007
* @author xiangshuai
* excel 文件读取,并将excel的行记录封装到String[]数组中,将查出来的整个行记录--即string[] 用list进行存储 StringUtils.isNull(text):
* 如果cell的值是 "null","Null"或无值或空字符串 均设为 ""
* @param excelPath excel的全路径
* @param startindex excel开始读取行数 [1,]
* @return List<String[]> 读取的excel的全部数据
*/
public static List<String[]> readExcel_poi2007(String excelPath,int startindex)throws Exception{
List<String[]> list=new ArrayList<String[]>();
InputStream is=new FileInputStream(excelPath);
XSSFWorkbook wb;
XSSFSheet sheet;
XSSFRow row;
try {
wb=new XSSFWorkbook(is);
sheet=wb.getSheetAt(0);
int rowcount=sheet.getLastRowNum();
for (int i = startindex-1; i < rowcount+1; i++) {
row=sheet.getRow(i);
int cum=row.getLastCellNum();
// int cum1=row.getPhysicalNumberOfCells();
String[] strs=new String[cum];
for (int j = 0; j < cum; j++) {
XSSFCell cell=row.getCell(j);
if(cell==null){
strs[j]="";
}else{
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
String text=cell.getStringCellValue();
if(StringUtils.isNull(text)){
text="";
}
strs[j]=text;
}
}
if(strs[0]!=null&&!"".equals(strs[0])){
list.add(strs);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
return new ArrayList<String[]>();
}
}
/**
* 向excel中插入图片
* @param excelPath
* @param imagePath
* @param x
* @param y
* @param width
* @param height
*/
public static void insertImg(String excelPath,String imagePath,int x,int y,int width,int height){
try{
InputStream is=new FileInputStream(new File(excelPath));
Workbook wb=Workbook.getWorkbook(is);
WritableWorkbook wbe=Workbook.createWorkbook(new File(excelPath),wb);
WritableSheet sheet=wbe.getSheet(0);
File imgFile=new File(imagePath);
WritableImage image=new WritableImage(x,y,width,height,imgFile);
sheet.addImage(image);
wbe.write();
wbe.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 判断字符串是否为空 为空返回true
* @param str
* @return
*/
public static boolean isNull(String str){
if(str==null||"null".equals(str)||"NULL".equals(str)){
return true;
}else{
str=str.replaceAll(" ", "");
if("".equals(str)){
return true;
}else{
return false;
}
}
}
public static void main(String[] args) {
/*try {
readExcel_poi2007("E:\\szhgj\\2009-XCH\\诉讼档案盒(1).xls",2);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
try {
createExcel("E:/temp");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//xiangshuai======================================================== excel导出操作
/**
* 表头单元格样式的设定
*/
public static WritableCellFormat getHeaderCellStyle()throws Exception{
/*
* WritableFont.createFont("宋体"):设置字体为宋体
* 10:设置字体大小
* WritableFont.BOLD:设置字体加粗(BOLD:加粗 NO_BOLD:不加粗)
* false:设置非斜体
* UnderlineStyle.NO_UNDERLINE:没有下划线
*/
WritableFont font = new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);
try {
//添加字体设置
headerFormat.setFont(font);
//设置单元格背景色:表头为黄色
headerFormat.setBackground(Colour.WHITE);
//设置表头表格边框样式
//整个表格线为粗线、黑色
headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//表头内容水平居中显示
headerFormat.setAlignment(Alignment.CENTRE);
// headerFormat.setAlignment(jxl.format.Alignment.CENTRE);//把水平对齐方式指定为居中
// headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中
// headerFormat.setWrap(true);//设置自动换行
} catch (WriteException e) {
e.printStackTrace();
}
return headerFormat;
}
/**
* 表头单元格样式的设定
*/
public static WritableCellFormat getBodyCellStyle()throws Exception{
/*
* WritableFont.createFont("宋体"):设置字体为宋体
* 10:设置字体大小
* WritableFont.NO_BOLD:设置字体非加粗(BOLD:加粗 NO_BOLD:不加粗)
* false:设置非斜体
* UnderlineStyle.NO_UNDERLINE:没有下划线
*/
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat bodyFormat = new WritableCellFormat(font);
try {
//设置单元格背景色:表体为白色
bodyFormat.setBackground(Colour.WHITE);
//设置表头表格边框样式
//整个表格线为细线、黑色
bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
} catch (WriteException e) {
e.printStackTrace();
}
return bodyFormat;
}
public static boolean createExcel(String excelPreDir) throws Exception{
String excelFilePath=excelPreDir+File.separator+"cxExport.xls";
//创建一个excel文件
WritableWorkbook book = Workbook.createWorkbook(new File(excelFilePath));
//创建一个工作薄,参数1:名称,参数2:位置(0:第一页,1:第二页 ....)
WritableSheet sheet = book.createSheet("测试EXCEL-DEMO", 0);
//去掉整个sheet中的网格线
//sheet.getSettings().setShowGridLines(false);
//添加excel表头 sheet.setColumnView(arg0, arg1) arg0:列(如0表示第一列) arg2:代表此单元格-cell的宽度
for(int j=0;j<8;j++){ //列
sheet.setColumnView(j, j*5+1);
/*xl.write.Label.Label(int c, int r, String cont, CellFormat st) 在第r行第c列 创建一个内容是 cont 样式为st 的文本单元格
* c: 列 r:行 ,cont:内容 st:样式
*/
Label label = new Label(j,0,"内容 "+j,getHeaderCellStyle());
sheet.addCell(label);
}
for(int i=0;i<5;i++){//行
for(int j=0;j<8;j++){ //列
}
}
book.write();//将sheet内容写入excel--否则excel会没有内容的
book.close();//不关闭会报错 ERROR: JDWP Unable to get JNI 1.2 environment, jvm->GetEnv() return code = -2
return false;
}
}