| Новости | Поиск | FAQ | Статьи | Книги | Новости VCL | | Форумы ЧАТ | Орешник | KOL и MCK |
Форумы -» Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

	« предыдущая ветвь | форум | следующая ветвь »

Экспорт в Excel2010: значения NumberFormat ячейки

AlekVolsk ©   (2011-05-01 12:16:00) 

Доброго времени суток.
Значения чисел, дробных чисел, даты и времеи, а также чисел длиною более 19 знаков, переданные в качестве строки (string), передаются криво: ячейка содержит значение 64. Какой NumberFormat необходимо выставить ячейкам, чтобы данные передавались корректно? В более ранних версиях Excel достаточно было указать '@', в 2010 это не работает. Спасибо.

---

AlekVolsk   (2011-05-12 10:36:00) [1]

Кому интересно, аналогичная ф-ция для ОО. Не знаю, как сделать border'ы ячеек, пытался сделать по образцу, взятому там: http://www.sql.ru/forum/actualthread.aspx?tid=405083, не получилось.

unit kd_odc;

interface

uses cxGridDBTableView;

procedure GridToOdc(Grid: TcxGridDBTableView);

implementation

uses
 COMObj, ShlObj, ActiveX, Variants, Forms, DB, SysUtils, Dialogs;

function BooleanToStr(Value: boolean): string;
begin
Result := 'Нет';
if Value then Result := 'Да';
end;

function IsOLEObjectInstalled(OLEName: string): boolean;
{ Проверяет, запущен ли сервер автоматизации }
var
 ClassID: TGUID;
begin
 Result := CLSIDFromProgID(PWideChar(WideString(OLEName)), ClassID) = S_OK;
end;

procedure GridToOdc(Grid: TcxGridDBTableView);
{ Экспорт сетки данных в OpenOffice.org/LibreOffice.org }
var
 oo, coreprop, corepropitem, calc, tbl, fmt, tmp, sheets, sheet: OLEVariant;
 col, row, cols, rows, coli, rowi,
   fmt_string, fmt_date, fmt_time, fmt_integer, fmt_currency: integer;
 v: variant;
