столкнулся с такой проблемой, когда у меня записей больше 300, формат данных начинает менятся на общий или какой угодно экселю и теряется форматирование в ячейке. пробовал заранее задавать формат через
Curr.Range['N12', 'N' + IntToStr(iRec + 1)].HorizontalAlignment := xlCenter; Curr.Range['N12', 'N' + IntToStr(iRec + 1)].VerticalAlignment := xlCenter; } //XLApp.activeworkbook.activesheet.cells.item[iRec + 1, 5].NumberFormat := '##0';
результата не принесло. Кто сталкивался, подскажите как быть?
procedure TForm5.BitBtn3Click(Sender: TObject); var workBooks, activeBook, curr, Range: Variant; CellStart, CellFinish: Variant; sPlace: string; Ls: TStringList; i, iRec: Integer; Data: Variant; st, st2, st3: string; col: integer; year, MonthSelected: string; oth: string; direct: string; IDSOURCE, PR_MEST: string; priznak: string; NO_FAM: string; ID_TRAN: string; ToCell, ToCell2, ToCell3, ToCell4, ToCell5, ToCell6, ToCell7, ToCell8, ToCell9, ToCell10, ToCell11, ToCell12, ToCell13, ToCell14, ToCell20, ToCell21, ToCell22: string; value, value2: string; mesto: string; nametext: string; TemplateSheet: string; perevoz: string; st_perevoz: string; IDPEREVOZ: string; position: string; irec_1: integer; V_ar: OleVariant; WorkBk: _WorkBook; // определяем WorkBook WorkSheet: _WorkSheet; // определяем WorkSheet //Range:OleVariant;// begin begin if Form5.Edit1.Text = '' then begin ShowMessage('Введите Год'); end else if Form5.ComboBox1.ItemIndex = (-1) then begin ShowMessage('Выберите месяц'); end else if Form5.ComboBox2.ItemIndex = (-1) then begin ShowMessage('Выберите Наименование'); end else // if Form5.ComboBox3.ItemIndex = (-1) then // begin // ShowMessage('Выберите признак принадлежности'); // end // else if Form5.ComboBox4.ItemIndex = (-1) then begin ShowMessage('Выберите признак места'); end else begin ToCell := 'C1'; // Var ToCell : String; ToCell2 := 'A6'; ToCell3 := 'B8'; value := Form5.ComboBox1.Text + ' ' + Form5.Edit1.Text + ' г.'; value2 := DateTimeToStr(Now); ID_TRAN := Form5.ComboBox2.Text; mesto := IntToStr(Form5.ComboBox4.ItemIndex); year := Form5.Edit1.Text; MonthSelected := FloatToStr(Form5.ComboBox1.ItemIndex + 1); priznak := IntToStr(Form5.ComboBox3.ItemIndex + 1); begin if Form5.ComboBox3.ItemIndex <> -1 then begin priznak := ' and lor.pr_attrib=' + IntToStr(Form5.ComboBox3.ItemIndex + 1); end else begin priznak := ' '; end; end; begin if Form5.ComboBox5.ItemIndex <> -1 then begin IDPEREVOZ := 'select ID_PRIGCOMP from SPR_PRIGCOMP where Full_name=' + #39 + Form5.ComboBox5.Text + #39 + ''; Form5.ClientDataSet1.close; Form5.ClientDataSet1.DataRequest(IDPEREVOZ); Form5.ClientDataSet1.open; IDPEREVOZ := Form5.ClientDataSet1.fieldbyname('ID_PRIGCOMP').asstring; perevoz := ' and l.ID_PEREVOZ=' + #39 + IDPEREVOZ + #39 + '' end else begin perevoz := ' '; end; end; oth := 'select ID_OTCHM from sprotchmonth where YEAROTCH = ' + year + ' and MONTHOTCH = ' + MonthSelected + ''; Form5.ClientDataSet1.close; Form5.ClientDataSet1.DataRequest(oth); Form5.ClientDataSet1.open; oth := Form5.ClientDataSet1.fieldbyname('id_otchm').asstring; st := 'select ID_TRANS from SPR_TRANS t where t.NAME_TRANS=' + #39 + ID_TRAN + #39 + ''; Form5.ClientDataSet2.close; Form5.ClientDataSet2.DataRequest(st); Form5.ClientDataSet2.open; ID_TRAN := Form5.ClientDataSet2.fieldbyname('ID_TRANS').asstring; IDSOURCE := 'select * from SPR_TRANS t, SPRSOURCEDATA s where ' + ' t.Id_Trans =' + #39 + ID_TRAN + #39 + ' and t.Id_Trans=s.Id_Trans and s.Pr_Mest=' + #39 + mesto + #39 + ''; Form5.ClientDataSet1.close; Form5.ClientDataSet1.DataRequest(IDSOURCE); Form5.ClientDataSet1.open; IDSOURCE := Form5.ClientDataSet1.fieldbyname('ID_SOURCE').asstring; PR_MEST := Form5.ClientDataSet1.FieldByName('Pr_MEST').AsString; NO_FAM := Form5.ClientDataSet1.FieldByName('NO_ONE_FAM').AsString; nametext := Form5.ClientDataSet1.FieldByName('NAMETEXT').AsString; begin if nametext = 'M6301' then begin TemplateSheet := 'H:\m6301.xls'; end; if nametext = 'M6302' then begin TemplateSheet := 'H:\ m6302.xls'; end; end; { IDSOURCE := 'select * from Sprsourcedata where SHORT_NAME = ' + #39 + Form5.ComboBox2.Text + #39 + ''; Form5.ClientDataSet1.close; Form5.ClientDataSet1.DataRequest(IDSOURCE); Form5.ClientDataSet1.open; IDSOURCE := Form5.ClientDataSet1.fieldbyname('ID_SOURCE').asstring; PR_MEST := Form5.ClientDataSet1.FieldByName('Pr_MEST').AsString; NO_FAM := Form5.ClientDataSet1.FieldByName('NO_ONE_FAM').AsString; } begin if mesto = '0' then begin st := ' select ' + ' s.SHORT_NAME, l.Kodorg, kod.forma_tt, l.Kodpodr, '; if NO_FAM = '0' then st := st + ' l.FIO ' else st := st + ' (rpad(l.FIO,38) ||rpad(l. FIODEPEN,38)) FIO '; st := st + ' ,l.n_Doclgotn, ' + ' l.Date_Opd, l.Date_Begin_Ab, l.n_Opd, decode(nvl(l.sposob_opd,0),0,''РУЧНОЙ.'',''ЭКСПРЕСС'')Sposob_Opd, ' + ' l.vid_period_ab, l.kol_doc, ' + ' (select msto.namestat from SPR_STATRUS msto where msto.Id_Mstorus=l.Id_Msto_Otpr) STAT_OTPR, ' + ' (select msto.namestat from SPR_STATRUS msto where msto.Id_Mstorus=l.Id_Msto_Nazn) STAT_NAZN, summa, SUMMA_DOP, sysdate ' + ' from LGOTSUMMA l, SPR_PRIGCOMP s, SPR_KODLGOT kod, SPRLORG lor ' + ' where l.Id_Otchm= ' + oth + ' and l.Id_Source=' + IDSOURCE + ' ' + priznak + perevoz + ' and l.id_perevoz = s.id_prigcomp ' + ' and l.kodorg = lor.kodorg ' + ' and kod.kodlgot=l.kodlgot ' + ' order by l.Kodorg, s.SHORT_NAME asc '; Form5.ClientDataSet1.close; Form5.ClientDataSet1.DataRequest(st); Form5.ClientDataSet1.open; st2 := 'select ' + ' l.KODORG, spr.short_name, (''Ф '' || kod.forma_tt) KOD, sum(l.kol_doc) doc , (sum(l.summa)) sum ' + ' from lgotsumma l, SPR_PRIGCOMP spr, SPR_KODLGOT kod, SPRLORG lor ' + ' where l.Id_Otchm= ' + oth + ' and l.Id_Source=' + IDSOURCE + ' ' + priznak + perevoz + ' and spr.id_prigcomp = l.id_perevoz ' + ' and l.kodorg = lor.kodorg ' + ' and kod.kodlgot = l.kodlgot ' + ' group by l.KODORG, spr.short_name, kod.forma_tt ' + ' order by l.Kodorg, spr.short_name, kod.forma_tt asc'; Form5.ClientDataSet2.close; Form5.ClientDataSet2.DataRequest(st2); Form5.ClientDataSet2.open; st3 := 'select l.KODORG, SUM(l.Kol_Doc), sum(l.summa) ' + 'from lgotsumma l, SPR_PRIGCOMP spr, SPR_KODLGOT kod, SPRLORG lor ' + ' where ' + ' l.Id_Otchm= ' + oth + ' ' + ' and spr.id_prigcomp=l.id_perevoz ' + 'and l.kodorg = lor.kodorg ' + ' and kod.kodlgot=l.kodlgot ' + ' and l.Id_Source=' + IDSOURCE + ' ' + priznak + perevoz + ' group by l.KODORG, l.Kol_Doc ' + ' order by l.Kodorg, l.Kol_Doc asc '; Form5.ClientDataSet3.close; Form5.ClientDataSet3.DataRequest(st3); Form5.ClientDataSet3.open; if Form5.ClientDataSet1.RecordCount = 0 then begin ShowMessage('Записи отсуствуют'); end else begin XLApp := CreateOleObject('Excel.Application'); // XLApp.Visible := True; XLApp.SheetsInNewWorkbook := 1; // Добавим 1 лист - 3 (по умолчанию) workBooks := XLApp.WorkBooks; ActiveBook := workBooks.Add; Curr := ActiveBook.Sheets[1]; // Запомним добавленный лист try Ls := TStringList.Create; // заполнение ClientDataSet1.First; while not ClientDataSet1.Eof do begin sPlace := ClientDataSet1.FieldByName('Kodorg').AsString; if Ls.IndexOf(sPlace) = -1 then begin Ls.Add(sPlace); end; ClientDataSet1.Next; end; for i := 0 to Pred(Ls.Count) do begin ClientDataSet1.Filtered := False; ClientDataSet1.Filter := 'Kodorg = ' + QuotedStr(Ls.Strings[i]); ClientDataSet1.Filtered := True; irec_1 := irec_1 + 1; Data := VarArrayCreate([1, ClientDataSet1.RecordCount, 1, 14], varVariant); ClientDataSet1.First; iRec := 0; while not ClientDataSet1.Eof do begin Data[iRec + 1, 1] := ClientDataSet1.FieldByName('SHORT_NAME').AsString; //Curr.Range[iRec + 1, 1].NumberFormat:='##0,00'; Data[iRec + 1, 2] := ClientDataSet1.FieldByName('forma_tt').AsString; Data[iRec + 1, 3] := ClientDataSet1.FieldByName('KODPODR').AsString; Data[iRec + 1, 4] := ClientDataSet1.FieldByName('FIO').AsString; Data[iRec + 1, 5] := ClientDataSet1.FieldByName('n_Doclgotn').AsString; // <---------------- вот этот столбец в числовом формате Data[iRec + 1, 6] := ClientDataSet1.FieldByName('Date_Opd').AsString; Data[iRec + 1, 7] := ClientDataSet1.FieldByName('Date_Begin_Ab').AsString; Data[iRec + 1, 8] := ClientDataSet1.FieldByName('n_Opd').AsString; Data[iRec + 1, 9] := ClientDataSet1.FieldByName('Sposob_Opd').AsString; Data[iRec + 1, 10] := ClientDataSet1.FieldByName('vid_period_ab').AsString; // Range := Sheets.Range['K' + IntToStr(iRec + 1)]; {Range.Borders[4].LineStyle := 1; //Range.Borders[4] - можно ставить от 1 до 8 - точно не мпомню //рисуем border вокруг ячейки (обрамление) // Range := Sheets.Cells[2, 2]; //можно переменные Range:=Sheets.Cells[iRow,iCol]; // Range.HorizontalAlignment := xlCenter; // Range.VerticalAlignment := xlCenter; //Range['K' + IntToStr(iRec + 1)].NumberFormat := '##0'; // Range.NumberFormat := '##0'; } v_Ar := Sheets.Cell['E' + IntToStr(iRec + 1)]; v_ar.HorizontalAlignment := xlRight; v_ar.VerticalAlignment := xlCenter; v_ar.NumberFormat := '##0'; Data[iRec + 1, 11] := ClientDataSet1.FieldByName('kol_doc').AsString; Data[iRec + 1, 12] := ClientDataSet1.FieldByName('STAT_OTPR').AsString; Data[iRec + 1, 13] := ClientDataSet1.FieldByName('STAT_NAZN').AsString; Data[iRec + 1, 14] := ClientDataSet1.FieldByName('SUMMA').AsString; Inc(iRec); ClientDataSet1.Next; end; // добавляем новый личт из шаблона ПОСЛЕ того, что был текущим Curr := ActiveBook.Sheets.Add(type := TemplateSheet, After := Curr); Curr.Name := Ls.Strings[i]; //разметка страницы Curr.PageSetup.PrintArea := 'A1:N' + inttostr(irec + 18); Curr.PageSetup.Zoom := 60; Curr.PageSetup.Orientation := 2; //разметка страницы CellStart := Curr.Cells[12, 1]; // CellFinish := Curr.Cells[iRec + 8, 14]; // корректировка позиции вставки CellFinish := Curr.Cells[12 + iRec - 1, 14]; Range := Curr.Range[CellStart, CellFinish]; Curr.Range[ToCell] := value2; Curr.Range['D12:D' + inttostr(irec + 12)].WrapText := True; // position := IntToStr(irec + 11); ToCell4 := 'A' + inttostr(irec + 18); begin Form5.ClientDataSet3.First; while not ClientDataSet3.Eof do begin if Ls.Strings[i] = Form5.ClientDataSet3.FieldByName('KODORG').AsString then begin ToCell20 := 'C' + inttostr(irec + 16); Curr.Range[ToCell20].NumberFormat := '##0'; Curr.Range[ToCell20] := Form5.ClientDataSet3.FieldByName('SUM(l.Kol_Doc)').AsString; ToCell21 := 'D' + inttostr(irec + 16); Curr.Range[ToCell21].NumberFormat := '##0,00'; Curr.Range[ToCell21] := Form5.ClientDataSet3.FieldByName('sum(l.summa)').AsString; end; Form5.ClientDataSet3.Next; end; end; { ToCell20 := 'N' + inttostr(irec + 11); Curr.Range[ToCell20].Formula := '=Sum(N12:N' + position + ')'; ToCell21 := 'K' + inttostr(irec + 11); Curr.Range[ToCell21].Formula := '=Sum(K12:K' + position + ')'; } ToCell22 := 'A' + inttostr(irec + 16); Curr.Range[ToCell22] := 'ИТОГО'; ToCell10 := 'A' + inttostr(irec + 13); Curr.Range[ToCell10] := 'Перевозчик'; ToCell11 := 'B' + inttostr(irec + 13); Curr.Range[ToCell11] := 'Форма'; ToCell12 := 'C' + inttostr(irec + 13); Curr.Range[ToCell12] := 'Кол-во.док.'; ToCell13 := 'D' + inttostr(irec + 13); Curr.Range[ToCell13] := 'Сумма'; begin irec_1 := irec + 14; Form5.ClientDataSet2.First; while not ClientDataSet2.Eof do begin // for Ls.Strings[i] := 1 to Ls.Strings[i] - 1 do begin if Ls.Strings[i] = Form5.ClientDataSet2.FieldByName('KODORG').AsString then begin ToCell7 := 'A' + inttostr(irec_1); Curr.Range[ToCell7] := Form5.ClientDataSet2.FieldByName('SHORT_NAME').AsString; ToCell8 := 'B' + inttostr(irec_1); Curr.Range[ToCell8].NumberFormat := '##0'; Curr.Range[ToCell8] := Form5.ClientDataSet2.FieldByName('KOD').AsString; ToCell14 := 'C' + inttostr(irec_1); Curr.Range[ToCell14].NumberFormat := '##0'; Curr.Range[ToCell14] := Form5.ClientDataSet2.FieldByName('DOC').AsString; ToCell9 := 'D' + inttostr(irec_1); Curr.Range[ToCell9].NumberFormat := '##0,00'; Curr.Range[ToCell9] := Form5.ClientDataSet2.FieldByName('SUM').AsString; irec_1 := irec_1 + 1; end; Form5.ClientDataSet2.Next; end; end; end; Curr.Range[ToCell3] := (Ls.Strings[i]); Range.Value := Data; VarClear(Data); end; finally Ls.Free; end; ClientDataSet1.Filtered := False; Form5.ClientDataSet1.close; ActiveBook.Sheets[1].Delete; // удаление первого листа XLApp.Visible := True; // XLApp.Workbooks[1].SaveAs('C:\' + nametext + '.xls'); // <--- Сохраняем куда надо // XLApp.Quit; // <--- И убираем из процессов XLApp := Unassigned; end.
Сообщение отредактировано: Lapp - 2.07.2011 4:39
Отрадно спать, отрадней камнем быть, О, этот век, преступный и постыдный, Не жить, не чувствовать - удел завидный. Прошу, молчи, не смей меня будить!