вторник, 5 июля 2011 г.

Экспорт SPList in Excel

Поговорим о экспoрте листа в Excel, и сохранения его в библиотеке документов.
Очень часто возникает необходимость экспортировать не весь список в excel а только некую его часть. Я покажу пример того, как быстро и удобно построить такой экспорт. Использовать мы будем решение которое базируется на создании простого xml:
XmlDocument _report;
            StringBuilder file_content = new StringBuilder();
            file_content.Append(@"<?xml version='1.0'?>
                <?mso-application progid='Excel.Sheet'?>
                <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
                 xmlns:o='urn:schemas-microsoft-com:office:office'
                 xmlns:x='urn:schemas-microsoft-com:office:excel'
                 xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
                 xmlns:html='http://www.w3.org/TR/REC-html40'>
                 <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
                  <Author>PIB</Author>
                  <LastAuthor>PIB</LastAuthor>
                  <LastPrinted>2009-09-15T12:46:54Z</LastPrinted>
                  <Created>2004-05-05T13:41:40Z</Created>
                  <LastSaved>2010-01-18T12:59:49Z</LastSaved>
                  <Company>PIB</Company>
                  <Version>1.00</Version>
                 </DocumentProperties>
                 <OfficeDocumentSettings xmlns='urn:schemas-microsoft-com:office:office'>
                  <AllowPNG/>
                 </OfficeDocumentSettings>
                 <ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
                  <SupBook>
                   <Path>Sheet2</Path>
                   <SheetName>Sheet2</SheetName>
                  </SupBook>
                  <WindowHeight>6450</WindowHeight>
                  <WindowWidth>9720</WindowWidth>
                  <WindowTopX>-15</WindowTopX>
                  <WindowTopY>-15</WindowTopY>
                  <TabRatio>831</TabRatio>
                  <PrecisionAsDisplayed/>
                  <DoNotSaveLinkValues/>
                  <ProtectStructure>False</ProtectStructure>
                  <ProtectWindows>False</ProtectWindows>
                 </ExcelWorkbook>
                 <Styles>
                  <Style ss:ID='Default' ss:Name='Normal'>
                   <Alignment ss:Vertical='Bottom'/>
                   <Borders/>
                   <Font ss:FontName='Arial Cyr' x:CharSet='204'/>
                   <Interior/>
                   <NumberFormat ss:Format='_(* #,##0_);_(* \(#,##0.00\);_(* ??_);_(@_)'/>
                   <Protection/>
                  </Style>
                  <Style ss:ID='s62'>
                  <Alignment ss:Vertical='Bottom' ss:WrapText='1'/>
                  <NumberFormat ss:Format='@'/>
                  </Style>
                 </Styles>
                <Worksheet ss:Name='Рабочий лист");
 Дальше объявляем поля:
file_content.Append(@"'>
                                 <Names>
                                   <NamedRange ss:Name='spp10test_akhz_bp_vw_day_real_plan_all'
                                    ss:RefersTo='=Sheet2!R1C1:R31465C8'/>
                                  </Names>
                              <Table ss:ExpandedColumnCount='14'>
                               <Column ss:Width='20'/>
                               <Column ss:Width='80'/>
                               <Column ss:Width='150'/>
                               <Column ss:Width='250'/>
                               <Column ss:Width='100'/>
                               <Column ss:Width='50'/>
                               <Column ss:Width='152'/>
                               <Column ss:Width='100'/>
                               <Column ss:Width='122'/>
                               <Column ss:Width='122'/>
                               <Column ss:Width='180'/>
                               <Column ss:Width='100'/>
                               <Column ss:Width='102'/>
                               <Column ss:Width='140'/>
                               <Row>");
Вносим название колонок из массива:
public static string[] Workshops = new string[] {
            "№ п/п",
            "Дата и время",
            "Тема жалобы",
            "Описание жалобы",
            "Регион",
            "Город",
            "Ф.И.О. Клиента",
            "Телефон клиента",
            "Зарегистрировал",
            "Исполнитель",
            "Результат решения",
            "Дата, время обратной связи",
            "Примечания",
            "Статус обращения" 
        };
 Добавляем поля:
foreach(string name in Workshops)
            {
                file_content.Append(@"<Cell><Data ss:Type='String'>" +name + @"</Data></Cell>");
            }
             file_content.Append(@"</Row>");Дальше я написал фильтер по дате для выбору данных из input
 start_date = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.ParseExact(Hidden_start_date.Value.ToString(), "dd.MM.yyyy", CultureInfo.InvariantCulture));
                end_date = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.ParseExact(Hidden_end_date.Value.ToString(), "dd.MM.yyyy", CultureInfo.InvariantCulture));
         
