POI操作EXCELのエクスポートExcel(有効性設定、ドロップダウンリスト参照)
22625 ワード
本人はpoi-bin-3.10-FINAL-20140208.zipバージョンのpoiを使用しています.以下はプログラムのキーコードです.
//
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
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.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
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;//
public static String writeExcel(String str,List mList,String path) throws IOException {
Date dt = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String temp_str = "";
temp_str = sdf.format(dt);
//System.out.println(InExcel.class.getClassLoader().getResource("/").getPath());
// String path = request.getSession().getServletContext().getRealPath("/");
//System.out.println(path+"00000000000000");
String pathname=path+"upload\\"+str+temp_str+".xls";
String pathname1="upload\\"+str+temp_str+".xls";
File file=new File(pathname);
String fileName = file.getName();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
.substring(fileName.lastIndexOf(".") + 1);
//
if ("xls".equals(extension)) {
WriteIn(file,"xls");
String[][] data=check(str);
write2003Excel(file.getPath(),mList,data,str);
return pathname1;
} else if ("xlsx".equals(extension)) {
String[][] data=check(str);
// write2007Excel(file.getPath(),mList,data);
} else {
throw new IOException(" ");
}
return "error";
}// excel
public static String WriteIn(File file,String extension) throws IOException{
//2003xls
FileOutputStream fileOut = new FileOutputStream(file.getAbsolutePath());
if ("xls".equals(extension)) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("Sheet1");
HSSFSheet sheet2 = wb.createSheet("Sheet2");
HSSFSheet sheet3 = wb.createSheet("Sheet3");
wb.write(fileOut);
}else if ("xlsx".equals(extension)){
//2007
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Sheet1");
XSSFSheet sheet2 = wb.createSheet("Sheet2");
XSSFSheet sheet3 = wb.createSheet("Sheet3");
wb.write(fileOut);
}
fileOut.close();
return "success";
}/**
*
* @param str
* @return
*/
public static String[][] check(String str){
if("00201".equals(str)){
// ：
String[] bzbt={" "," "," "," "," "," "," "," "};
// ： 0- ,1- ,2- ,9-
String[] bzdata2={ "0- ","1- ","2- ","9- "};
//01- ,02- ,03- 、 、 ,04- ,05-
String[] bzdata3={ "01- ","02- ","03- 、 、 ","04- ","05- "};
//
String [] bzdatarow={"1","3"};
String[][] a ={bzbt,bzdata2,bzdata3,bzdatarow};
return a;
}
return null;
}//
private static HSSFDataValidation SetDataValidation(String strFormula,int firstRow,int firstCol,int endRow,int endCol)
{
//String formula = "Sheet2!$A$2:$A$59" ;// Z 1-N
// HSSFDataValidation dataValidation = new HSSFDataValidation((short) 1,
// (short) 1, (short) 300, (short) 1); //
// dataValidation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
// dataValidation.setFirstFormula(formula);
// dataValidation.setSecondFormula(null);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// CellRangeAddressList regions = new CellRangeAddressList( firstRow,
// (short) 300, (short) 1, (short) 1);//add
DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);//add
HSSFDataValidation dataValidation = new HSSFDataValidation(regions,constraint);//add
dataValidation.createErrorBox("Error", "Error");
dataValidation.createPromptBox("", null);
return dataValidation;
}
//255
public static DataValidation setDataValidation(Sheet sheet,String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
//
DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
// DVConstraint constraint = new DVConstraint();
constraint.setExplicitListValues(textList);
// 。
// ： 、 、 、
CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
//
DataValidation data_validation = helper.createValidation(constraint, regions);
//DataValidation data_validation = new DataValidation(regions, constraint);
return data_validation;
}//write 2003Excel
public static void write2003Excel(String filePath,List list,String[][] data,String str) {
try {
if(list.size()<=60000){
// excel
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
//
HSSFSheet sheet;
//
HSSFRow row = null;
//
HSSFCell cell = null;
sheet = wb.getSheetAt(0);
// sheet.addValidationData(setDataValidation(sheet,data[1], 1,list.size(), 1, 1));
String[] num=data[data.length-1];
System.out.println(num.length);
if (data.length>1) {
for(int i=0;i<num.length;i++){
int rowdata=Integer.parseInt(num[i]);
sheet.addValidationData(setDataValidation(sheet,data[i+1], 1,list.size(),rowdata , rowdata));
System.out.println(" "+rowdata);
}
}
if("00301".equals(str)){
String strFormula = "Sheet2!$A$2:$A$59" ;
sheet.addValidationData(SetDataValidation(strFormula, 1, 5, list.size(), 5));
}
row=sheet.createRow(0);
for (int i = 0; i < data[0].length; i++) {
cell=row.createCell(i);
cell.setCellValue(data[0][i]);
}
//
for (int i=1; i <=list.size(); i++) {
row = sheet.createRow(i);
//
for (int j=0; j<data[0].length; j++) {
cell = row.createCell(j);//
String m=String.valueOf(list.get(i-1)).replace("[", "").replace("]", "");
String[] a=m.split(",");
cell.setCellValue(a[j]);//
// cell.setCellFormula("Sheet2!$A$2:$A$59");
}
}
//select Ahd010401,Ahd010405,Ahd010404,Ahd010409,Ahd010410,Ahd010406,Ahd010408,Ahd010416 from res_00301
if("00301".equals(str)){
String[] bzdata4={" ","01- ","02- ","03- ","04- ","05- ","06- ","07- ","08- ","09- ","10- ","11- ","12- ","13- ","14- ","15- ","16- ","17- ","18- ","19- ","20- ","21- ","22- ","23- ","24- ","25- ","26- ","27- ","28- ","29- ","30- ","31- ","32- ","33- ","34- ","35- ","36- ","37- ","38- ","39- ","40- ","41- ","42- ","43- ","44- ","45- ","46- ","47- ","48- ","49- ","50- ","51- ","52- ","53- ","54- ","55- ","56- ","98- ","99- "};
sheet=wb.getSheetAt(1);
for (int i = 0; i < bzdata4.length; i++) {
row=sheet.createRow(i);
cell=row.createCell(0);
cell.setCellValue(bzdata4[i]);
}
}
FileOutputStream out = new FileOutputStream(filePath);
wb.write(out);
out.close();
}else {
throw new IOException(" excel ， 60000 ");
}
} catch (Exception e) {
e.printStackTrace();
}
}