我想将一个完整的XML文件转换为XLSX格式,但我不确定该怎么做。
我在Google上搜索了解决方案,但大多数情况下我只能找到相反的方向,例如从XLSX到XML。
在Microsoft页面上,我找到了一个xmlconvertclass,但我不确定如何使用这个类。
有人之前做过这样的事情并可以帮助我吗?
请尝试以下代码,我将XML转换为DataSet,然后导出DataSet到Excel
DataSet ds = new DataSet();
//Convert the XML into Dataset
ds.ReadXml(@"E:\movie.xml");
//Retrieve the table fron Dataset
DataTable dt = ds.Tables[0];
// Create an Excel object
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//Create workbook object
string str = @"E:\test.xlsx";
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(Filename: str);
//Create worksheet object
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
// Column Headings
int iColumn = 0;
foreach (DataColumn c in dt.Columns)
{
iColumn++;
excel.Cells[1, iColumn] = c.ColumnName;
}
// Row Data
int iRow = worksheet.UsedRange.Rows.Count - 1;
foreach (DataRow dr in dt.Rows)
{
iRow++;
// Row's Cell Data
iColumn = 0;
foreach (DataColumn c in dt.Columns)
{
iColumn++;
excel.Cells[iRow + 1, iColumn] = dr[c.ColumnName];
}
}
((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();
//Save the workbook
workbook.Save();
//Close the Workbook
workbook.Close();
// Finally Quit the Application
((Microsoft.Office.Interop.Excel._Application)excel).Quit();
如果您从数据集中获取多个表格,并且需要注意将数据表插入到Excel的逻辑,请使用以下代码。
static void Main(string[] args)
{
DataSet ds = new DataSet();
//Convert the XML into Dataset
ds.ReadXml(@"E:\movies.xml");
//Retrieve the table fron Dataset
//DataTable dt = ds.Tables[0];
// Create an Excel object
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//Create workbook object
string str = @"E:\test.xlsx";
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(Filename: str);
foreach (DataTable tab in ds.Tables)
{
FromDataTableToExcel(tab,excel,workbook);
}
//Save the workbook
workbook.Save();
//Close the Workbook
workbook.Close();
// Finally Quit the Application
((Microsoft.Office.Interop.Excel._Application)excel).Quit();
}
static void FromDataTableToExcel(DataTable dt, Microsoft.Office.Interop.Excel.Application excel, Microsoft.Office.Interop.Excel.Workbook workbook)
{
//Create worksheet object
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
// Column Headings
int iColumn = worksheet.UsedRange.Columns.Count-1;
int iColumn1 = iColumn;
int iColumn2 = iColumn;
foreach (DataColumn c in dt.Columns)
{
iColumn++;
excel.Cells[1, iColumn] = c.ColumnName;
}
// Row Data
int iRow = 0;
foreach (DataRow dr in dt.Rows)
{
iRow++;
// Row's Cell Data
foreach (DataColumn c in dt.Columns)
{
iColumn1++;
excel.Cells[iRow + 1, iColumn1] = dr[c.ColumnName];
}
iColumn1 = iColumn2;
}
((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();
}
如果有人需要将XML(表格数据)文件转换为XLSX而没有Microsoft Excel,下面是一个NPOI + C#的解决方案...
示例XML(表格数据):
<rows>
<row>
<col_1>a</col_1>
<col_2>b</col_2>
<col_3>c</col_3>
<col_4>d</col_4>
<col_5>e</col_5>
</row>
<row>
<col_1>f</col_1>
<col_2>h</col_2>
<col_3>h</col_3>
<col_4>i</col_4>
<col_5>j</col_5>
</row>
<row>
<col_1>k</col_1>
<col_2>l</col_2>
<col_3>m</col_3>
<col_4>n</col_4>
<col_5>o</col_5>
</row>
</rows>
C# 代码:
class clsNpoi
{
public static bool convert_xml_to_xlsx(string xml_path, string row_element_name)
{
try
{
//load xml
XElement elem = XElement.Load(xml_path);
if (elem.XPathSelectElements(row_element_name).Count() == 0)
{
return false; // exit if no row elements are found
}
else
{
//process;
string xpath_text = "//" + row_element_name;
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet(Path.GetFileNameWithoutExtension(xml_path));
//get column heads;
var heading_items = elem.XPathSelectElements(xpath_text).First().Elements().Select(e => e.Name.LocalName).Distinct().ToArray();
//get row items;
var row_elems = elem.XPathSelectElements("//" + row_element_name).ToArray();
int row_index = 0, col_index = 0;
foreach (XElement row_elem in row_elems)
{
sh.CreateRow(row_index);
if (row_index == 0)
{
foreach (string heading_item in heading_items)
{
sh.GetRow(row_index).CreateCell(col_index);
sh.GetRow(row_index).GetCell(col_index).SetCellValue(heading_item);
sh.AutoSizeColumn(col_index);
col_index++;
}
row_index++; col_index = 0;
sh.CreateRow(row_index);
}
for (col_index = 0; col_index < heading_items.Count(); col_index++)
{
sh.GetRow(row_index).CreateCell(col_index);
//discard blank / null values -- set "-" as default !important step
var content_value = (string.IsNullOrEmpty(row_elem.Element(heading_items[col_index]).Value) || string.IsNullOrWhiteSpace(row_elem.Element(heading_items[col_index]).Value)) ? "-" : row_elem.Element(heading_items[col_index]).Value;
sh.GetRow(row_index).GetCell(col_index).SetCellValue(content_value);
}
row_index++; col_index = 0;
}
FileStream fs = new FileStream(Path.ChangeExtension(xml_path, ".xlsx"), FileMode.Create, FileAccess.Write);
wb.Write(fs);
fs.Close();
wb.Clear();
return true;
}
}
catch (Exception ex)
{
Debug.Print(ex.ToString());
return false;
}
}
调用clsNpoi.convert_xml_to_xlsx(<xml_path>, "row");
,结果...
如需使用Java,请访问忙碌开发者指南:HSSF和XSSF功能
如需使用C#,请访问nissl-lab / npoi
希望这能帮助到某些人 :)