и сам запрос к листу:
SPQuery query = new SPQuery();
            query.Query = "<Where><And><Geq><FieldRef Name='CallbackDate' /><Value  Type='DateTime'>" + start_date + "</Value></Geq><Leq><FieldRef Name='CallbackDate' /><Value Type='DateTime'>" + end_date + "</Value></Leq></And></Where>";
            SPListItemCollection items = data_List.GetItems(query);
Теперь заполняем ячейки данными:
foreach (SPListItem item in items)
            {
                file_content.Append(@"<Row><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[0]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[1]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[2]]));
                file_content.Append(@"</Data></Cell><Cell ss:StyleID='s62'><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[3]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[4]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[5]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String' >");
                file_content.Append(Get_String_From_Obj(item[Workshops[6]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[7]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(getUser(new SPFieldUserValue(web, Get_String_From_Obj(item["Author"]))));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(getUser(new SPFieldUserValue(web,Get_String_From_Obj(item[Workshops[9]]))));
                file_content.Append(@"</Data></Cell><Cell ss:StyleID='s62'><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[10]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[11]]));
                file_content.Append(@"</Data></Cell><Cell ss:StyleID='s62'><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[12]]));
                file_content.Append(@"</Data></Cell><Cell><Data ss:Type='String'>");
                file_content.Append(Get_String_From_Obj(item[Workshops[13]]));
                file_content.Append(@"</Data></Cell></Row>");
            }
<Cell ss:StyleID='s62'> это стиль для текстового поля с переносом по буквам в ячейке.
методы которые тут задействованы:

private string Get_String_From_Obj(object obj)
        {
            string str = "";
            if (obj != null)
            {
                str = obj.ToString();
            }
            return str;
        }
        public string getUser(SPFieldUserValue field_user)
        {
            if (field_user != null)
            {
                return Get_String_From_Obj(field_user.LookupValue);
            }
            return "";
        }
Дальше добавим в нашу xml следующие:
file_content.Append(@"</Table>
                            <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
                            <PageSetup>
                            <Header x:Margin='0.3'/>
                            <Footer x:Margin='0.3'/>
                            <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>
                            </PageSetup>
                            <Visible>SheetHidden</Visible>
                            <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                            </Print>
                            <Selected/>
                            <FilterOn/>
                            <Panes>
                            <Pane>
                            <Number>3</Number>
                            <ActiveRow>25583</ActiveRow>
                            <ActiveCol>3</ActiveCol>
                            </Pane>
                            </Panes>
                            <ProtectObjects>False</ProtectObjects>
                            <ProtectScenarios>False</ProtectScenarios>
                            </WorksheetOptions>
                            </Worksheet>
                            </Workbook>");
И теперь сохраним его в библиотеку:
 MemoryStream stream = new MemoryStream();
            _report = new XmlDocument();
            _report.LoadXml(file_content.ToString());
            _report.Save(stream);
            SPUser user = web.CurrentUser;
            SPFile file = web.Files.Add(web.Url + "/" + sourceListObj.RootFolder.ToString() + "/" + String.Format("{0}_отчет_{1}.xls", user.Name, date.ToString("dddd, dd MMMM yyyy h mm tt")), stream.GetBuffer(), true);
            file.Update();
 Вот и все!
Автор: на

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)