博客指出一次性查询很多字段时赋值麻烦的问题，并给出解决办法。若对字段无要求，可将查询sql复制到EXCEL，在特定列输入内容后复制到开发工具，还介绍了通过角标进行查询数据赋值及多个查询赋值的方式。
如果一次性查询很多字段，这样赋值的时候就会很麻烦，怎么解决这个问题呢，如果对字段没什么要求的话建议以下方法
StringBuffer sql=new StringBuffer("");
1：把查询sql复制到EXCEL中；
在B列输入sql.append("
在D列输入");
复制粘贴到开发工具中
2:查询数据赋值
通过角标复制 第一个是1
while(rs.next()){
entity=new DBQueryIndex2Dto();
entity.setB1(rs.getString(1));
entity.setB2(rs.getString(2));
entity.setB3(rs.getString(3));
entity.setB4(rs.getString(4));
entity.setB5(rs.getString(5));
entity.setB6(rs.getString(6));
entity.setB7(rs.getString(7));
entity.setB8(rs.getString(8));
entity.setB9(rs.getDouble(9));
entity.setB10(rs.getDouble(10));
entity.setB11(rs.getDouble(11));
entity.setB12(rs.getDouble(12));
entity.setB13(rs.getDouble(13));
entity.setB14(rs.getDouble(14));
entity.setB15(rs.getDouble(15));
entity.setB16(rs.getDouble(16));
entity.setB17(rs.getDouble(17));
entity.setB18(rs.getDouble(18));
entity.setB19(rs.getDouble(19));
entity.setB20(rs.getDouble(20));
entity.setB21(rs.getDouble(21));
entity.setB22(rs.getDouble(22));
entity.setB23(rs.getDouble(23));
entity.setB24(rs.getDouble(24));
entity.setB25(rs.getDouble(25));
entity.setB26(rs.getDouble(26));
entity.setB27(rs.getDouble(27));
entity.setB28(rs.getDouble(28));
entity.setB29(rs.getDouble(29));
entity.setB30(rs.getDouble(30));
entity.setB31(rs.getDouble(31));
entity.setB32(rs.getDouble(32));
entity.setB33(rs.getDouble(33));
entity.setB34(rs.getDouble(34));
entity.setB35(rs.getDouble(35));
entity.setB36(rs.getDouble(36));
entity.setB37(rs.getDouble(37));
entity.setB38(rs.getDouble(38));
entity.setB39(rs.getDouble(39));
entity.setB40(rs.getDouble(40));
entity.setB41(rs.getDouble(41));
entity.setB42(rs.getDouble(42));
entity.setB43(rs.getDouble(43));
entity.setB44(rs.getDouble(44));
entity.setB45(rs.getDouble(45));
entity.setB46(rs.getDouble(46));
entity.setB47(rs.getDouble(47));
entity.setB48(rs.getDouble(48));
entity.setB49(rs.getDouble(49));
entity.setB50(rs.getDouble(50));
entity.setB51(rs.getDouble(51));
entity.setB52(rs.getDouble(52));
entity.setB53(rs.getDouble(53));
entity.setB54(rs.getDouble(54));
entity.setB55(rs.getDouble(55));
entity.setB56(rs.getDouble(56));
entity.setB57(rs.getDouble(57));
entity.setB58(rs.getDouble(58));
entity.setB59(rs.getDouble(59));
entity.setB60(rs.getDouble(60));
entity.setB61(rs.getDouble(61));
entity.setB62(rs.getDouble(62));
entity.setB63(rs.getDouble(63));
entity.setB64(rs.getDouble(64));
entity.setB65(rs.getDouble(65));
entity.setB66(rs.getDouble(66));
entity.setB67(rs.getDouble(67));
entity.setB68(rs.getDouble(68));
entity.setB69(rs.getDouble(69));
entity.setB70(rs.getDouble(70));
entity.setB71(rs.getDouble(71));
entity.setB72(rs.getDouble(72));
entity.setB73(rs.getDouble(73));
entity.setB74(rs.getDouble(74));
entity.setB75(rs.getDouble(75));
entity.setB76(rs.getDouble(76));
entity.setB77(rs.getDouble(77));
entity.setB78(rs.getDouble(78));
entity.setB79(rs.getDouble(79));
entity.setB80(rs.getDouble(80));
entity.setB81(rs.getDouble(81));
entity.setB82(rs.getDouble(82));
entity.setB83(rs.getDouble(83));
entity.setB84(rs.getDouble(84));
entity.setB85(rs.getDouble(85));
entity.setB86(rs.getDouble(86));
entity.setB87(rs.getDouble(87));
entity.setB88(rs.getDouble(88));
entity.setB89(rs.getDouble(89));
entity.setB90(rs.getDouble(90));
entity.setB91(rs.getDouble(91));
entity.setB92(rs.getDouble(92));
entity.setB93(rs.getDouble(93));
entity.setB94(rs.getDouble(94));
entity.setB95(rs.getDouble(95));
entity.setB96(rs.getDouble(96));
entity.setB97(rs.getDouble(97));
entity.setB98(rs.getDouble(98));
entity.setB99(rs.getDouble(99));
entity.setB100(rs.getDouble(100));
entity.setB101(rs.getDouble(101));
entity.setB102(rs.getDouble(102));
entity.setB103(rs.getDouble(103));
entity.setB104(rs.getDouble(104));
entity.setB105(rs.getDouble(105));
entity.setB106(rs.getDouble(106));
entity.setB107(rs.getDouble(107));
entity.setB108(rs.getDouble(108));
entity.setB109(rs.getDouble(109));
entity.setB110(rs.getDouble(110));
entity.setB111(rs.getDouble(111));
entity.setB112(rs.getDouble(112));
entity.setB113(rs.getDouble(113));
entity.setB114(rs.getDouble(114));
entity.setB115(rs.getDouble(115));
entity.setB116(rs.getDouble(116));
entity.setB117(rs.getDouble(117));
entity.setB118(rs.getDouble(118));
entity.setB119(rs.getDouble(119));
entity.setB120(rs.getDouble(120));
entity.setB121(rs.getDouble(121));
entity.setB122(rs.getDouble(122));
entity.setB123(rs.getDouble(123));
entity.setB124(rs.getDouble(124));
entity.setB125(rs.getDouble(125));
entity.setB126(rs.getDouble(126));
entity.setB127(rs.getDouble(127));
entity.setB128(rs.getDouble(128));
entity.setB129(rs.getDouble(129));
entity.setB130(rs.getDouble(130));
entity.setB131(rs.getDouble(131));
entity.setB132(rs.getDouble(132));
entity.setB133(rs.getDouble(133));
entity.setB134(rs.getDouble(134));
entity.setB135(rs.getDouble(135));
entity.setB136(rs.getDouble(136));
entity.setB137(rs.getDouble(137));
entity.setB138(rs.getDouble(138));
entity.setB139(rs.getDouble(139));
entity.setB140(rs.getDouble(140));
entity.setB141(rs.getDouble(141));
entity.setB142(rs.getDouble(142));
entity.setB143(rs.getDouble(143));
entity.setB144(rs.getDouble(144));
entity.setB145(rs.getDouble(145));
entity.setB146(rs.getDouble(146));
entity.setB147(rs.getDouble(147));
entity.setB148(rs.getDouble(148));
entity.setB149(rs.getDouble(149));
entity.setB150(rs.getDouble(150));
entity.setB151(rs.getDouble(151));
entity.setB152(rs.getDouble(152));
entity.setB153(rs.getDouble(153));
entity.setB154(rs.getDouble(154));
entity.setB155(rs.getDouble(155));
entity.setB156(rs.getDouble(156));
entity.setB157(rs.getDouble(157));
entity.setB158(rs.getDouble(158));
entity.setB159(rs.getDouble(159));
entity.setB160(rs.getDouble(160));
entity.setB161(rs.getDouble(161));
entity.setB162(rs.getDouble(162));
entity.setB163(rs.getDouble(163));
entity.setB164(rs.getDouble(164));
entity.setB165(rs.getDouble(165));
entity.setB166(rs.getDouble(166));
entity.setB167(rs.getDouble(167));
entity.setB168(rs.getDouble(168));
entity.setB169(rs.getDouble(169));
entity.setB170(rs.getDouble(170));
entity.setB171(rs.getDouble(171));
entity.setB172(rs.getDouble(172));
entity.setB173(rs.getDouble(173));
entity.setB174(rs.getDouble(174));
entity.setB175(rs.getDouble(175));
entity.setB176(rs.getDouble(176));
entity.setB177(rs.getDouble(177));
entity.setB178(rs.getDouble(178));
entity.setB179(rs.getDouble(179));
list.add(entity);
}
2：多个查询赋值
/**
*
* @param startdate
* @param enddate
* @param datatype
* @param path
* @param fileName
* @param modeFileName
* @return
*/
public String makeExcel4(String startdate,String enddate,String datatype,String path,String fileName,String modeFileName){
path=path+File.separator+"DownLoad";
//导出excel路径
String newUrl=path+File.separator+fileName;
//下载路径
String pathUrl=File.separator+"DownLoad"+File.separator+fileName;
//模板路径
String filePath=path+File.separator+modeFileName;
XSSFWorkbook wb = null;
FileInputStream fis = null;
File file = new File(filePath);
try {
if (file != null) {
fis = new FileInputStream(file);
wb = new XSSFWorkbook(fis);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
DBManager dbManager=null;
//存放查询的sql数据
ResultSet rs=null;
try{
dbManager=new DBManager();
dbManager.open(AppConfig.get("sysconst.reserveDataSource"));
XSSFSheet sheet=null;
sheet=wb.getSheetAt(5);
rs=queryACCSumpaid(startdate,dbManager,rs);
//单元格赋值方法 只需提供查询结果 开始行 写入的sheet
setCellValue(rs,1,sheet);
sheet.setForceFormulaRecalculation(true);
//
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(newUrl);
wb.write(fileOut);
wb.setForceFormulaRecalculation(true);
fileOut.flush();
fileOut.close();
sheet=null;
} catch (IOException e) {
e.printStackTrace();
}
}catch (Exception e) {
}finally{
try {
dbManager.close();
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return pathUrl;
}
/**
* 赋值到EXCEL共用方法
* @param rs 查询的数据
* @param startRow 开始赋值
* @param sheet 要赋值的sheet页
*/
public void setCellValue(ResultSet rs,Integer startRow,XSSFSheet sheet){
try {
Row row = null;
Integer rowCount=0;//初始行数=rowCount+startRow rowCount每次加一
ResultSetMetaData rsmd = rs.getMetaData() ; //获取字段属性
int columnCount = rsmd.getColumnCount();//获取查询结果的总列数
String colTypeName="NUMBER";
int flag = -1;//确认字段类型是不是数字类型，不是-1表示是数字类型
//遍历查询的数据
while(rs.next()){
//创建行
row=sheet.createRow(rowCount+startRow);//EXCEL的开始行
for(int i=0;i<columnCount;i++){
colTypeName=rsmd.getColumnTypeName(i+1);//获取字段类型
flag = colTypeName.toUpperCase().indexOf("NUMBER");
if(flag==-1){//不是数字类型
//创建单元格并赋值
row.createCell(i).setCellValue(rs.getString(i+1));
}else{//数字类型
//创建单元格并赋值
row.createCell(i).setCellValue(rs.getDouble(i+1));
}
}
rowCount++;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
扫一扫
19万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
