Не удается создать правильную электронную таблицу Excel с ColdFusion

Question

У меня есть запрос от клиента на создание электронной таблицы Excel из запроса. У меня есть запрос, выбивающий поля, и я могу создать файл Excel без помех. Проблема возникает, когда клиент берет этот файл Excel, а затем пытается манипулировать им.

Большая часть проблемы связана с полями, которые должны быть помечены как валюта или даты. Я с некоторым трудом могу создать "реальное" поле даты. До этого Excel не сортировал даты должным образом. Мне удалось вызвать формулу Excel с помощью кода ниже. DateValue заставляет Excel признать это как поле реальной даты. Тем не менее, это не удается, когда этот файл манипулирует через Excel.

<cfset SpreadsheetSetCellFormula(s
          ,"DATEVALUE(#Chr(34)##Replacement_ETD##Chr(34)#)"
          , therow
          , 9)>

Следующая проблема - поле валюты. Я не могу заставить Excel признать значения в валюте. Это всегда приходит на заказ. Когда это установлено, SUM функция не будет работать в Excel. Вы можете добавить поля индивидуально, как A1+B1+C1 = TOTAL, Тем не менее, это не будет полезно, когда есть 200 строк.

Я смог получить предложение от другого программиста CF, у которого была похожая ситуация. Сначала он сгенерировал файл Excel с правильными заголовками и установил в столбцах соответствующие поля, такие как дата и валюта и т. Д.

Следующим шагом будет заполнение полей строка за строкой, и они должны быть правильно отформатированы.

Код:

<cfset filename = expandPath("./reports/arrivals.xlsx")>
<cfspreadsheet  action="read" src = "#filename#"  name = "s" >
<cfset therow = 0>
<cfoutput query="myExcel" startrow="1">
    <cfset therow = myExcel.currentrow + 1>
    <cfset SpreadsheetSetCellValue(s, Incumbent, therow, 1)>
    <cfset SpreadsheetSetCellValue(s, Section, therow, 2)>
    <cfset SpreadsheetSetCellValue(s, Position_Number, therow, 3)>
    <cfset SpreadsheetSetCellValue(s, Position_Title, therow, 4)>
    <cfset SpreadsheetSetCellValue(s, Incumbent_Emplyment_Type, therow, 5)>
    <cfset SpreadsheetSetCellValue(s, Incumbent_ETD, therow, 6)>
    <cfset SpreadsheetSetCellValue(s, Tour_Comments, therow, 7)>
    <cfset SpreadsheetSetCellValue(s, Replacement, therow, 8)>
    <cfset SpreadsheetSetCellValue(s, Replacement_ETA, therow, 9)>
</cfoutput>    
<cfheader name="content-disposition" value="attachment; filename=Departures_(#DateFormat(now(),'mmddyy')#).xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Данные в ячейках уже были правильно отформатированы. Когда этот файл генерируется и передается пользователю, столбцы не форматируются должным образом.

Кто-нибудь еще знает, сработает ли этот метод, или у него есть лучшее предложение о том, чтобы заставить CF генерировать правильную дату и поле валюты для подтверждения Excel?

Adobe ColdFusion v10 работает на RHEL 5.

Для каждого запроса здесь используется некоторый код queryNeW, который будет генерировать кодовые даты и валюту.

Шаг первый: я создал файл Excel с первой замороженной строкой, в которой есть заголовок столбца. Первая колонка была обозначена как дата в формате длинная дата - мм / дд / ггг; Второй столбец - это доллар, который был установлен в валюте.

Я читаю этот файл, затем заполняю строки и передаю файл пользователю для загрузки.

<cfset filename = expandPath("./reports/Test.xlsx")>

<cfspreadsheet  action="read" src = "#filename#"  name = "s" >

<cfset myQuery = QueryNew("MyDate, Dollar", "Date, Decimal")> 

<cfset newRow = QueryAddRow(MyQuery, 5)> 

<cfset temp = QuerySetCell(myQuery, "MyDate", "03-11-2000", 1)> 
<cfset temp = QuerySetCell(myQuery, "Dollar", "403.45", 1)> 

<cfset temp = QuerySetCell(myQuery, "MyDate", "01-01-2009", 2)> 
<cfset temp = QuerySetCell(myQuery, "Dollar", "603.22", 2)> 

