This project has moved and is read-only. For the latest updates, please go here.

Problem with renaming columnnames and pivot tables - Excel crash

Aug 7, 2012 at 10:07 AM
Edited Aug 7, 2012 at 10:08 AM

Hi,

I'm trying to extract data from a database and populating to a datasheet. If I rename the columns to reflect the real names (localized) Excel crashes after I activate editmode (Excels builtin security feature)

Anyone knows a good solution to this problem? I will probobly create the table by hand instead.

The row that causes the problem is commented below.

Thanks

Daniel

 

            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                var wsData = pck.Workbook.Worksheets.Add("Source");
                
                var dataRange = wsData.Cells["A4"].LoadFromCollection(
                    from s in listWO
                    select new {
                        WONumber=s.WorkOrderNo,
                        WOName=s.WorkOrderName,
                        Status =s.Status,
                        Planned = s.Planned,
                        Performed =s.Performed,
                        Customer=s.Customer,
                        Category =s.Category,
                        Group =s.Group, 
                        Property="",
                        Building ="",
                        Object ="",
                        Location =s.Location,
                        WorkOrderType=s.WorkOrderType,
                        Time =s.TimeElapsed,
                        Cost=s.Cost,
                        Description=s.Instruction,
                        Comment = s.Comment
                    },
                   true, OfficeOpenXml.Table.TableStyles.Medium2);

                wsData.Cells[5, (int)Columns.Planned, dataRange.End.Row, (int)Columns.Planned].Style.Numberformat.Format = "yyyy-MM-dd hh:mm";
                wsData.Cells[5, (int)Columns.Performed, dataRange.End.Row, (int)Columns.Performed].Style.Numberformat.Format = "yyyy-MM-dd hh:mm";

                //This row causes Excel to crash
                //wsData.Tables.GetFromRange(dataRange).Columns[(int)Columns.Planned-1].Name = "Planerad";
                
                dataRange.AutoFitColumns();
                
                
                var wsPivot = pck.Workbook.Worksheets.Add("PivotSimple");
                var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PerEmploee");

                pivotTable1.RowFields.Add(pivotTable1.Fields[(int)Columns.Group - 1]);
                pivotTable1.ColumnFields.Add(pivotTable1.Fields[(int)Columns.Status - 1]);
                var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields[(int)Columns.Time - 1]);
                dataField.Format = "#,##0";
                pivotTable1.DataOnRows = true;

                return pck.GetAsByteArray();