Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Jul 25, 2014 1:03 pm

Hi,
If I want pivot with field Date (format Italian "dd/mm/yyyy") in column
error in create execell (view exemple) this code:

PivotField r2 = pt.PivotFields["Datagiornaliera"] as PivotField;
r2.Axis = AxisTypes.Column;
pt.Options.RowHeaderCaption = "Datagionaliera";
r2.Subtotals = SubtotalTypes.None;
//Set subtotals at bottom of group
r2.SubtotalTop = false;

this error xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error042680_01.xml</logFileName>
<summary>Rilevati errori nel file 'C:\tmp\mio.xlsx'</summary>
- <removedParts summary="Di seguito è riportato un elenco delle parti rimosse:">
<removedPart>Parte rimossa: Parte /xl/pivotCache/pivotCacheDefinition1.xml con errore XML. (Cache tabella pivot) Errore di caricamento. Riga 1, colonna 0.</removedPart>
</removedParts>
- <removedFeatures summary="Di seguito è riportato un elenco delle caratteristiche rimosse:">
<removedFeature>Caratteristica rimossa: Rapporto di tabella pivot dalla parte /xl/pivotTables/pivotTable1.xml (Visualizzazione Tabella pivot)</removedFeature>
</removedFeatures>
</recoveryLog>

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Mon Jul 28, 2014 6:07 am

Hello,

Sorry for late reply as weekend.
Please provide us the excel document for testing, which would be helpful to work out the solution for you ASAP.
Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jul 28, 2014 10:09 am

Hi,
this is the file
thank
Attachments
errorDate.zip
(10.21 KiB) Downloaded 228 times

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Tue Jul 29, 2014 5:04 am

Hello,

Thanks for your document.
Please change your culture of current thread as InvariantCulture, following codes is for your reference.
Code: Select all
CultureInfo cc = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\errorDate.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet2 = workbook.CreateEmptySheet("Pivot Table");       
CellRange dataRange = sheet.AllocatedRange;
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);
PivotField r2 = pt.PivotFields["Datagiornaliera"] as PivotField;
r2.Axis = AxisTypes.Column;
pt.Options.RowHeaderCaption = "Datagionaliera";
r2.Subtotals = SubtotalTypes.None;
//Set subtotals at bottom of group
r2.SubtotalTop = false;
workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2007);
Thread.CurrentThread.CurrentCulture = cc;

If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jul 29, 2014 8:23 am

This is ok
thans

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Tue Jul 29, 2014 8:40 am

Hello,

Thanks for your quick reply.
If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jul 29, 2014 1:06 pm

Hi
Another question
Is possibile formattig date and change forecolor in Pivot ??
field(Datagiornaliera)
(look exemple)
Thanks very much
Attachments
testxlsx.zip
(18.52 KiB) Downloaded 218 times

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Wed Jul 30, 2014 7:06 am

Hello,

Thanks for your inquiry.
There are some codes for your reference. Please try it.
Code: Select all
 Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\testxlsx.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            Worksheet sheet2 = workbook.CreateEmptySheet("Pivot Table");

            CellRange dataRange = sheet.AllocatedRange;
            PivotCache cache = workbook.PivotCaches.Add(dataRange);

            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            pt.PivotFields["Datagiornaliera"].Axis = AxisTypes.Column;
            pt.PivotFields["Datagiornaliera"].NumberFormat = @"ddd,dd\/mm\/yyyy";
            pt.Options.ColumnHeaderCaption = "Datagionaliera";

            pt.PivotFields["repcodice"].Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "repcodice";

            pt.DataFields.Add(pt.PivotFields["Quanti"], "Quanti", SubtotalTypes.Sum);

            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium10;
       
            workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2007);

If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Wed Jul 30, 2014 9:24 am

Hi, this Ok for formated, but is possible change
color text of head "Datagiornaliera"
(if day is sunday color red else black for exemple)
Thank

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Thu Jul 31, 2014 3:24 am

Hello,

Please try the following code.
Code: Select all
 Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\testxlsx.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            Worksheet sheet2 = workbook.CreateEmptySheet("Pivot Table");

            CellRange dataRange = sheet.AllocatedRange;
            PivotCache cache = workbook.PivotCaches.Add(dataRange);

            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            pt.PivotFields["Datagiornaliera"].Axis = AxisTypes.Column;
            pt.PivotFields["Datagiornaliera"].NumberFormat = @"ddd,dd\/mm\/yyyy";
           
            pt.Options.ColumnHeaderCaption = "Datagionaliera";

            pt.PivotFields["repcodice"].Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "repcodice";

            pt.DataFields.Add(pt.PivotFields["Quanti"], "Quanti", SubtotalTypes.Sum);

            CellRange range = sheet2.Range["B2:HC2"];
            var negCondFormat = range.ConditionalFormats.AddCondition();
            negCondFormat.FormatType = ConditionalFormatType.Formula;
            negCondFormat.FirstFormula = "=WEEKDAY(B2)=1";
            negCondFormat.FontColor = System.Drawing.Color.Red;
     
            workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2007);

If there are any questions, welcome to get it back to us.
Sincerely,
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Thu Jul 31, 2014 10:02 am

hi
beatifull all ok
Is possible add color red for date '01/01/2014', '25/12/2014' over =WEEKDAY(D6)=1 ?
thank

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Fri Aug 01, 2014 2:32 am

Hello,

Please modify the firstformula.
Code: Select all
FirstFormula = "=OR(AND(YEAR(B2)=2014,MONTH(B2)=1,DAY(B2)=1),AND(YEAR(B2)=2014,MONTH(B2)=12,DAY(B2)=25),WEEKDAY(B2)=1)";

If there are any questions, welcome to get it back to us.
Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Aug 01, 2014 12:18 pm

You are the best
thanks

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Mon Aug 04, 2014 5:01 am

Hello,

Thanks for your feedback. If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Aug 04, 2014 10:54 am

Another question is possibile
autofitColumn Pivot ??
I try sheet2.AutoFitColumn(1);
sheet2.AutoFitColumn(2);
but not bad.
Is There another street ??

Thanks

ict@sogedin.it
 
Posts: 63
Joined: Mon Jul 15, 2013 2:06 pm

Return to Spire.XLS