[C#] Excel Workbook XML 파일 형식으로 저장하기 (Workbook.SaveAs)

 

Excel File 생성하는 방법과 동일하게

Excel.Application을 이용해 엑셀 연결 후

Workbook 생성, WorkSheet 생성한 다음

저장할 때 XML 파일로 저장하는 방법이 있다.

 

 

Workbook.SaveAs 메서드를 이용하여

파일 포맷을 XML 형식으로 지정 후

나머지 옵션들을 설정하여 저장하면 된다.

 

 

(참고1 - Microsoft 공식문서)

Workbook.SaveAs 메서드 (Microsoft.Office.Tools.Excel)

 

(참고2 - 이전 발행글)

[C#] Excel 사용하기. Create Excel File + Update Excel File

 

(참고3 - 이전 발행글)

[C#] Excel NumberFormat 표시 형식 지정해서 저장하기

 

 

[ 전체 소스코드 ]

public static bool SaveExcelXmlFile()
{
    try
    {
        // 1. ExcelApp 연결
        excelApp = new Excel.Application();
        if (excelApp == null)
        {
            return false;
        }
 
        // 2. Workbook 생성
        wb = excelApp.Workbooks.Add();
 
        // 3. WorkSheet 생성
        ws = (Excel.Worksheet)wb.Sheets.Add();
        ws.Name = "Data";
 
        // 4. 데이터 넣기
 
        // 1) 일반
        ws.Cells[1, 1] = "Date";
        // 1행 NumberFormat 일반 형식으로 지정
        ws.Rows[1].NumberFormat = "General";
 
        // 2) 날짜
        ws.Cells[2, 1] = DateTime.Now.ToString("yy/MM/dd HH:mm:ss");
        // A2 NumberFormat 날짜 형식으로 지정
        ws.Range["A2"].NumberFormat = "yy/m/d hh:mm:ss";
 
        // 3) 숫자
        ws.Cells[1, 2] = "Data";
        ws.Cells[2, 2] = 0.0000000123;
        ws.Cells[3, 2] = 0.00000000234;
        ws.Cells[4, 2] = 0.000000000345;
        ws.Cells[5, 2] = 0.0000000000456;
        // B2 Cell부터 1행 4열 만큼 NumberFormat 지수 형식으로 지정
        ws.Range["B2"].Resize[1, 4].NumberFormat = "0.00E+00";
 
        // 4) 회계
        ws.Cells[1, 3] = 97.80;
        ws.Cells[2, 3] = 4527.09;
        ws.Cells[3, 3] = 365087.97;
        // C열 NumberFormat 회계 형식으로 지정
        ws.Columns["C"].NumberFormat = "$#,###,##0.00";
 
        // 5. Workbook 저장하고 닫기
        string sFilePath = $@"D:\123456\ExcelSaveTest.xml";
        wb.SaveAs(sFilePath,
            Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
            null, null, false, false,
            XlSaveAsAccessMode.xlNoChange,
            Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
            false, null, null, true);
        wb.Close(Type.Missing, Type.Missing, Type.Missing);
 
        // 6. ExcelApp 종료
        excelApp.Quit();
    }
    catch (Exception ex)
    {
        MessageBox.Show($"엑셀파일 저장 에러.");
        return false;
    }
    finally
    {
        releaseObject(excelApp);
        releaseObject(ws);
        releaseObject(wb);
    }
 
    return true;
}

 

 

[저장된 XML 파일]

<?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>Ella</Author>
  <LastAuthor>Ella</LastAuthor>
  <Created>2024-12-05T06:24:35Z</Created>
  <Version>12.00</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9240</WindowHeight>
  <WindowWidth>12420</WindowWidth>
  <WindowTopX>400</WindowTopX>
  <WindowTopY>50</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="129" x:Family="Modern" ss:Size="11"
    ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <NumberFormat/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="yy/m/d\ hh:mm:ss"/>
  </Style>
  <Style ss:ID="s64">
   <NumberFormat ss:Format="Scientific"/>
  </Style>
  <Style ss:ID="s65">
   <NumberFormat ss:Format="&quot;$&quot;#,###,##0.00"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Data">
  <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="17">
   <Column ss:Index="3" ss:StyleID="s65" ss:Width="66.5"/>
   <Row ss:StyleID="s62">
    <Cell><Data ss:Type="String">Date</Data></Cell>
    <Cell><Data ss:Type="String">Data</Data></Cell>
    <Cell ss:StyleID="s65"><Data ss:Type="Number">97.8</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s63"><Data ss:Type="String">24/12/05 15:24:35</Data></Cell>
    <Cell ss:StyleID="s64"><Data ss:Type="Number">1.2299999999999999E-8</Data></Cell>
    <Cell><Data ss:Type="Number">4527.09</Data></Cell>
    <Cell ss:StyleID="s64"/>
    <Cell ss:StyleID="s64"/>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="Number">2.3400000000000002E-9</Data></Cell>
    <Cell><Data ss:Type="Number">365087.97</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="Number">3.45E-10</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="Number">4.5600000000000003E-11</Data></Cell>
   </Row>
  </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>
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="17">
  </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>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="17">
  </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>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="52" ss:DefaultRowHeight="17">
  </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>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
 

 

ExcelSaveTest.xml

0.00MB