用于实现导出XML格式的Excel文档的类

 

    /// <summary>
    /// 用于实现导出Excel文档的类
    /// </summary>
    public class Export
    {
        /// <summary>
        /// 导出Excel格式的xml文档
        /// </summary>
        /// <param name="grid">要导出的表格数据</param>
        /// <param name="FileName">要导出的文件名称</param>
        /// <param name="Author">作者</param>
        /// <param name="CompanyName">公司名称</param>
        public static void ExportExcel(DataGridView grid, string FileName, string Author, string CompanyName)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Excel文件(*.xml)|*.xml";
            dlg.FileName = FileName;
            if (dlg.ShowDialog() != DialogResult.OK) return;
            StringBuilder sb = new StringBuilder();
            //--工作表名称、列数、行数、创建时间、标题、作者、公司
            sb.Append(string.Format(
                xml_xls_top, FileName, grid.Columns.Count + 1, grid.Rows.Count + 1,
                DateTime.Now, FileName, Author, CompanyName));

            //定义各列的列宽信息
            sb.Append(string.Format("   <Column ss:Width='{0}'/>", grid.RowHeadersWidth));
            foreach (DataGridViewColumn col in grid.Columns)
            {
                if (col.Visible)
                    sb.Append(string.Format("   <Column ss:Width='{0}'/>", col.Width));
            }

            //表头部信息
            sb.AppendLine("   <Row ss:AutoFitHeight='0'>");
            sb.AppendLine("    <Cell ss:StyleID='s21'><Data ss:Type='String'>序号</Data></Cell>");
            foreach (DataGridViewColumn col in grid.Columns)
            {
                if (col.Visible)
                {
                    string value = col.HeaderText;
                    sb.AppendLine(string.Format("    <Cell ss:StyleID='s21'><Data ss:Type='String'>{0}</Data></Cell>", value));
                }
            }
            sb.AppendLine("   </Row>");

            int RowIndex = 0;   //表示行号
            //数据行信息
            foreach (DataGridViewRow dr in grid.Rows)
            {
                RowIndex++;
                //添加行头部信息
                sb.AppendLine("   <Row ss:AutoFitHeight='0'>");
                sb.AppendLine(string.Format("    <Cell ss:StyleID='s21'><Data ss:Type='Number'>{0}</Data></Cell>", RowIndex));
                foreach (DataGridViewCell c in dr.Cells)
                {
                    if (grid.Columns[c.ColumnIndex].Visible)
                    {
                        string value = c.EditedFormattedValue.ToString();
                        sb.AppendLine(string.Format("    <Cell ss:StyleID='s21'><Data ss:Type='String'>{0}</Data></Cell>", value));
                    }
                }
                sb.AppendLine("   </Row>");
            }
            sb.Append(xml_xls_bottom);
            try
            {
                File.WriteAllText(dlg.FileName, sb.ToString());
                Process.Start(dlg.FileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel出错:" + ex.Message, "系统提示",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /// <summary>
        /// 表示xml文档的头部信息
        /// </summary>
        private static string xml_xls_top =
@"<?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'>
  <Title>{4}</Title>
  <Author>{5}</Author>
  <LastAuthor>{6}</LastAuthor>
  <Created>{3}</Created>
  <Company>{6}</Company>
  <Version>11.5606</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
  <WindowHeight>13050</WindowHeight>
  <WindowWidth>14940</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>45</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID='Default' ss:Name='Normal'>
   <Alignment ss:Vertical='Center'/>
   <Borders/>
   <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID='s21'>
   <Borders>
    <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
    <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
    <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
    <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
   </Borders>
   <Font ss:FontName='宋体' x:CharSet='134'/>
  </Style>
 </Styles>
 <Worksheet ss:Name='{0}'>
  <Table ss:ExpandedColumnCount='{1}' ss:ExpandedRowCount='{2}' x:FullColumns='1'
   x:FullRows='1' ss:DefaultColumnWidth='54' ss:DefaultRowHeight='18'>";

        /// <summary>
        /// 表示xml文档的尾部信息
        /// </summary>
        private static string xml_xls_bottom =
            @"  </Table>
  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>149</PaperSizeIndex>
    <HorizontalResolution>180</HorizontalResolution>
    <VerticalResolution>180</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
";
    }