You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by saper1281 <sa...@yandex.ru> on 2009/06/09 09:34:34 UTC
Images and Exel
Help me, please.
In the Exel document need to subjoin sheet 69.
If in Exel document include image then Exel document return defective.
When use poi-bin-3.5-beta5-20090219 return error:
Exception in tread "main"
java.lang.NoSuchMethodError:org.apache.poi.hssf.usermodel.HSSFSheet.addMergedRegion
<Lorg/apache/poi/hssf/unit/Region;>I ......;
When use poi-bin-3.2-FINAL-20081019 return defective document, error:
Error initialization VBA(265), and appeal restore Exel document
Code:
HSSFWorkbook wb ;
InputStream fileInput = null;
try {
fileInput = new FileInputStream(Pyt);
POIFSFileSystem fs111= new POIFSFileSystem(new FileInputStream(Pyt));
wb = new HSSFWorkbook(fs111);
fileInput.close();
}
catch(Exception e){
wb = new HSSFWorkbook();
}
HSSFSheet sheet =null;
if(wb.getSheetIndex("sheet69")<0){
sheet =wb.createSheet("sheet69");
wb.setSheetName( wb.getSheetIndex("sheet69"), "sheet69",
HSSFWorkbook.ENCODING_UTF_16 );
}else
{
sheet=wb.getSheet("Лист69");
}
//----------
//-----------
OutputStream fileOut = new FileOutputStream(Pyt);
wb.write(fileOut);
fileOut.close();
--
View this message in context: http://www.nabble.com/Images-and-Exel-tp23937543p23937543.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Images and Exel
Posted by MSB <ma...@tiscali.co.uk>.
I have had the chance to look through the code you posted and can see no
reason why Excel should refuse to open the file. As far as I can see what
you are doing with POI is perfectly normal.
The only conclusion that I can draw is that there may be something in the
file you are opening - the file referenced by the variable Pyt in this line
of code fileInput = new FileInputStream(Pyt) - and modifying either that POI
cannot work with or that causes Excel to display an error message.
As a test, could I ask you to use POI just to copy the file under a new name
and then use Excel to try and open it? For example you could do something
like this;
FileInputStream inputStream = new FileInputStream(new File(Pyt));
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
FileOutputStream outputStream = new java.io.FileOutputStream(new
File(".............."));
workbook.write(outputStream);
You would need to replace the 'new File("..............")' phrase with a
suitable name.
I have not tracked down the true meaning of the error message Excel
displayed. It could mean that some of the resources needed by the
application are not accessible if you are working in a client server
environment.
Yours
Mark B
saper1281 wrote:
>
> Hello!
> It is necessary in existing Exel the document to add sheet Лист96.
> If the document contains in a case of figure all works successful (using
> libraries: poi-2.5.1-final-20040804, poi-contrib-2.5.1-final-20040804,
> poi-scratchpad-2.5.1-final-20040804).
> If in the document there is a figure that the damaged document comes back.
> I have recommendation to take advantage of newer libraries:
> I had downloaded:
> poi-bin-3.2-FINAL-20081019: <http: //
> mirror.prvtgeo.com/apache/poi/release/bin/>
> poi-bin-3.5-beta5-20090219: <http: //
> mirror.prvtgeo.com/apache/poi/dev/bin/>
> At their use there are errors:
>
> When I used poi-bin-3.5-beta5-20090219, at compilation there is a error:
> Exception in tread "main"
> java.lang.NoSuchMethodError:org.apache.poi.hssf.usermodel.HSSFSheet.addMergedRegion
> <Lorg/apache/poi/hssf/unit/Region;>I ......;
>
>
> When I used poi-bin-3.2-FINAL-20081019, after completion of the program,
> the document is damaged.
> error:
> Error of initialization VBA (265). Also suggests to restore the document.
>
> This is a full code of the program:
>
>
> package VC34;
> import VC34.classes.*;
> import java.io.*;
> import java.util.Date;
> import java.net.*;
>
> import javax.servlet.*;
> import javax.servlet.http.*;
> import javax.naming.InitialContext;
> import javax.sql.DataSource;
> import java.sql.*;
> import java.util.*;
> import javax.naming.*;
> import java.text.*;
>
> import java.lang.reflect.*;
> import org.apache.poi.hssf.util.Region;
> //import org.apache.poi.hssf.util.CellRangeAddress;
> import org.apache.poi.hssf.util.*;
> //import org.apache.poi.ss.util.Region;
> import org.apache.poi.hssf.model.Sheet.*;
> import org.apache.poi.hssf.usermodel.HSSFWorkbook.*;
> import org.apache.poi.hssf.usermodel.*;
> import org.apache.poi.hssf.usermodel.HSSFSheet.*;
> import org.apache.poi.poifs.filesystem.*;
> import org.apache.regexp.*;
> import org.apache.*;
> //import org.apache.struts.*;
> import javax.swing.*;
> import java.awt.event.*;
> import java.applet.*;
> /**
> *
> * @author apopovkin
> */
> public class VC34_Excel {
>
> /** Creates a new instance of VC34_Excel */
> public VC34_Excel() {
> }
>
> /**
> * @param args the command line arguments
> */
> public static void main(String[] args)throws Exception {
>
> // NumberFormat form = new DecimalFormat("#########0.0#");
>
> System.out.println(args[0]);
> System.out.println(args[1]);
> System.out.println(args[2]);
> System.out.println(args[3]);
> System.out.println(args[4]);
> System.out.println(args[5]);
> System.out.println(args.length);
> String beg_dt = "";
> String tper = args[4];
> String dbName ="";
> String UserID ="";
> String Password ="";
> String Pyt ="";
> String Driver ="COM.ibm.db2.jdbc.app.DB2Driver";
> PrintStream out=System.out;
> SimpleDateFormat dateFormatter1 = new
> SimpleDateFormat("yyyy-MM-dd");
> SimpleDateFormat dateFormatter2 = new
> SimpleDateFormat("dd-MM-yyyy");
> SimpleDateFormat dateFormatter3 = new SimpleDateFormat("dd-MM-yyyy
> HH:mm:ss");
>
> Calendar time1=Calendar.getInstance();
> out.print("Begin:
> "+dateFormatter3.format(time1.getTime()).toString()+"\n");
> try {
> if (args.length>=1){dbName+="db2:"+args[0];}
> if (args.length>=2){UserID=args[1];}
> if (args.length>=3){Password=args[2];}
> if (args.length>=4){beg_dt=args[3];} else beg_dt = "current
> date - 1 day";
> if (args.length>=5){Pyt=args[5];}
> }
> catch (Exception e){System.out.print("23"+e);System.exit(1);}
>
> if (dbName.equals("")||UserID.equals("")||Password.equals("")){
> System.out.print("Неверно заданны параметры. Формат
> параметров gen_stat db_alias user pass otchname year month \n По умолчанию
> устанавливается текущий год и предыдущий месяц");
> System.exit(0);
> }
> String dday = beg_dt.substring(0,2);
> String mmonth = beg_dt.substring(3,5);
> String yyear = beg_dt.substring(6,10);
> String End_dt=yyear+"-"+mmonth+"-"+dday;//конец
> String DT_OTCH_SYT_F2=dday+"-"+mmonth+"-"+yyear;
> String Beg_dt=yyear+"-"+mmonth+"-"+"01";//начало
> String DT_NACH_MES_F2="01"+"-"+mmonth+"-"+yyear;
> String
> GEnd_dt=String.valueOf(Integer.parseInt(yyear)-1)+"-"+mmonth+"-"+dday;//конец
> Г
> String
> GBeg_dt=String.valueOf(Integer.parseInt(yyear)-1)+"-"+mmonth+"-"+"01";//начало
> Г
> Context initContext = null;
>
>
>
>
>
>
>
> Vector report_VC_0 = new Vector();
> Vector report_VC_1 = new Vector();
> Vector report_VC_T = new Vector();
> Connection connection_mm = null;
> ResultSet resultSet_mm = null;
> ResultSet resultSet_nod = null;
>
>
>
>
>
> try {
> Class.forName(Driver);
> connection_mm = DriverManager.getConnection("jdbc:"+dbName ,
> UserID, Password);
> Calendar time=Calendar.getInstance();
> out.print("DB conect open OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> }
> catch(Exception e){
> out.print("DB error"+e+"\n");
> System.exit(1);}
>
> try{
>
> PreparedStatement st1=null;
>
>
> st1 = connection_mm.prepareStatement(
>
> //SQL---------------
>
>
> //SQL---------------
>
> );
> resultSet_nod=st1.executeQuery();
>
>
>
>
>
> while (resultSet_nod.next()) {
>
>
> VC34_Excel_ line0 =new VC34_Excel_();
> line0.setDu_Sn(resultSet_nod.getString("co4_r3_1"));
> line0.setDu_num(resultSet_nod.getInt("co4_r3_0"));
>
>
> line0.setco4_r1_13a(resultSet_nod.getFloat("co4_r1_13a"));
> line0.setco4_r1_13(resultSet_nod.getDouble("co4_r1_13"));
> line0.setco4_r1_14(resultSet_nod.getDouble("co4_r1_14"));
> line0.setco4_r3_3(resultSet_nod.getDouble("co4_r3_3"));
> line0.setYch_speedM(resultSet_nod.getDouble("co4_r3_22"));
> line0.setYch_speedD(resultSet_nod.getDouble("co4_r3_22d"));
> line0.setYch_speedNorm(resultSet_nod.getDouble("sp_u"));
> line0.setVt(resultSet_nod.getInt("VT"));
>
> report_VC_0.add(line0);
>
>
>
>
> }
>
>
>
> resultSet_nod.close();
> st1.close();
>
> }catch (SQLException e) {
> e.printStackTrace(); }
> //закрытие соед.
> try {
> connection_mm.close();
> Calendar time=Calendar.getInstance();
> out.print("DB conect close OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> } catch (SQLException e) {
> e.printStackTrace(); //To change body of catch statement use
> File | Settings | File Templates.
> }
>
>
>
>
>
> try {
> Class.forName(Driver);
> connection_mm = DriverManager.getConnection("jdbc:"+dbName ,
> UserID, Password);
> Calendar time=Calendar.getInstance();
> out.print("DB conect open OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> }
> catch(Exception e){
> out.print("DB error"+e+"\n");
> System.exit(1);}
>
> try{
>
> PreparedStatement st1=null;
> st1 = connection_mm.prepareStatement(
> "Select CLASS.VT.vt_sn,CLASS.VT.VT from CLASS.VT ORDER BY VT "
>
> );
> resultSet_nod=st1.executeQuery();
>
>
>
>
>
> while (resultSet_nod.next()) {
>
>
>
>
> VC34_Excel_ line1 =new VC34_Excel_();
> line1.setVt_sn(resultSet_nod.getString("vt_sn"));
> line1.setVt(resultSet_nod.getInt("vt"));
> report_VC_1.add(line1);
> }
>
>
>
>
>
>
> resultSet_nod.close();
> st1.close();
>
> }catch (SQLException e) {
> e.printStackTrace(); }
> //закрытие соед.
> try {
> connection_mm.close();
> Calendar time=Calendar.getInstance();
> out.print("DB conect close OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> } catch (SQLException e) {
> e.printStackTrace(); //To change body of catch statement use
> File | Settings | File Templates.
> }
> //NumberFormat form = new DecimalFormat("#########0.##");
> NumberFormat form = new DecimalFormat("#########0.0#");
>
>
>
>
>
> HSSFWorkbook wb ;
>
>
> InputStream fileInput = null;
>
> try {
> //FileDescriptor t=new FileDescriptor
> fileInput = new FileInputStream(Pyt);
>
> POIFSFileSystem fs111= new POIFSFileSystem(new
> FileInputStream(Pyt));
>
> wb = new HSSFWorkbook(fs111);
> // wb = new HSSFWorkbook(fileInput);
> out.println("1");
> // fileInput.close();
> }
> catch(Exception e){
>
> wb = new HSSFWorkbook();
> }
>
>
>
> HSSFSheet sheet =null;
> out.println("index69_1="+wb.getSheetIndex("Лист69"));
>
> if(wb.getSheetIndex("Лист69")<0){
> sheet =wb.createSheet("Лист69");
> wb.setSheetName( wb.getSheetIndex("Лист69"), "Лист69",
> HSSFWorkbook.ENCODING_UTF_16 );
>
>
> out.println("index69_2="+wb.getSheetName(wb.getSheetIndex("Лист69")));
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> }else
> { out.println("index69_3="+wb.getSheetIndex("Лист69"));
> sheet=wb.getSheet("Лист69");
>
>
>
> out.println("index69_4="+wb.getSheetName(wb.getSheetIndex("Лист69")));
>
>
> }
>
>
>
>
>
>
> sheet.setColumnWidth((short)0,(short)2200);
> sheet.setColumnWidth((short)1,(short)5000);
> sheet.setColumnWidth((short)2,(short)4200);
> sheet.setColumnWidth((short)3,(short)4200);
> sheet.setColumnWidth((short)4,(short)4200);
> sheet.setColumnWidth((short)5,(short)4200);
>
> //---------делаем альбомную страницу----------------------
> HSSFPrintSetup print = sheet.getPrintSetup();
> print.setScale((short)83);
> print.setPaperSize((short) print.A4_PAPERSIZE);
>
>
> String f1="ОТЧЕТ";
> String f2="Анализ участковой скорости по диспетчерским участкам";
> String f3="";
> if(tper.equals("1")){
> f3="За "+DT_OTCH_SYT_F2+" г.";}
> if(tper.equals("2")){
> f3="За период с "+DT_NACH_MES_F2+" по "+DT_OTCH_SYT_F2+" г.";}
> String f4="Дорога ЮВОСТ";
> String f5="";
>
>
> HSSFFont font = wb.createFont();
> font.setFontHeightInPoints((short)10);//шрифт
> font.setFontName("Arial");
> font.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
>
>
>
>
>
>
> HSSFFont font1 = wb.createFont();
> font1.setFontHeightInPoints((short)13);//шрифт
> font1.setFontName("Arial");
> font1.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
>
> HSSFCellStyle styleN = wb.createCellStyle();
> styleN.setFillBackgroundColor((short)12);
> styleN.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> styleN.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> styleN.setWrapText(true);
> styleN.setFont(font);
>
> HSSFCellStyle styleN1 = wb.createCellStyle();
> styleN1.setFillBackgroundColor((short)12);
> styleN1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> styleN1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> styleN1.setFillBackgroundColor((short)155);
> styleN1.setWrapText(true);
>
> styleN1.setFont(font1);
>
>
>
>
>
>
>
>
> HSSFCellStyle style = wb.createCellStyle();
> style.setFillBackgroundColor((short)12);
> style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style.setFont(font);
> //style.setWrapText(true);
>
> HSSFCellStyle style0 = wb.createCellStyle();
>
> style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> style0.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
>
> style0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> style0.setFont(font);
> style0.setWrapText(true);
>
> HSSFCellStyle style0_0 = wb.createCellStyle();
>
> style0_0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> style0_0.setFillForegroundColor(HSSFColor.YELLOW.index);
>
> style0_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0_0.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> style0_0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> style0_0.setFont(font);
> style0_0.setWrapText(true);
>
>
>
>
>
>
>
>
> HSSFCellStyle style0_2 = wb.createCellStyle();
> style0_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> style0_2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
> style0_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0_2.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> style0_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> style0_2.setFont(font);
> style0_2.setWrapText(true);
> style0_2.setRotation((short)90);//вертикальный текст
>
>
>
>
> HSSFCellStyle style0_1 = wb.createCellStyle();
> style0_1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0_1.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0_1.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
>
> style0_1.setFont(font);
> style0_1.setWrapText(true);
>
>
>
>
>
>
> HSSFCellStyle style0_1_0 = wb.createCellStyle();
> style0_1_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_1_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_1_0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
>
> style0_1_0.setFont(font);
> style0_1_0.setWrapText(true);
>
>
>
>
>
>
>
> HSSFRow rowN1 = sheet.createRow(0);
> HSSFCell cellN1 = rowN1.createCell((short)0);
>
> cellN1.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN1.setCellValue(f1);
> cellN1.setCellStyle(styleN1);
>
>
> Region mmmm= new Region(0,(short)0,1,(short)5);
>
>
> sheet.addMergedRegion(mmmm);
>
> HSSFRow rowN2 = sheet.createRow(2);//начиная с какой строчки
> (+1=строка)
> HSSFCell cellN2 = rowN2.createCell((short)0);//начиная со столбца
> (+1=столбц)
> cellN2.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN2.setCellValue(f2);
> cellN2.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(2,(short)0,3,(short)5));
>
>
> HSSFRow rowN3 = sheet.createRow(4);//начиная с какой строчки
> (+1=строка)
> HSSFCell cellN3 = rowN3.createCell((short)0);//начиная со столбца
> (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN3.setCellValue(f5);
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(4,(short)0,5,(short)1));
>
>
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN3.setCellValue(f4);
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(4,(short)2,5,(short)3));
>
>
> cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN3.setCellValue(f3);
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(4,(short)4,5,(short)5));
>
>
>
>
>
> int k_min=6;
> int k_max=7;
>
>
>
> for (int i1=0;i1<report_VC_1.size();i1++){
> VC34_Excel_ line1 =(VC34_Excel_)report_VC_1.get(i1);
> String vt_snstr=line1.getVt_sn();
> int vtstr=line1.getVt();
> //все
> for (int i0=0;i0<report_VC_0.size();i0++){
> VC34_Excel_ line0 =(VC34_Excel_)report_VC_0.get(i0);
> if (line0.getVt()==vtstr )
>
> {
> if ((line0.getVt()==1)
> &&((line0.getDu_num()==1)||(line0.getDu_num()==2)||(line0.getDu_num()==6)||
>
> (line0.getDu_num()==7)||(line0.getDu_num()==8)||(line0.getDu_num()==9)||
>
> (line0.getDu_num()==10)||(line0.getDu_num()==11)||(line0.getDu_num()==12)||
>
> (line0.getDu_num()==13)||(line0.getDu_num()==15)||(line0.getDu_num()==17)||
>
> (line0.getDu_num()==19)||(line0.getDu_num()==20)||(line0.getDu_num()==26)||
> (line0.getDu_num()==27)
> ))
> {
>
>
> if((line0.getYch_speedM()+line0.getYch_speedD())!=0
> )
> {
> VC34_Excel_ line3 =new VC34_Excel_();
> line3.setYch_speedM(line0.getYch_speedM());
> line3.setYch_speedD(line0.getYch_speedD());
> line3.setDu_Sn(line0.getDu_Sn());
> line3.setDu_num(line0.getDu_num());
> line3.setYch_speedNorm(line0.getYch_speedNorm());
> line3.setco4_r1_13a(line0.getco4_r1_13a());
> line3.setco4_r1_13(line0.getco4_r1_13());
> line3.setco4_r1_14(line0.getco4_r1_14());
> line3.setco4_r3_3(line0.getco4_r3_3());
> report_VC_T.add(line3); }
> }
>
>
> if ((line0.getVt()==2)
> &&((line0.getDu_num()==3)||(line0.getDu_num()==4)||(line0.getDu_num()==5)||
>
> (line0.getDu_num()==14)||(line0.getDu_num()==16)||(line0.getDu_num()==18)||
>
> (line0.getDu_num()==21)||(line0.getDu_num()==22)||(line0.getDu_num()==23)||
>
> (line0.getDu_num()==24)||(line0.getDu_num()==25)||(line0.getDu_num()==28)||
> (line0.getDu_num()==29)
> ))
> {
>
>
> if((line0.getYch_speedM()+line0.getYch_speedD())!=0
> )
> {
> VC34_Excel_ line3 =new VC34_Excel_();
> line3.setYch_speedM(line0.getYch_speedM());
> line3.setYch_speedD(line0.getYch_speedD());
> line3.setDu_Sn(line0.getDu_Sn());
> line3.setDu_num(line0.getDu_num());
> line3.setYch_speedNorm(line0.getYch_speedNorm());
> line3.setco4_r1_13a(line0.getco4_r1_13a());
> line3.setco4_r1_13(line0.getco4_r1_13());
> line3.setco4_r1_14(line0.getco4_r1_14());
> line3.setco4_r3_3(line0.getco4_r3_3());
> report_VC_T.add(line3); }
> }
>
>
>
>
>
>
>
>
>
>
> }
> }//все
>
> if (report_VC_T.size()!=0){
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(line1.getVt_sn());
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)1));
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("");
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(k_min,(short)2,k_min+1,(short)5));
>
>
> k_min=k_min+2;
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("№ ДУ");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)0));
>
> cellN3 = rowN3.createCell((short)1);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Диспетчерский участок");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)1,k_min+1,(short)1));
>
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Участковая скорость, норма");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)2,k_min+1,(short)2));
>
> cellN3 = rowN3.createCell((short)3);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Участковая скорость, сутки");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)3,k_min+1,(short)3));
>
> cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Участковая скорость, с/сутки");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)4,k_min+1,(short)4));
>
> cellN3 = rowN3.createCell((short)5);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("+/- к норме");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)5,k_min+1,(short)5));
>
>
>
>
> for (int w=0;w<=5;w++ )
> { for (int k=k_min+1; k<=k_min+1;k++) {
> rowN3 = sheet.createRow(k);
> cellN3 = rowN3.createCell((short)w);//начиная со столбца (+1=столбц)
> cellN3.setCellStyle(style0);}}
>
>
>
>
>
> k_min=k_min+2;
>
>
>
> for (int i3=0;i3<report_VC_T.size();i3++){
> VC34_Excel_ line3 =(VC34_Excel_)report_VC_T.get(i3);
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки
> (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(line3.getDu_num());
>
> cellN3.setCellStyle(style0_1);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min,(short)0));
>
>
> cellN3 = rowN3.createCell((short)1);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(line3.getDu_Sn());
> if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
> sheet.addMergedRegion(new Region(k_min,(short)1,k_min,(short)1));
>
>
>
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(form.format((line3.getYch_speedNorm())));
> cellN3.setCellStyle(style0_1);
> sheet.addMergedRegion(new Region(k_min,(short)2,k_min,(short)2));
>
>
>
>
>
>
>
>
>
>
> cellN3 = rowN3.createCell((short)3);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(form.format(line3.getYch_speedD()));
> if((line3.getYch_speedD()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
>
> sheet.addMergedRegion(new Region(k_min,(short)3,k_min,(short)3));
>
> cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(form.format(line3.getYch_speedM()));
> if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
>
> sheet.addMergedRegion(new Region(k_min,(short)4,k_min,(short)4));
>
> cellN3 = rowN3.createCell((short)5);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
>
> cellN3.setCellValue(form.format((line3.getYch_speedM()-line3.getYch_speedNorm())));
>
> if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
>
> sheet.addMergedRegion(new Region(k_min,(short)5,k_min,(short)5));
> k_min=k_min+1;
>
>
>
> }//line3
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("");
> cellN3.setCellStyle(style0_1_0);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)5));
>
> k_min=k_min+2;
>
>
>
>
> report_VC_T.clear();
> }//size
> }//тяга
>
> OutputStream fileOut = new FileOutputStream(Pyt);
>
> // POIFSFileSystem fs112= new POIFSFileSystem(new
> FileOutputStream(Pyt));
>
> wb.write(fileOut);
> fileOut.close();
>
>
>
>
>
>
>
>
>
>
> }
>
> }
>
>
>
>
> Give me please advise that it is possible to make in the given situation.
> Sorry, I have a bad english.
>
>
--
View this message in context: http://www.nabble.com/Images-and-Exel-tp23937543p23984458.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Images and Exel
Posted by MSB <ma...@tiscali.co.uk>.
Please do not think that you need to apologise for your English. It's fine
and I understand what you are asking now I think.
Just to be clear in my own mind, the code will not compile using version 3.5
but it does if you switch to version 3.2. The compiled code will run and it
creates an Excel spreadsheet file for you but the file is damaged in some
way; when you try to open it, you see this 'Error initialising VBA(265)'
message.
Can I ask, what happens if you accept Excel's offer to restore the document?
In the meantime, I will have a look through your code to see if anything
obvious jumps out at me and I will also see if I can find out what Excel's
error message means.
Will post if I find anything.
Yours
Mark B
saper1281 wrote:
>
> Hello!
> It is necessary in existing Exel the document to add sheet Лист96.
> If the document contains in a case of figure all works successful (using
> libraries: poi-2.5.1-final-20040804, poi-contrib-2.5.1-final-20040804,
> poi-scratchpad-2.5.1-final-20040804).
> If in the document there is a figure that the damaged document comes back.
> I have recommendation to take advantage of newer libraries:
> I had downloaded:
> poi-bin-3.2-FINAL-20081019: <http: //
> mirror.prvtgeo.com/apache/poi/release/bin/>
> poi-bin-3.5-beta5-20090219: <http: //
> mirror.prvtgeo.com/apache/poi/dev/bin/>
> At their use there are errors:
>
> When I used poi-bin-3.5-beta5-20090219, at compilation there is a error:
> Exception in tread "main"
> java.lang.NoSuchMethodError:org.apache.poi.hssf.usermodel.HSSFSheet.addMergedRegion
> <Lorg/apache/poi/hssf/unit/Region;>I ......;
>
>
> When I used poi-bin-3.2-FINAL-20081019, after completion of the program,
> the document is damaged.
> error:
> Error of initialization VBA (265). Also suggests to restore the document.
>
> This is a full code of the program:
>
>
> package VC34;
> import VC34.classes.*;
> import java.io.*;
> import java.util.Date;
> import java.net.*;
>
> import javax.servlet.*;
> import javax.servlet.http.*;
> import javax.naming.InitialContext;
> import javax.sql.DataSource;
> import java.sql.*;
> import java.util.*;
> import javax.naming.*;
> import java.text.*;
>
> import java.lang.reflect.*;
> import org.apache.poi.hssf.util.Region;
> //import org.apache.poi.hssf.util.CellRangeAddress;
> import org.apache.poi.hssf.util.*;
> //import org.apache.poi.ss.util.Region;
> import org.apache.poi.hssf.model.Sheet.*;
> import org.apache.poi.hssf.usermodel.HSSFWorkbook.*;
> import org.apache.poi.hssf.usermodel.*;
> import org.apache.poi.hssf.usermodel.HSSFSheet.*;
> import org.apache.poi.poifs.filesystem.*;
> import org.apache.regexp.*;
> import org.apache.*;
> //import org.apache.struts.*;
> import javax.swing.*;
> import java.awt.event.*;
> import java.applet.*;
> /**
> *
> * @author apopovkin
> */
> public class VC34_Excel {
>
> /** Creates a new instance of VC34_Excel */
> public VC34_Excel() {
> }
>
> /**
> * @param args the command line arguments
> */
> public static void main(String[] args)throws Exception {
>
> // NumberFormat form = new DecimalFormat("#########0.0#");
>
> System.out.println(args[0]);
> System.out.println(args[1]);
> System.out.println(args[2]);
> System.out.println(args[3]);
> System.out.println(args[4]);
> System.out.println(args[5]);
> System.out.println(args.length);
> String beg_dt = "";
> String tper = args[4];
> String dbName ="";
> String UserID ="";
> String Password ="";
> String Pyt ="";
> String Driver ="COM.ibm.db2.jdbc.app.DB2Driver";
> PrintStream out=System.out;
> SimpleDateFormat dateFormatter1 = new
> SimpleDateFormat("yyyy-MM-dd");
> SimpleDateFormat dateFormatter2 = new
> SimpleDateFormat("dd-MM-yyyy");
> SimpleDateFormat dateFormatter3 = new SimpleDateFormat("dd-MM-yyyy
> HH:mm:ss");
>
> Calendar time1=Calendar.getInstance();
> out.print("Begin:
> "+dateFormatter3.format(time1.getTime()).toString()+"\n");
> try {
> if (args.length>=1){dbName+="db2:"+args[0];}
> if (args.length>=2){UserID=args[1];}
> if (args.length>=3){Password=args[2];}
> if (args.length>=4){beg_dt=args[3];} else beg_dt = "current
> date - 1 day";
> if (args.length>=5){Pyt=args[5];}
> }
> catch (Exception e){System.out.print("23"+e);System.exit(1);}
>
> if (dbName.equals("")||UserID.equals("")||Password.equals("")){
> System.out.print("Неверно заданны параметры. Формат
> параметров gen_stat db_alias user pass otchname year month \n По умолчанию
> устанавливается текущий год и предыдущий месяц");
> System.exit(0);
> }
> String dday = beg_dt.substring(0,2);
> String mmonth = beg_dt.substring(3,5);
> String yyear = beg_dt.substring(6,10);
> String End_dt=yyear+"-"+mmonth+"-"+dday;//конец
> String DT_OTCH_SYT_F2=dday+"-"+mmonth+"-"+yyear;
> String Beg_dt=yyear+"-"+mmonth+"-"+"01";//начало
> String DT_NACH_MES_F2="01"+"-"+mmonth+"-"+yyear;
> String
> GEnd_dt=String.valueOf(Integer.parseInt(yyear)-1)+"-"+mmonth+"-"+dday;//конец
> Г
> String
> GBeg_dt=String.valueOf(Integer.parseInt(yyear)-1)+"-"+mmonth+"-"+"01";//начало
> Г
> Context initContext = null;
>
>
>
>
>
>
>
> Vector report_VC_0 = new Vector();
> Vector report_VC_1 = new Vector();
> Vector report_VC_T = new Vector();
> Connection connection_mm = null;
> ResultSet resultSet_mm = null;
> ResultSet resultSet_nod = null;
>
>
>
>
>
> try {
> Class.forName(Driver);
> connection_mm = DriverManager.getConnection("jdbc:"+dbName ,
> UserID, Password);
> Calendar time=Calendar.getInstance();
> out.print("DB conect open OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> }
> catch(Exception e){
> out.print("DB error"+e+"\n");
> System.exit(1);}
>
> try{
>
> PreparedStatement st1=null;
>
>
> st1 = connection_mm.prepareStatement(
>
> //SQL---------------
>
>
> //SQL---------------
>
> );
> resultSet_nod=st1.executeQuery();
>
>
>
>
>
> while (resultSet_nod.next()) {
>
>
> VC34_Excel_ line0 =new VC34_Excel_();
> line0.setDu_Sn(resultSet_nod.getString("co4_r3_1"));
> line0.setDu_num(resultSet_nod.getInt("co4_r3_0"));
>
>
> line0.setco4_r1_13a(resultSet_nod.getFloat("co4_r1_13a"));
> line0.setco4_r1_13(resultSet_nod.getDouble("co4_r1_13"));
> line0.setco4_r1_14(resultSet_nod.getDouble("co4_r1_14"));
> line0.setco4_r3_3(resultSet_nod.getDouble("co4_r3_3"));
> line0.setYch_speedM(resultSet_nod.getDouble("co4_r3_22"));
> line0.setYch_speedD(resultSet_nod.getDouble("co4_r3_22d"));
> line0.setYch_speedNorm(resultSet_nod.getDouble("sp_u"));
> line0.setVt(resultSet_nod.getInt("VT"));
>
> report_VC_0.add(line0);
>
>
>
>
> }
>
>
>
> resultSet_nod.close();
> st1.close();
>
> }catch (SQLException e) {
> e.printStackTrace(); }
> //закрытие соед.
> try {
> connection_mm.close();
> Calendar time=Calendar.getInstance();
> out.print("DB conect close OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> } catch (SQLException e) {
> e.printStackTrace(); //To change body of catch statement use
> File | Settings | File Templates.
> }
>
>
>
>
>
> try {
> Class.forName(Driver);
> connection_mm = DriverManager.getConnection("jdbc:"+dbName ,
> UserID, Password);
> Calendar time=Calendar.getInstance();
> out.print("DB conect open OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> }
> catch(Exception e){
> out.print("DB error"+e+"\n");
> System.exit(1);}
>
> try{
>
> PreparedStatement st1=null;
> st1 = connection_mm.prepareStatement(
> "Select CLASS.VT.vt_sn,CLASS.VT.VT from CLASS.VT ORDER BY VT "
>
> );
> resultSet_nod=st1.executeQuery();
>
>
>
>
>
> while (resultSet_nod.next()) {
>
>
>
>
> VC34_Excel_ line1 =new VC34_Excel_();
> line1.setVt_sn(resultSet_nod.getString("vt_sn"));
> line1.setVt(resultSet_nod.getInt("vt"));
> report_VC_1.add(line1);
> }
>
>
>
>
>
>
> resultSet_nod.close();
> st1.close();
>
> }catch (SQLException e) {
> e.printStackTrace(); }
> //закрытие соед.
> try {
> connection_mm.close();
> Calendar time=Calendar.getInstance();
> out.print("DB conect close OK_kol_mm
> time="+dateFormatter3.format(time.getTime()).toString()+"\n");
> } catch (SQLException e) {
> e.printStackTrace(); //To change body of catch statement use
> File | Settings | File Templates.
> }
> //NumberFormat form = new DecimalFormat("#########0.##");
> NumberFormat form = new DecimalFormat("#########0.0#");
>
>
>
>
>
> HSSFWorkbook wb ;
>
>
> InputStream fileInput = null;
>
> try {
> //FileDescriptor t=new FileDescriptor
> fileInput = new FileInputStream(Pyt);
>
> POIFSFileSystem fs111= new POIFSFileSystem(new
> FileInputStream(Pyt));
>
> wb = new HSSFWorkbook(fs111);
> // wb = new HSSFWorkbook(fileInput);
> out.println("1");
> // fileInput.close();
> }
> catch(Exception e){
>
> wb = new HSSFWorkbook();
> }
>
>
>
> HSSFSheet sheet =null;
> out.println("index69_1="+wb.getSheetIndex("Лист69"));
>
> if(wb.getSheetIndex("Лист69")<0){
> sheet =wb.createSheet("Лист69");
> wb.setSheetName( wb.getSheetIndex("Лист69"), "Лист69",
> HSSFWorkbook.ENCODING_UTF_16 );
>
>
> out.println("index69_2="+wb.getSheetName(wb.getSheetIndex("Лист69")));
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> }else
> { out.println("index69_3="+wb.getSheetIndex("Лист69"));
> sheet=wb.getSheet("Лист69");
>
>
>
> out.println("index69_4="+wb.getSheetName(wb.getSheetIndex("Лист69")));
>
>
> }
>
>
>
>
>
>
> sheet.setColumnWidth((short)0,(short)2200);
> sheet.setColumnWidth((short)1,(short)5000);
> sheet.setColumnWidth((short)2,(short)4200);
> sheet.setColumnWidth((short)3,(short)4200);
> sheet.setColumnWidth((short)4,(short)4200);
> sheet.setColumnWidth((short)5,(short)4200);
>
> //---------делаем альбомную страницу----------------------
> HSSFPrintSetup print = sheet.getPrintSetup();
> print.setScale((short)83);
> print.setPaperSize((short) print.A4_PAPERSIZE);
>
>
> String f1="ОТЧЕТ";
> String f2="Анализ участковой скорости по диспетчерским участкам";
> String f3="";
> if(tper.equals("1")){
> f3="За "+DT_OTCH_SYT_F2+" г.";}
> if(tper.equals("2")){
> f3="За период с "+DT_NACH_MES_F2+" по "+DT_OTCH_SYT_F2+" г.";}
> String f4="Дорога ЮВОСТ";
> String f5="";
>
>
> HSSFFont font = wb.createFont();
> font.setFontHeightInPoints((short)10);//шрифт
> font.setFontName("Arial");
> font.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
>
>
>
>
>
>
> HSSFFont font1 = wb.createFont();
> font1.setFontHeightInPoints((short)13);//шрифт
> font1.setFontName("Arial");
> font1.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
>
> HSSFCellStyle styleN = wb.createCellStyle();
> styleN.setFillBackgroundColor((short)12);
> styleN.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> styleN.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> styleN.setWrapText(true);
> styleN.setFont(font);
>
> HSSFCellStyle styleN1 = wb.createCellStyle();
> styleN1.setFillBackgroundColor((short)12);
> styleN1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> styleN1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> styleN1.setFillBackgroundColor((short)155);
> styleN1.setWrapText(true);
>
> styleN1.setFont(font1);
>
>
>
>
>
>
>
>
> HSSFCellStyle style = wb.createCellStyle();
> style.setFillBackgroundColor((short)12);
> style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style.setFont(font);
> //style.setWrapText(true);
>
> HSSFCellStyle style0 = wb.createCellStyle();
>
> style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> style0.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
>
> style0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> style0.setFont(font);
> style0.setWrapText(true);
>
> HSSFCellStyle style0_0 = wb.createCellStyle();
>
> style0_0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> style0_0.setFillForegroundColor(HSSFColor.YELLOW.index);
>
> style0_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0_0.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> style0_0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> style0_0.setFont(font);
> style0_0.setWrapText(true);
>
>
>
>
>
>
>
>
> HSSFCellStyle style0_2 = wb.createCellStyle();
> style0_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> style0_2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
> style0_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0_2.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
> style0_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
> style0_2.setFont(font);
> style0_2.setWrapText(true);
> style0_2.setRotation((short)90);//вертикальный текст
>
>
>
>
> HSSFCellStyle style0_1 = wb.createCellStyle();
> style0_1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
> style0_1.setBorderRight(HSSFCellStyle.BORDER_THIN);
> style0_1.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
>
> style0_1.setFont(font);
> style0_1.setWrapText(true);
>
>
>
>
>
>
> HSSFCellStyle style0_1_0 = wb.createCellStyle();
> style0_1_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
> style0_1_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
> style0_1_0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
>
> style0_1_0.setFont(font);
> style0_1_0.setWrapText(true);
>
>
>
>
>
>
>
> HSSFRow rowN1 = sheet.createRow(0);
> HSSFCell cellN1 = rowN1.createCell((short)0);
>
> cellN1.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN1.setCellValue(f1);
> cellN1.setCellStyle(styleN1);
>
>
> Region mmmm= new Region(0,(short)0,1,(short)5);
>
>
> sheet.addMergedRegion(mmmm);
>
> HSSFRow rowN2 = sheet.createRow(2);//начиная с какой строчки
> (+1=строка)
> HSSFCell cellN2 = rowN2.createCell((short)0);//начиная со столбца
> (+1=столбц)
> cellN2.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN2.setCellValue(f2);
> cellN2.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(2,(short)0,3,(short)5));
>
>
> HSSFRow rowN3 = sheet.createRow(4);//начиная с какой строчки
> (+1=строка)
> HSSFCell cellN3 = rowN3.createCell((short)0);//начиная со столбца
> (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN3.setCellValue(f5);
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(4,(short)0,5,(short)1));
>
>
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN3.setCellValue(f4);
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(4,(short)2,5,(short)3));
>
>
> cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> cellN3.setCellValue(f3);
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(4,(short)4,5,(short)5));
>
>
>
>
>
> int k_min=6;
> int k_max=7;
>
>
>
> for (int i1=0;i1<report_VC_1.size();i1++){
> VC34_Excel_ line1 =(VC34_Excel_)report_VC_1.get(i1);
> String vt_snstr=line1.getVt_sn();
> int vtstr=line1.getVt();
> //все
> for (int i0=0;i0<report_VC_0.size();i0++){
> VC34_Excel_ line0 =(VC34_Excel_)report_VC_0.get(i0);
> if (line0.getVt()==vtstr )
>
> {
> if ((line0.getVt()==1)
> &&((line0.getDu_num()==1)||(line0.getDu_num()==2)||(line0.getDu_num()==6)||
>
> (line0.getDu_num()==7)||(line0.getDu_num()==8)||(line0.getDu_num()==9)||
>
> (line0.getDu_num()==10)||(line0.getDu_num()==11)||(line0.getDu_num()==12)||
>
> (line0.getDu_num()==13)||(line0.getDu_num()==15)||(line0.getDu_num()==17)||
>
> (line0.getDu_num()==19)||(line0.getDu_num()==20)||(line0.getDu_num()==26)||
> (line0.getDu_num()==27)
> ))
> {
>
>
> if((line0.getYch_speedM()+line0.getYch_speedD())!=0
> )
> {
> VC34_Excel_ line3 =new VC34_Excel_();
> line3.setYch_speedM(line0.getYch_speedM());
> line3.setYch_speedD(line0.getYch_speedD());
> line3.setDu_Sn(line0.getDu_Sn());
> line3.setDu_num(line0.getDu_num());
> line3.setYch_speedNorm(line0.getYch_speedNorm());
> line3.setco4_r1_13a(line0.getco4_r1_13a());
> line3.setco4_r1_13(line0.getco4_r1_13());
> line3.setco4_r1_14(line0.getco4_r1_14());
> line3.setco4_r3_3(line0.getco4_r3_3());
> report_VC_T.add(line3); }
> }
>
>
> if ((line0.getVt()==2)
> &&((line0.getDu_num()==3)||(line0.getDu_num()==4)||(line0.getDu_num()==5)||
>
> (line0.getDu_num()==14)||(line0.getDu_num()==16)||(line0.getDu_num()==18)||
>
> (line0.getDu_num()==21)||(line0.getDu_num()==22)||(line0.getDu_num()==23)||
>
> (line0.getDu_num()==24)||(line0.getDu_num()==25)||(line0.getDu_num()==28)||
> (line0.getDu_num()==29)
> ))
> {
>
>
> if((line0.getYch_speedM()+line0.getYch_speedD())!=0
> )
> {
> VC34_Excel_ line3 =new VC34_Excel_();
> line3.setYch_speedM(line0.getYch_speedM());
> line3.setYch_speedD(line0.getYch_speedD());
> line3.setDu_Sn(line0.getDu_Sn());
> line3.setDu_num(line0.getDu_num());
> line3.setYch_speedNorm(line0.getYch_speedNorm());
> line3.setco4_r1_13a(line0.getco4_r1_13a());
> line3.setco4_r1_13(line0.getco4_r1_13());
> line3.setco4_r1_14(line0.getco4_r1_14());
> line3.setco4_r3_3(line0.getco4_r3_3());
> report_VC_T.add(line3); }
> }
>
>
>
>
>
>
>
>
>
>
> }
> }//все
>
> if (report_VC_T.size()!=0){
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(line1.getVt_sn());
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)1));
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("");
> cellN3.setCellStyle(styleN1);
> sheet.addMergedRegion(new Region(k_min,(short)2,k_min+1,(short)5));
>
>
> k_min=k_min+2;
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("№ ДУ");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)0));
>
> cellN3 = rowN3.createCell((short)1);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Диспетчерский участок");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)1,k_min+1,(short)1));
>
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Участковая скорость, норма");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)2,k_min+1,(short)2));
>
> cellN3 = rowN3.createCell((short)3);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Участковая скорость, сутки");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)3,k_min+1,(short)3));
>
> cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("Участковая скорость, с/сутки");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)4,k_min+1,(short)4));
>
> cellN3 = rowN3.createCell((short)5);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("+/- к норме");
> cellN3.setCellStyle(style0);
> sheet.addMergedRegion(new Region(k_min,(short)5,k_min+1,(short)5));
>
>
>
>
> for (int w=0;w<=5;w++ )
> { for (int k=k_min+1; k<=k_min+1;k++) {
> rowN3 = sheet.createRow(k);
> cellN3 = rowN3.createCell((short)w);//начиная со столбца (+1=столбц)
> cellN3.setCellStyle(style0);}}
>
>
>
>
>
> k_min=k_min+2;
>
>
>
> for (int i3=0;i3<report_VC_T.size();i3++){
> VC34_Excel_ line3 =(VC34_Excel_)report_VC_T.get(i3);
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки
> (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(line3.getDu_num());
>
> cellN3.setCellStyle(style0_1);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min,(short)0));
>
>
> cellN3 = rowN3.createCell((short)1);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(line3.getDu_Sn());
> if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
> sheet.addMergedRegion(new Region(k_min,(short)1,k_min,(short)1));
>
>
>
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(form.format((line3.getYch_speedNorm())));
> cellN3.setCellStyle(style0_1);
> sheet.addMergedRegion(new Region(k_min,(short)2,k_min,(short)2));
>
>
>
>
>
>
>
>
>
>
> cellN3 = rowN3.createCell((short)3);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(form.format(line3.getYch_speedD()));
> if((line3.getYch_speedD()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
>
> sheet.addMergedRegion(new Region(k_min,(short)3,k_min,(short)3));
>
> cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue(form.format(line3.getYch_speedM()));
> if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
>
> sheet.addMergedRegion(new Region(k_min,(short)4,k_min,(short)4));
>
> cellN3 = rowN3.createCell((short)5);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
>
> cellN3.setCellValue(form.format((line3.getYch_speedM()-line3.getYch_speedNorm())));
>
> if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
> cellN3.setCellStyle(style0_0);
> }else{
> cellN3.setCellStyle(style0_1);}
>
> sheet.addMergedRegion(new Region(k_min,(short)5,k_min,(short)5));
> k_min=k_min+1;
>
>
>
> }//line3
>
>
> rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
> cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
> cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
> // cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
> cellN3.setCellValue("");
> cellN3.setCellStyle(style0_1_0);
> sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)5));
>
> k_min=k_min+2;
>
>
>
>
> report_VC_T.clear();
> }//size
> }//тяга
>
> OutputStream fileOut = new FileOutputStream(Pyt);
>
> // POIFSFileSystem fs112= new POIFSFileSystem(new
> FileOutputStream(Pyt));
>
> wb.write(fileOut);
> fileOut.close();
>
>
>
>
>
>
>
>
>
>
> }
>
> }
>
>
>
>
> Give me please advise that it is possible to make in the given situation.
> Sorry, I have a bad english.
>
>
--
View this message in context: http://www.nabble.com/Images-and-Exel-tp23937543p23981526.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Images and Exel
Posted by saper1281 <sa...@yandex.ru>.
Hello!
It is necessary in existing Exel the document to add sheet Лист96.
If the document contains in a case of figure all works successful (using
libraries: poi-2.5.1-final-20040804, poi-contrib-2.5.1-final-20040804,
poi-scratchpad-2.5.1-final-20040804).
If in the document there is a figure that the damaged document comes back.
I have recommendation to take advantage of newer libraries:
I had downloaded:
poi-bin-3.2-FINAL-20081019: <http: //
mirror.prvtgeo.com/apache/poi/release/bin/>
poi-bin-3.5-beta5-20090219: <http: //
mirror.prvtgeo.com/apache/poi/dev/bin/>
At their use there are errors:
When I used poi-bin-3.5-beta5-20090219, at compilation there is a error:
Exception in tread "main"
java.lang.NoSuchMethodError:org.apache.poi.hssf.usermodel.HSSFSheet.addMergedRegion
<Lorg/apache/poi/hssf/unit/Region;>I ......;
When I used poi-bin-3.2-FINAL-20081019, after completion of the program, the
document is damaged.
error:
Error of initialization VBA (265). Also suggests to restore the document.
This is a full code of the program:
package VC34;
import VC34.classes.*;
import java.io.*;
import java.util.Date;
import java.net.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import javax.naming.*;
import java.text.*;
import java.lang.reflect.*;
import org.apache.poi.hssf.util.Region;
//import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.*;
//import org.apache.poi.ss.util.Region;
import org.apache.poi.hssf.model.Sheet.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFSheet.*;
import org.apache.poi.poifs.filesystem.*;
import org.apache.regexp.*;
import org.apache.*;
//import org.apache.struts.*;
import javax.swing.*;
import java.awt.event.*;
import java.applet.*;
/**
*
* @author apopovkin
*/
public class VC34_Excel {
/** Creates a new instance of VC34_Excel */
public VC34_Excel() {
}
/**
* @param args the command line arguments
*/
public static void main(String[] args)throws Exception {
// NumberFormat form = new DecimalFormat("#########0.0#");
System.out.println(args[0]);
System.out.println(args[1]);
System.out.println(args[2]);
System.out.println(args[3]);
System.out.println(args[4]);
System.out.println(args[5]);
System.out.println(args.length);
String beg_dt = "";
String tper = args[4];
String dbName ="";
String UserID ="";
String Password ="";
String Pyt ="";
String Driver ="COM.ibm.db2.jdbc.app.DB2Driver";
PrintStream out=System.out;
SimpleDateFormat dateFormatter1 = new
SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat dateFormatter2 = new
SimpleDateFormat("dd-MM-yyyy");
SimpleDateFormat dateFormatter3 = new SimpleDateFormat("dd-MM-yyyy
HH:mm:ss");
Calendar time1=Calendar.getInstance();
out.print("Begin:
"+dateFormatter3.format(time1.getTime()).toString()+"\n");
try {
if (args.length>=1){dbName+="db2:"+args[0];}
if (args.length>=2){UserID=args[1];}
if (args.length>=3){Password=args[2];}
if (args.length>=4){beg_dt=args[3];} else beg_dt = "current date
- 1 day";
if (args.length>=5){Pyt=args[5];}
}
catch (Exception e){System.out.print("23"+e);System.exit(1);}
if (dbName.equals("")||UserID.equals("")||Password.equals("")){
System.out.print("Неверно заданны параметры. Формат параметров
gen_stat db_alias user pass otchname year month \n По умолчанию
устанавливается текущий год и предыдущий месяц");
System.exit(0);
}
String dday = beg_dt.substring(0,2);
String mmonth = beg_dt.substring(3,5);
String yyear = beg_dt.substring(6,10);
String End_dt=yyear+"-"+mmonth+"-"+dday;//конец
String DT_OTCH_SYT_F2=dday+"-"+mmonth+"-"+yyear;
String Beg_dt=yyear+"-"+mmonth+"-"+"01";//начало
String DT_NACH_MES_F2="01"+"-"+mmonth+"-"+yyear;
String
GEnd_dt=String.valueOf(Integer.parseInt(yyear)-1)+"-"+mmonth+"-"+dday;//конец
Г
String
GBeg_dt=String.valueOf(Integer.parseInt(yyear)-1)+"-"+mmonth+"-"+"01";//начало
Г
Context initContext = null;
Vector report_VC_0 = new Vector();
Vector report_VC_1 = new Vector();
Vector report_VC_T = new Vector();
Connection connection_mm = null;
ResultSet resultSet_mm = null;
ResultSet resultSet_nod = null;
try {
Class.forName(Driver);
connection_mm = DriverManager.getConnection("jdbc:"+dbName ,
UserID, Password);
Calendar time=Calendar.getInstance();
out.print("DB conect open OK_kol_mm
time="+dateFormatter3.format(time.getTime()).toString()+"\n");
}
catch(Exception e){
out.print("DB error"+e+"\n");
System.exit(1);}
try{
PreparedStatement st1=null;
st1 = connection_mm.prepareStatement(
//SQL---------------
//SQL---------------
);
resultSet_nod=st1.executeQuery();
while (resultSet_nod.next()) {
VC34_Excel_ line0 =new VC34_Excel_();
line0.setDu_Sn(resultSet_nod.getString("co4_r3_1"));
line0.setDu_num(resultSet_nod.getInt("co4_r3_0"));
line0.setco4_r1_13a(resultSet_nod.getFloat("co4_r1_13a"));
line0.setco4_r1_13(resultSet_nod.getDouble("co4_r1_13"));
line0.setco4_r1_14(resultSet_nod.getDouble("co4_r1_14"));
line0.setco4_r3_3(resultSet_nod.getDouble("co4_r3_3"));
line0.setYch_speedM(resultSet_nod.getDouble("co4_r3_22"));
line0.setYch_speedD(resultSet_nod.getDouble("co4_r3_22d"));
line0.setYch_speedNorm(resultSet_nod.getDouble("sp_u"));
line0.setVt(resultSet_nod.getInt("VT"));
report_VC_0.add(line0);
}
resultSet_nod.close();
st1.close();
}catch (SQLException e) {
e.printStackTrace(); }
//закрытие соед.
try {
connection_mm.close();
Calendar time=Calendar.getInstance();
out.print("DB conect close OK_kol_mm
time="+dateFormatter3.format(time.getTime()).toString()+"\n");
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use
File | Settings | File Templates.
}
try {
Class.forName(Driver);
connection_mm = DriverManager.getConnection("jdbc:"+dbName ,
UserID, Password);
Calendar time=Calendar.getInstance();
out.print("DB conect open OK_kol_mm
time="+dateFormatter3.format(time.getTime()).toString()+"\n");
}
catch(Exception e){
out.print("DB error"+e+"\n");
System.exit(1);}
try{
PreparedStatement st1=null;
st1 = connection_mm.prepareStatement(
"Select CLASS.VT.vt_sn,CLASS.VT.VT from CLASS.VT ORDER BY VT "
);
resultSet_nod=st1.executeQuery();
while (resultSet_nod.next()) {
VC34_Excel_ line1 =new VC34_Excel_();
line1.setVt_sn(resultSet_nod.getString("vt_sn"));
line1.setVt(resultSet_nod.getInt("vt"));
report_VC_1.add(line1);
}
resultSet_nod.close();
st1.close();
}catch (SQLException e) {
e.printStackTrace(); }
//закрытие соед.
try {
connection_mm.close();
Calendar time=Calendar.getInstance();
out.print("DB conect close OK_kol_mm
time="+dateFormatter3.format(time.getTime()).toString()+"\n");
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use
File | Settings | File Templates.
}
//NumberFormat form = new DecimalFormat("#########0.##");
NumberFormat form = new DecimalFormat("#########0.0#");
HSSFWorkbook wb ;
InputStream fileInput = null;
try {
//FileDescriptor t=new FileDescriptor
fileInput = new FileInputStream(Pyt);
POIFSFileSystem fs111= new POIFSFileSystem(new
FileInputStream(Pyt));
wb = new HSSFWorkbook(fs111);
// wb = new HSSFWorkbook(fileInput);
out.println("1");
// fileInput.close();
}
catch(Exception e){
wb = new HSSFWorkbook();
}
HSSFSheet sheet =null;
out.println("index69_1="+wb.getSheetIndex("Лист69"));
if(wb.getSheetIndex("Лист69")<0){
sheet =wb.createSheet("Лист69");
wb.setSheetName( wb.getSheetIndex("Лист69"), "Лист69",
HSSFWorkbook.ENCODING_UTF_16 );
out.println("index69_2="+wb.getSheetName(wb.getSheetIndex("Лист69")));
}else
{ out.println("index69_3="+wb.getSheetIndex("Лист69"));
sheet=wb.getSheet("Лист69");
out.println("index69_4="+wb.getSheetName(wb.getSheetIndex("Лист69")));
}
sheet.setColumnWidth((short)0,(short)2200);
sheet.setColumnWidth((short)1,(short)5000);
sheet.setColumnWidth((short)2,(short)4200);
sheet.setColumnWidth((short)3,(short)4200);
sheet.setColumnWidth((short)4,(short)4200);
sheet.setColumnWidth((short)5,(short)4200);
//---------делаем альбомную страницу----------------------
HSSFPrintSetup print = sheet.getPrintSetup();
print.setScale((short)83);
print.setPaperSize((short) print.A4_PAPERSIZE);
String f1="ОТЧЕТ";
String f2="Анализ участковой скорости по диспетчерским участкам";
String f3="";
if(tper.equals("1")){
f3="За "+DT_OTCH_SYT_F2+" г.";}
if(tper.equals("2")){
f3="За период с "+DT_NACH_MES_F2+" по "+DT_OTCH_SYT_F2+" г.";}
String f4="Дорога ЮВОСТ";
String f5="";
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)10);//шрифт
font.setFontName("Arial");
font.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
HSSFFont font1 = wb.createFont();
font1.setFontHeightInPoints((short)13);//шрифт
font1.setFontName("Arial");
font1.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle styleN = wb.createCellStyle();
styleN.setFillBackgroundColor((short)12);
styleN.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleN.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleN.setWrapText(true);
styleN.setFont(font);
HSSFCellStyle styleN1 = wb.createCellStyle();
styleN1.setFillBackgroundColor((short)12);
styleN1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleN1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleN1.setFillBackgroundColor((short)155);
styleN1.setWrapText(true);
styleN1.setFont(font1);
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor((short)12);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setFont(font);
//style.setWrapText(true);
HSSFCellStyle style0 = wb.createCellStyle();
style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style0.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style0.setBorderRight(HSSFCellStyle.BORDER_THIN);
style0.setBorderTop(HSSFCellStyle.BORDER_THIN);
style0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style0.setFont(font);
style0.setWrapText(true);
HSSFCellStyle style0_0 = wb.createCellStyle();
style0_0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style0_0.setFillForegroundColor(HSSFColor.YELLOW.index);
style0_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style0_0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style0_0.setBorderRight(HSSFCellStyle.BORDER_THIN);
style0_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
style0_0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0_0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style0_0.setFont(font);
style0_0.setWrapText(true);
HSSFCellStyle style0_2 = wb.createCellStyle();
style0_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style0_2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style0_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style0_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style0_2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style0_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style0_2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style0_2.setFont(font);
style0_2.setWrapText(true);
style0_2.setRotation((short)90);//вертикальный текст
HSSFCellStyle style0_1 = wb.createCellStyle();
style0_1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style0_1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style0_1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style0_1.setBorderTop(HSSFCellStyle.BORDER_THIN);
style0_1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0_1.setFont(font);
style0_1.setWrapText(true);
HSSFCellStyle style0_1_0 = wb.createCellStyle();
style0_1_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style0_1_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
style0_1_0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0_1_0.setFont(font);
style0_1_0.setWrapText(true);
HSSFRow rowN1 = sheet.createRow(0);
HSSFCell cellN1 = rowN1.createCell((short)0);
cellN1.setCellType( HSSFCell.CELL_TYPE_STRING );
cellN1.setCellValue(f1);
cellN1.setCellStyle(styleN1);
Region mmmm= new Region(0,(short)0,1,(short)5);
sheet.addMergedRegion(mmmm);
HSSFRow rowN2 = sheet.createRow(2);//начиная с какой строчки
(+1=строка)
HSSFCell cellN2 = rowN2.createCell((short)0);//начиная со столбца
(+1=столбц)
cellN2.setCellType( HSSFCell.CELL_TYPE_STRING );
cellN2.setCellValue(f2);
cellN2.setCellStyle(styleN1);
sheet.addMergedRegion(new Region(2,(short)0,3,(short)5));
HSSFRow rowN3 = sheet.createRow(4);//начиная с какой строчки (+1=строка)
HSSFCell cellN3 = rowN3.createCell((short)0);//начиная со столбца
(+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
cellN3.setCellValue(f5);
cellN3.setCellStyle(styleN1);
sheet.addMergedRegion(new Region(4,(short)0,5,(short)1));
cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
cellN3.setCellValue(f4);
cellN3.setCellStyle(styleN1);
sheet.addMergedRegion(new Region(4,(short)2,5,(short)3));
cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
cellN3.setCellValue(f3);
cellN3.setCellStyle(styleN1);
sheet.addMergedRegion(new Region(4,(short)4,5,(short)5));
int k_min=6;
int k_max=7;
for (int i1=0;i1<report_VC_1.size();i1++){
VC34_Excel_ line1 =(VC34_Excel_)report_VC_1.get(i1);
String vt_snstr=line1.getVt_sn();
int vtstr=line1.getVt();
//все
for (int i0=0;i0<report_VC_0.size();i0++){
VC34_Excel_ line0 =(VC34_Excel_)report_VC_0.get(i0);
if (line0.getVt()==vtstr )
{
if ((line0.getVt()==1)
&&((line0.getDu_num()==1)||(line0.getDu_num()==2)||(line0.getDu_num()==6)||
(line0.getDu_num()==7)||(line0.getDu_num()==8)||(line0.getDu_num()==9)||
(line0.getDu_num()==10)||(line0.getDu_num()==11)||(line0.getDu_num()==12)||
(line0.getDu_num()==13)||(line0.getDu_num()==15)||(line0.getDu_num()==17)||
(line0.getDu_num()==19)||(line0.getDu_num()==20)||(line0.getDu_num()==26)||
(line0.getDu_num()==27)
))
{
if((line0.getYch_speedM()+line0.getYch_speedD())!=0
)
{
VC34_Excel_ line3 =new VC34_Excel_();
line3.setYch_speedM(line0.getYch_speedM());
line3.setYch_speedD(line0.getYch_speedD());
line3.setDu_Sn(line0.getDu_Sn());
line3.setDu_num(line0.getDu_num());
line3.setYch_speedNorm(line0.getYch_speedNorm());
line3.setco4_r1_13a(line0.getco4_r1_13a());
line3.setco4_r1_13(line0.getco4_r1_13());
line3.setco4_r1_14(line0.getco4_r1_14());
line3.setco4_r3_3(line0.getco4_r3_3());
report_VC_T.add(line3); }
}
if ((line0.getVt()==2)
&&((line0.getDu_num()==3)||(line0.getDu_num()==4)||(line0.getDu_num()==5)||
(line0.getDu_num()==14)||(line0.getDu_num()==16)||(line0.getDu_num()==18)||
(line0.getDu_num()==21)||(line0.getDu_num()==22)||(line0.getDu_num()==23)||
(line0.getDu_num()==24)||(line0.getDu_num()==25)||(line0.getDu_num()==28)||
(line0.getDu_num()==29)
))
{
if((line0.getYch_speedM()+line0.getYch_speedD())!=0
)
{
VC34_Excel_ line3 =new VC34_Excel_();
line3.setYch_speedM(line0.getYch_speedM());
line3.setYch_speedD(line0.getYch_speedD());
line3.setDu_Sn(line0.getDu_Sn());
line3.setDu_num(line0.getDu_num());
line3.setYch_speedNorm(line0.getYch_speedNorm());
line3.setco4_r1_13a(line0.getco4_r1_13a());
line3.setco4_r1_13(line0.getco4_r1_13());
line3.setco4_r1_14(line0.getco4_r1_14());
line3.setco4_r3_3(line0.getco4_r3_3());
report_VC_T.add(line3); }
}
}
}//все
if (report_VC_T.size()!=0){
rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(line1.getVt_sn());
cellN3.setCellStyle(styleN1);
sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)1));
rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("");
cellN3.setCellStyle(styleN1);
sheet.addMergedRegion(new Region(k_min,(short)2,k_min+1,(short)5));
k_min=k_min+2;
rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("№ ДУ");
cellN3.setCellStyle(style0);
sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)0));
cellN3 = rowN3.createCell((short)1);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("Диспетчерский участок");
cellN3.setCellStyle(style0);
sheet.addMergedRegion(new Region(k_min,(short)1,k_min+1,(short)1));
cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("Участковая скорость, норма");
cellN3.setCellStyle(style0);
sheet.addMergedRegion(new Region(k_min,(short)2,k_min+1,(short)2));
cellN3 = rowN3.createCell((short)3);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("Участковая скорость, сутки");
cellN3.setCellStyle(style0);
sheet.addMergedRegion(new Region(k_min,(short)3,k_min+1,(short)3));
cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("Участковая скорость, с/сутки");
cellN3.setCellStyle(style0);
sheet.addMergedRegion(new Region(k_min,(short)4,k_min+1,(short)4));
cellN3 = rowN3.createCell((short)5);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("+/- к норме");
cellN3.setCellStyle(style0);
sheet.addMergedRegion(new Region(k_min,(short)5,k_min+1,(short)5));
for (int w=0;w<=5;w++ )
{ for (int k=k_min+1; k<=k_min+1;k++) {
rowN3 = sheet.createRow(k);
cellN3 = rowN3.createCell((short)w);//начиная со столбца (+1=столбц)
cellN3.setCellStyle(style0);}}
k_min=k_min+2;
for (int i3=0;i3<report_VC_T.size();i3++){
VC34_Excel_ line3 =(VC34_Excel_)report_VC_T.get(i3);
rowN3 = sheet.createRow(k_min);//начиная с какой строчки
(+1=строка)
cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(line3.getDu_num());
cellN3.setCellStyle(style0_1);
sheet.addMergedRegion(new Region(k_min,(short)0,k_min,(short)0));
cellN3 = rowN3.createCell((short)1);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(line3.getDu_Sn());
if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
cellN3.setCellStyle(style0_0);
}else{
cellN3.setCellStyle(style0_1);}
sheet.addMergedRegion(new Region(k_min,(short)1,k_min,(short)1));
rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
cellN3 = rowN3.createCell((short)2);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(form.format((line3.getYch_speedNorm())));
cellN3.setCellStyle(style0_1);
sheet.addMergedRegion(new Region(k_min,(short)2,k_min,(short)2));
cellN3 = rowN3.createCell((short)3);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(form.format(line3.getYch_speedD()));
if((line3.getYch_speedD()-line3.getYch_speedNorm())<0){
cellN3.setCellStyle(style0_0);
}else{
cellN3.setCellStyle(style0_1);}
sheet.addMergedRegion(new Region(k_min,(short)3,k_min,(short)3));
cellN3 = rowN3.createCell((short)4);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(form.format(line3.getYch_speedM()));
if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
cellN3.setCellStyle(style0_0);
}else{
cellN3.setCellStyle(style0_1);}
sheet.addMergedRegion(new Region(k_min,(short)4,k_min,(short)4));
cellN3 = rowN3.createCell((short)5);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue(form.format((line3.getYch_speedM()-line3.getYch_speedNorm())));
if((line3.getYch_speedM()-line3.getYch_speedNorm())<0){
cellN3.setCellStyle(style0_0);
}else{
cellN3.setCellStyle(style0_1);}
sheet.addMergedRegion(new Region(k_min,(short)5,k_min,(short)5));
k_min=k_min+1;
}//line3
rowN3 = sheet.createRow(k_min);//начиная с какой строчки (+1=строка)
cellN3 = rowN3.createCell((short)0);//начиная со столбца (+1=столбц)
cellN3.setCellType( HSSFCell.CELL_TYPE_STRING );
// cellN3.setEncoding( HSSFCell.ENCODING_UTF_16 );
cellN3.setCellValue("");
cellN3.setCellStyle(style0_1_0);
sheet.addMergedRegion(new Region(k_min,(short)0,k_min+1,(short)5));
k_min=k_min+2;
report_VC_T.clear();
}//size
}//тяга
OutputStream fileOut = new FileOutputStream(Pyt);
// POIFSFileSystem fs112= new POIFSFileSystem(new FileOutputStream(Pyt));
wb.write(fileOut);
fileOut.close();
}
}
Give me please advise that it is possible to make in the given situation.
Sorry, I have a bad english.
--
View this message in context: http://www.nabble.com/Images-and-Exel-tp23937543p23980019.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Images and Exel
Posted by MSB <ma...@tiscali.co.uk>.
I waited a while to see if anyone else replied to your post because I am
sorry to say that I cannot understand your question.
What do you mean by a subjoin please?
You talk of a problem with images but there does not seem to be any code
that works with images in your post. Further, the exception relates to the
addMergedRegion method and yet the code you have posted does not include a
call to this method. Can I ask you please to explain a little more clearly
exactly what you are trying to accomplish? Can you also post all of the code
you are trying to use - especially any that section that causes the
exception to be thrown. Finally, as fas as I am aware, the setSheetName()
method takes only two arguments, not three.
Yours
Mark B
saper1281 wrote:
>
> Help me, please.
> In the Exel document need to subjoin sheet 69.
> If in Exel document include image then Exel document return defective.
>
> When use poi-bin-3.5-beta5-20090219 return error:
> Exception in tread "main"
> java.lang.NoSuchMethodError:org.apache.poi.hssf.usermodel.HSSFSheet.addMergedRegion
> <Lorg/apache/poi/hssf/unit/Region;>I ......;
>
> When use poi-bin-3.2-FINAL-20081019 return defective document, error:
> Error initialization VBA(265), and appeal restore Exel document
>
>
> Code:
>
> HSSFWorkbook wb ;
> InputStream fileInput = null;
> try {
>
> fileInput = new FileInputStream(Pyt);
> POIFSFileSystem fs111= new POIFSFileSystem(new FileInputStream(Pyt));
> wb = new HSSFWorkbook(fs111);
> fileInput.close();
> }
> catch(Exception e){
> wb = new HSSFWorkbook();
> }
>
> HSSFSheet sheet =null;
>
> if(wb.getSheetIndex("sheet69")<0){
> sheet =wb.createSheet("sheet69");
> wb.setSheetName( wb.getSheetIndex("sheet69"), "sheet69",
> HSSFWorkbook.ENCODING_UTF_16 );
>
> }else
> {
> sheet=wb.getSheet("Лист69");
> }
>
> //----------
>
> //-----------
>
> OutputStream fileOut = new FileOutputStream(Pyt);
>
> wb.write(fileOut);
> fileOut.close();
>
>
--
View this message in context: http://www.nabble.com/Images-and-Exel-tp23937543p23957498.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org