<cfset temp = QuerySetCell(myQuery, "MyDate", "09-21-2013", 3)> 
<cfset temp = QuerySetCell(myQuery, "Dollar", "103.55", 3)> 

<cfset temp = QuerySetCell(myQuery, "MyDate", "01-15-2005", 4)> 
<cfset temp = QuerySetCell(myQuery, "Dollar", "3.33", 4)> 

<cfset temp = QuerySetCell(myQuery, "MyDate", "07-22-2003", 5)> 
<cfset temp = QuerySetCell(myQuery, "Dollar", "13.75", 5)> 

<cfset therow = 0>
<cfoutput query="myQuery" startrow="1">
  <cfset therow = myQuery.currentrow + 1>

  <cfset SpreadsheetSetCellValue(s, DateFormat(MyDate, 'mm/dd/yyyy'), therow, 1)>
  <cfset SpreadsheetSetCellValue(s, Dollar, therow, 2)>
  #myQuery.currentrow# <br>
  #myQuery.MyDate# <br>
  #myQuery.Dollar# <br>
</cfoutput>          

<cfheader name="content-disposition" value="attachment; 
                  filename=Departures_(#DateFormat(now(),'mmddyy')#).xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Вы можете открыть файл в MS Excel или в Google Sheets. Протестируйте один, с первой замороженной строкой, мы должны быть в состоянии сортировать по полю даты. Мои результаты: даты не сортируются должным образом. В столбце 2 с валютой, если мы попытаемся сделать СУММУ, которая действительно работает! Это не сработало раньше, но сейчас.

Кроме того, когда я пытаюсь открыть файл, я получаю предупреждение, что этот файл поврежден, и Excel попытается открыть его. Я не получаю такого предупреждения в Google Sheets.

Answer 1

CF может быть немного странным при работе с датами. Excel довольно хорошо угадывает правильный тип ячейки, когда значение вводится вручную. Однако с CF немного сложнее. Поскольку CF является относительно типизированным, он не всегда правильно сопоставляет значения и типы ячеек. Использование функций, использующих объект запроса, вместо SpreadsheetSetCellValue() обычно приводит к лучшим результатам. Скорее всего, потому что объекты запроса содержат как значения, так и типы данных. Хотя по состоянию на CF11 SpreadsheetSetCellValue поддерживает новый параметр данных, который позволяет указывать как значение, так и тип данных ячейки. Поскольку вы используете CF10, попробуйте использовать SpreadsheetAddRows для заполнения значений.

Что касается предупреждения о том, что файл поврежден, то это связано с тем, что фактическое содержимое файла и расширение файла в коде загрузки не совпадают. Код читается в файле .xlsx, но загрузка утверждает, что это. файлxls (application / msexcel). Чтобы избавиться от ошибки, убедитесь, что два совпадают.

Вот рабочий пример, протестированный с CF11

<!---
    Test.xlsx contains two columns, with headers on row 1
    - Column A format: *m/d/yyyy
    - Column B format: number with 2 decimal places
--->
<cfspreadsheet  action="read" src="c:/temp/Test.xlsx"  name="sheet" >

<cfset myQuery = QueryNew("")> 
<cfset QueryAddColumn(MyQuery, "Dollar", "Decimal", [ 403.45, 703.22, 103.55, 3.33, 13.75]  )>
<cfset QueryAddColumn(MyQuery, "MyDate", "date", [ parseDateTime("2000-03-11", "yyyy-mm-dd")
                                                , parseDateTime("2009-01-01", "yyyy-mm-dd")
                                                , parseDateTime("2013-09-21", "yyyy-mm-dd")
                                                , parseDateTime("2005-01-15", "yyyy-mm-dd")
                                                , parseDateTime("2003-07-22", "yyyy-mm-dd")] ) > 

<cfset spreadsheetAddRows(sheet, myQuery)>
<cfset spreadsheetFormatColumn(sheet, {dataFormat="m/d/yy"}, 1)>
<cfset spreadsheetFormatColumn(sheet, {dataFormat="##,####0.00"}, 2)>

<cfheader name="content-disposition" value="attachment; filename=Departures_(#DateFormat(now(),'mmddyy')#).xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadsheetReadBinary(sheet)#" reset="true">