Scientific-notation ønskes ændret til helt tal
HejJeg har problem ved generering af excel-ark. Problemet er at tal uønsket bliver omsat til scientific-notation:
990000784813 bliver således omsat til 9,0001E+11
Jeg har fundet dette link som fortæller problemet ligger i at excel gætter sig til formatet.
http://stackoverflow.com/questions/429853/scientific-notation-when-importing-from-excel-in-net
Der gives to løsninger:
1) using the COM interface of Excel
2) bruge et trediepartsprogram. Her foreslås http://www.spreadsheetgear.com/
Ad 1)
Her kræves at officepakken installeres på webserveren. Det kan godt lade sig gøre, omend det er fjollet.
Ad 2) Spreadsheetgear koster 1000 dollar. Det er i overkanten.
Kan I hjælpe mig, ved at finde en løsning i kodningen (helst) eller - alternativt - finde et 3.partsprogram der er noget billigere end SpreadSheetGear?
Det er
xlWorkSheet.Cells[i, 2] = theRow["SP-reference"];
der er problemet.
Kode:
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
xlWorkBook = new Microsoft.Office.Interop.Excel.Application().Workbooks.Add(Missing.Value);
xlWorkBook.Application.Visible = true;
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.ActiveSheet;
xlWorkSheet.Cells[1, 1] = "Date";
xlWorkSheet.Cells[1, 2] = "SP-No";
xlWorkSheet.Cells[1, 3] = "DeliveryAdress";
xlWorkSheet.Cells[1, 4] = "SP-OrderNo";
xlWorkSheet.Cells[1, 5] = "SP-reference";
xlWorkSheet.Cells[1, 6] = "Weight";
xlWorkSheet.Cells[1, 7] = "WorkOrderNo";
xlWorkSheet.Cells[1, 8] = "MaterialNo";
xlWorkSheet.Cells[1, 9] = "Description";
xlWorkSheet.Cells[1, 10] = "SerialNo";
xlWorkSheet.Cells[1, 11] = "ReturnNo";
xlWorkSheet.get_Range("A1", "K1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "K1").Interior.ColorIndex = 44;
// her har jeg forsøgt mig frem. Kan man evt. lægge en formel ind, som konverterer?
xlWorkSheet.get_Range("A2", "A100").FormatConditions.Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression, Type.Missing, "true", Type.Missing);
DataSet dataset = GetPartReturnsByDatePhysicallyReturned(Convert.ToDateTime(Convert.ToDateTime(Date.Text)));
int i = 2;
foreach (DataRow theRow in dataset.Tables[0].Rows)
{
xlWorkSheet.Cells[i, 7] = theRow["Date"];
xlWorkSheet.Cells[i, 2] = theRow["SP-reference"];
xlWorkSheet.Cells[i, 3] = theRow["Ordrenr."];
xlWorkSheet.Cells[i, 4] = theRow["Mat.nr."];
xlWorkSheet.Cells[i, 5] = theRow["Varebesk."];
xlWorkSheet.Cells[i, 6] = theRow["Serienr."];
xlWorkSheet.Cells[i, 1] = theRow["Ordreref."];
xlWorkSheet.Cells[i,8] = theRow["Returnr."];
i++;
}
// Autofit the columns
xlWorkSheet.Columns.AutoFit();