We have a vendor we ship claims to from time to time, and they use a manual process. Because they are in the public sector, they require a certain format for their claims, which includes a lot of unnecessary Excel formulas because that's what they audit based on (the formulas themselves). Does it matter that we could generate an excel file that has every number they need along the way? No, they must have the FORMULAS in the spreadsheet, or it isn't a valid claim.
Unfortunately for me, our spreadsheet library we use (the "spreadsheet" ruby gem) doesn't have support for formulas yet. So our options were to A) spend some time to work on this gem and add formula support (time we didn't really have). Or B) make the spreadsheet generation a partly manual process on our end as well (Blech, we actually did this for a quarter, generate our numbers and copy them into the claim template one at a time).
This quarter, I went a different route:
Sub Main Dim Doc As Object Dim InputSheet As Object Dim TemplateSheet As Object SetupVariables(Doc,InputSheet,TemplateSheet,ClaimSheet) RowNum = 1 NameCell = InputSheet.getCellByPosition(2,RowNum) Do While NameCell.Type <> com.sun.star.table.CellContentType.EMPTY ClaimRow = InputSheet.Rows(RowNum) ClaimSheet = Doc.createInstance("com.sun.star.sheet.Spreadsheet") Doc.Sheets.insertByName(NameCell.getString(), ClaimSheet) CopyTemplateToNewClaim(TemplateSheet,ClaimSheet) ProcessClaim(InputSheet,ClaimSheet,1) RowNum = RowNum + 1 NameCell = InputSheet.getCellByPosition(2,RowNum) Loop End Sub
Yep, I used OpenOffice and BASIC and just scripted the damn thing. Ugly? Yes. But it worked, and we don't have to manually calculate our claims. I just generate our spreadsheet that has the claim numbers, and use the macro to copy each row into it's own template sheet in the workbook. It doesn't fit into our rails application at all, but we made it happen, and we're collecting on those claims.
If you're beating your head against the wall in frustration, sometimes it pays to step back and resort to plain old hackery, cause at the end of the day your customers only really care that it works.