begin
 cols := Grid.VisibleColumnCount;
 rows := Grid.DataController.RowCount;
 if cols = 0 then begin
   ShowMessage('Количество экспортируемых столбцов равно 0. Вы не можете экспортировать пустой НД.');
   exit;
 end;
 if rows = 0 then begin
   ShowMessage('Количество экспортируемых строк равно 0. Вы не можете экспортировать пустой НД.');
   exit;
 end;
 if rows > 65500 then begin
   ShowMessage('Вы пытаетесь экспортировать ' + ITS(rows) + ' строк. Кол-ов экспортируемых строк не должно превышать 65500. Экспорт данных невозможен.');
   exit;
 end;
 if not IsOLEObjectInstalled('com.sun.star.ServiceManager') then begin
   ShowMessage('Не удалось обнаружить на Вашем компьютере установленный OpenOffice.org/LibreOffice.org. Экспорт данных невозможен.');
   exit;
 end;
 try oo := GetActiveOleObject('com.sun.star.ServiceManager');
 except oo := CreateOleObject('com.sun.star.ServiceManager');
 end;
 calc := oo.CreateInstance('com.sun.star.frame.Desktop');
 coreprop := VarArrayCreate([0, 0], VarVariant);
 corepropitem := calc.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
 corepropitem.Name := 'Hidden';
 corepropitem.Value := true;
 coreprop[0] := corepropitem;
 tbl := calc.LoadComponentFromURL('private:factory/scalc','_blank', 0, coreprop);
 tbl.getCurrentController.getFrame.getContainerWindow.setVisible(false);
 sheets := tbl.getSheets;
 sheet := sheets.getByIndex(0);

 tmp := tbl.GetNumberFormats;
 fmt := oo.Bridge_GetStruct('com.sun.star.lang.Locale');
 fmt_string := tmp.QueryKey(#64, fmt, false);
 if fmt_string < 0 then fmt_string := tmp.AddNew(#64, fmt);
 fmt_date := tmp.QueryKey('DD.MM.YYYY', fmt, false);
 if fmt_date < 0 then fmt_date := tmp.AddNew('DD.MM.YYYY', fmt);
 fmt_time := tmp.QueryKey('HH:MM:SS', fmt, false);
 if fmt_time < 0 then fmt_time := tmp.AddNew('HH:MM:SS', fmt);
 fmt_integer := tmp.QueryKey('# ##0', fmt, false);
 if fmt_integer < 0 then fmt_integer := tmp.AddNew('# ##0', fmt);
 fmt_currency := tmp.QueryKey('# ##0.00', fmt, false);
 if fmt_currency < 0 then fmt_currency := tmp.AddNew('# ##0.00', fmt);

 for col := 0 to pred(cols) do begin
   sheet.GetCellByPosition(col, 0).NumberFormat := fmt_string;
   sheet.GetCellByPosition(col, 0).SetString(Grid.VisibleColumns[col].Caption);
   sheet.GetCellByPosition(col, 0).getText.createTextCursor.CharWeight := 150;
   Application.ProcessMessages;
 end;
 for row := 0 to pred(rows) do begin
   rowi := Grid.ViewData.Rows[row].RecordIndex;
   for col := 0 to pred(cols) do begin
     coli := Grid.VisibleColumns[col].Index;
     v := Grid.DataController.Values[rowi, coli];
     case VarType(v) of
       varString, varOleStr, varUString, varStrArg, varEmpty, varNull: begin
         sheet.GetCellByPosition(col, row + 1).NumberFormat := fmt_string;
         sheet.GetCellByPosition(col, row + 1).SetString(VarToStr(v));
       end;
       varDate: begin
         if (VarToDateTime(v) < 30000) and (VarToDateTime(v) >= 1) then sheet.GetCellByPosition(col, row + 1).NumberFormat := fmt_time else sheet.GetCellByPosition(col, row + 1).NumberFormat := fmt_date;
         sheet.GetCellByPosition(col, row + 1).SetValue(VarToDateTime(v));
       end;
       varBoolean: begin
         sheet.GetCellByPosition(col, row + 1).NumberFormat := fmt_string;
         sheet.GetCellByPosition(col, row + 1).SetString(BooleanToStr(v));
       end;
       varSmallint, varShortInt, varInteger, varInt64, varUInt64, varWord, varLongWord, varByte: begin
         sheet.GetCellByPosition(col, row + 1).NumberFormat := fmt_integer;
         sheet.GetCellByPosition(col, row + 1).SetValue(v);
       end;
       varSingle, varDouble, varCurrency: begin
         sheet.GetCellByPosition(col, row + 1).NumberFormat := fmt_currency;
         sheet.GetCellByPosition(col, row + 1).SetValue(v);
       end;
     end;
   end;
   Application.ProcessMessages;
 end;

 for col := 0 to pred(cols) do begin
   sheet.getColumns.getByIndex(col).setPropertyValue('OptimalWidth', true);
   sheet.GetCellByPosition(col, 0).HoriJustify := 2;
   Application.ProcessMessages;
 end;

 tbl.getCurrentController.getFrame.getContainerWindow.setVisible(true);
 tmp := Unassigned;
 fmt := Unassigned;
 sheet := Unassigned;
 sheets := Unassigned;
 tbl := Unassigned;
 calc := Unassigned;
 corepropitem := Unassigned;
 coreprop := Unassigned;
 oo := Unassigned;
end;

end.


---

AlekVolsk   (2011-05-06 17:14:00) [2]

Кому интересно:

unit to_excel;

interface

uses cxGridDBTableView;

procedure GridToMSExcel(Grid: TcxGridDBTableView);

implementation

uses
 COMObj, ShlObj, ActiveX, Variants, Forms, DB, Dialogs;

const
 // Vertical alignment
 xl_VCenterAlignment = 2;

 // Hirizontal alignment
 xl_HLeftAlignment = 2;
 xl_HCenterAlingment = 3;
 xl_HRightAlignment = 4;

 // Borders
 xlEdgeLeft = 1;
 xlEdgeRight = 2;
 xlEdgeTop = 3;
 xlEdgeBottom = 4;
 xlInsideVertical = 11;
 xlInsideHorizontal = 12;
 xlDiagonalDown = 5;
 xlDiagonalUp = 6;

 // Line style
 xlNone = 0; // отсутствует
 xlContinuous = 1; // обычная
 xlDash = 2; // пунктир
 xlDot = 3; //пунктир точками
 xlDashDot = 4; // пунктир с точкой
 xlDashDotDot = 5; // пунктир с двумя точками
 xlSlantDashDot = 6; // пунктир со скошенной точкой
 xlDouble = $FFFFEFE9; // двойная

 // Line weight - толщина линии
 xlHairline = 1; // тонкая прерывистая, очень тонкий пунктир, LineStyle := xlContinuous;
 xlThin = 2; // стандартная
 xlMedium = 3; // толстая
 xlThick = 4; // очень толстая

function ITS(I: integer): string;
var
 S: shortstring;
begin
 try
   Str(I, S);
   Result := string(S);
 except Result := '';
 end;
end;

function BooleanToStr(Value: boolean): string;
begin
 Result := 'Нет';
 if Value then Result := 'Да';
end;

function GetIC(Value: integer): string;
{ Возвращает строковое представление порядкового номера заголовка столбца в MS Excel }
var
 x, y: integer;
begin
 Result := '';
 case Value of
   1..26: Result := Result + Chr(Value + 64);
   27..702: begin
     x := Value div 26;
     y := Value mod 26;
     Result := Result + Chr(x + 64);
     Result := Result + Chr(y + 64);
   end;
 end;
end;

function IsOLEObjectInstalled(OLEName: string): boolean;
{ Проверяет, запущен ли сервер автоматизации }
var
 ClassID: TGUID;
begin
 Result := CLSIDFromProgID(PWideChar(WideString(OLEName)), ClassID) = S_OK;
end;

procedure GridToMSExcel(Grid: TcxGridDBTableView);
{ Экспорт сетки данных в MS Excel }
var
 xls, wb: OLEVariant;
 col, row, cols, rows, coli, rowi: integer;
 s: string;
 ArrayData, v: variant;
begin
 cols := Grid.VisibleColumnCount;
 rows := Grid.DataController.RowCount;
 if rows = 0 then begin
   ShowMessage('Количество экспортируемых строк равно 0. Вы не можете экспортировать пустой НД.');
   exit;
 end;
 if rows > 65500 then begin
   ShowMessage('Вы пытаетесь экспортировать ' + ITS(rows) + ' строк. Кол-ов экспортируемых строк не должно превышать 65500. Экспорт данных невозможен.');
   exit;
 end;
 if not IsOLEObjectInstalled('Excel.Application') then begin
   ShowMessage('Не удалось обнаружить на Вашем компьютере установленный MS Excel. Экспорт данных невозможен.');
   exit;
 end;
 try xls := GetActiveOleObject('Excel.Application');
 except xls := CreateOleObject('Excel.Application');
 end;
 wb := xls.Workbooks.Add;

 for col := 1 to cols do begin
   wb.WorkSheets[1].Cells[1, col].Value := Grid.VisibleColumns[pred(col)].Caption;
   s := GetIC(col);
   s := s + ':' + s;
   xls.Range[s].Select;
   case Grid.DataController.DataSet.FieldByName((Grid.VisibleColumns[pred(col)] as TcxGridDBColumn).DataBinding.FieldName).DataType of
     ftString, ftMemo, ftFixedChar, ftWideString, ftFmtMemo, ftWideMemo, ftBoolean: s := #64;
     ftDate, ftDateTime, ftTimeStamp: s := 'ДД.ММ.ГГГГ';
     ftTime: s := 'чч:мм:сс;@';
     ftBCD, ftFloat, ftCurrency: s := '# ##0,00';
   else s := '# ##0';
   end;
   xls.Selection.NumberFormat := s;
   xls.Selection.NumberFormatLocal := s;
   Application.ProcessMessages;
 end;

 ArrayData := VarArrayCreate([1, rows, 1, cols], varVariant);
 for row := 1 to rows do begin
   rowi := Grid.ViewData.Rows[pred(row)].RecordIndex;
   for col := 1 to cols do begin
     coli := Grid.VisibleColumns[pred(col)].Index;
     v := Grid.DataController.Values[rowi, coli];
     case Grid.DataController.DataSet.FieldByName((Grid.VisibleColumns[pred(col)] as TcxGridDBColumn).DataBinding.FieldName).DataType of
       ftString, ftMemo, ftFixedChar, ftWideString, ftFmtMemo, ftWideMemo: ArrayData[row, col] := VarToStr(v);
       ftBoolean: ArrayData[row, col] := BooleanToStr(v);
     else ArrayData[row, col] := v;
     end;
     Application.ProcessMessages;
   end;
   Application.ProcessMessages;
 end;
 s := 'A2:' + GetIC(cols) + ITS(rows + 1);
 xls.Range[s].Value := ArrayData;

 xls.Range[s].Select;
 xls.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous;
 xls.Selection.Borders[xlEdgeLeft].Weight := xlMedium;
 xls.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous;
 xls.Selection.Borders[xlEdgeRight].Weight := xlMedium;
 xls.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous;
 xls.Selection.Borders[xlEdgeBottom].Weight := xlMedium;
 xls.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous;
 xls.Selection.Borders[xlInsideVertical].Weight := xlThin;
 xls.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous;
 xls.Selection.Borders[xlInsideHorizontal].Weight := xlThin;

 s := 'A1:' + GetIC(cols) + '1';
 xls.Range[s].Select;
 xls.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous;
 xls.Selection.Borders[xlEdgeLeft].Weight := xlMedium;
 xls.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous;
 xls.Selection.Borders[xlEdgeRight].Weight := xlMedium;
 xls.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous;
 xls.Selection.Borders[xlEdgeTop].Weight := xlMedium;
 xls.Selection.Borders[xlEdgeBottom].LineStyle := xlDouble;
 xls.Selection.Borders[xlEdgeBottom].Weight := xlThick;
 xls.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous;
 xls.Selection.Borders[xlInsideVertical].Weight := xlThin;

 xls.Range['1:1'].Select;
 xls.Selection.Font.Bold := true;
 xls.Selection.VerticalAlignment := xl_VCenterAlignment;
 xls.Selection.HorizontalAlignment := xl_HCenterAlingment;
 xls.Selection.NumberFormat := #64;
 xls.Selection.NumberFormatLocal := #64;

 s := 'A:' + GetIC(cols);
 xls.Range[s].Select;
 xls.Selection.Columns.AutoFit;
 wb.WorkSheets[1].PageSetup.LeftMargin := 25;   // 2.5 единицы Excel = 1 миллиметр
 wb.WorkSheets[1].PageSetup.RightMargin := 25;
 wb.WorkSheets[1].PageSetup.TopMargin := 25;
 wb.WorkSheets[1].PageSetup.BottomMargin := 25;

 xls.Cells[1, 1].Select;
 xls.Visible := true;
 ArrayData := null;
 wb := null;
 xls := null;
 HideProgress;
end;

end.


---

sniknik   (2011-05-05 12:10:00) [3]

> обновил Delphi с 2006 до XE
вот для этого и существует в создании вопроса галочка, или поле если нету, версии дельфей.
спартизанил, вот и помучился.

---

Anatoly Podgoretsky   (2011-05-05 12:09:00) [4]

> AlekVolsk  (05.05.2011 11:57:08)  [8]

Это значит что ты вводил символ в другой кодировке и поленился исправить
его.

---

AlekVolsk   (2011-05-05 11:57:00) [5]

А ларчик-то просто открывался: Unicode!
Вместе с обновлением офиса до 2010 я обновил Delphi с 2006 до XE. а он на юникоде весь! т.е. годда я передаю символ @, это еще не значит что я передаю непосредственно символ с кодом #64, т.к. в русской локализации символ @ имеет код аж восемьсот-какой-то (непомню точно).
Обнаружил, когда пересобрал проект снова на D2006, все данные в Excel2010 перекатывались на отлично. Тут-то я и вспомнил про юникод...

wb.WorkSheets[1].Cells[1, 1].NumberFormat := #64;

---

sniknik   (2011-05-04 19:34:00) [6]

> Я не знаю куда залезьть, посмотреть пример, как передать через FormulaR1C1.
http://www.google.ru/search?q=%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D1%8C+%D0%BC%D0%B0%D0%BA%D1%80%D0%BE%D1%81%D0%B0+%D0%B2+excel&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:ru:official&client=firefox

> хотя это и не совсем правильно.
совсем не правильно, даже не скомпилируется...
а вот так вполне нормально
wb.WorkSheets[1].Cells[1, 1].Value := '''1528.26';

---

AlekVolsk   (2011-05-04 19:12:00) [7]

Я с макросами на vba не очень дружен, к тому же просто очень поверхностно знаком с устройством Excel, мне просто нужно:
1. вывести данные с грида в книгу, с шапкой;
2. по заполненым ячейкам выставить границы;
3. шапку сделать болдом;
4. заполненым колонкам сделать AutoFit.
В гриде переменное кол-во полей/записей сог-но настройкам/фильтрам конечного пользователя.
Я не знаю куда залезьть, посмотреть пример, как передать через FormulaR1C1.
Пока строковые значения передаю как
wb.WorkSheets[1].Cells[1, 1].Value := '''' + 1528.26;
хотя это и не совсем правильно.

---

sniknik   (2011-05-04 14:11:00) [8]

> правильнее было бы так
вернее, еще правильнее, так как макрос генерит, т.е. через FormulaR1C1

---

sniknik   (2011-05-04 14:10:00) [9]

> А еще надо NumberFormatLocal  смотреть/ ставить
зачем если по формату от присваивает строку... типа обязуется, но после правда вносит число.
> wb.WorkSheets[1].Cells[1, 1].NumberFormat := ' @';
> wb.WorkSheets[1].Cells[1, 1].Value := 1528.26;
правильнее было бы так
wb.WorkSheets[1].Cells[1, 1].NumberFormat := '@';
wb.WorkSheets[1].Cells[1, 1].Value := '1528.26 ';

---

OW   (2011-05-04 10:09:00) [10]

> Установка формата в книге вручную и чтение NumberFormat
> программно

А еще надо NumberFormatLocal  смотреть/ ставить

FormatSheet(ActiveSheets, 2, 5, RowCount, 5, '#,##0', '# ##0,00');

procedure FormatSheet(Sheet: Variant; BR, BC, ER, EC: Integer; Formats, FormatsLocal: string);
var
 Range, C1, C2: Variant;
begin
 C1 := Sheet.Cells[BR, BC];
 C2 := Sheet.Cells[ER, EC];
 Range := Sheet.Range[C1, C2];
 if Formats <> '' then
   Range.NumberFormat := Formats;
 if FormatsLocal <> '' then
   Range.NumberFormatLocal := FormatsLocal;
end;

---

sniknik   (2011-05-04 07:53:00) [11]

> Опытным путем
а как же обычное - записать макрос и посмотреть?

> wb.WorkSheets[1].Cells[1, 1]. Value:= 1528.26;
а как макрос в 2010м вносит значения? в 2003м не через Value, а через FormulaR1C1. и так думаю правильней, но не лишнее бы и посмотреть в макрос. научится пользоваться.

---

AlekVolsk   (2011-05-04 01:14:00) [12]

Опытным путем (Установка формата в книге вручную и чтение NumberFormat программно) установил:
 @ - текст
 Основной - общий
 0 - числовой
 0,00 - числовой
 # ##0,00 - числовой с разделителем
 ДД.ММ.ГГГГ - дата
 чч:мм:сс;@ - время

Однако, если я программно учтанавливаю любое из этих значений, то по факту записывается значение 64:
var
 xls, wb: OLEVariant;
begin
 try xls := GetActiveOleObject('Excel.Application');
 except xls := CreateOleObject('Excel.Application');
 end;
 wb := xls.Workbooks.Add;
 // ...
 wb.WorkSheets[1].Cells[1, 1].NumberFormat := '@';
 wb.WorkSheets[1].Cells[1, 1].Value := 1528.26;
 // ...
end;

Cell[1, 1] содержит формат 64, отображаемое значение в строке формул 1528.26, отображаемое значение в ячейке 64. Почему? Что я делаю не так? В Excel с 2000 по 2007 все работает на отлично!

---

Наверх