namespace ExcelToXml
{
class Program
{
[STAThread]
static void Main(
string[] args)
{
Program program =
new Program();
DataSet dataSet=
program.getData();
Program.ConvertDataSetToXMLFile(dataSet,"D:\\"+dataSet.DataSetName+
".xml");
}
public DataSet getData()
{
//打开文件
OpenFileDialog file =
new OpenFileDialog();
file.Filter =
"Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
file.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
file.Multiselect =
false;
if (file.ShowDialog() ==
DialogResult.Cancel)
return null;
//判断文件后缀
var path =
file.FileName;
string name =
Path.GetFileNameWithoutExtension(path);
string fileSuffix =
System.IO.Path.GetExtension(path);
if (
string.IsNullOrEmpty(fileSuffix))
return null;
////加载Excel
//Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //获取权限
//Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
//Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(path);
//Microsoft.Office.Interop.Excel.Sheets sheet = workbook.Sheets;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);//获取sheet (1)为第一个sheet
//double usedRows = app.WorksheetFunction.CountA(worksheet.Columns[3]); //第3列的行数
//string num = usedRows.ToString();
//object[,] twoDoubleList = worksheet.Range["A1:AH" + num].Value2; //获取数组
using (DataSet ds =
new DataSet())
{
//判断Excel文件是2003版本还是2007版本
string connString =
"";
//server=.;database=ExcelToXml;integrated security=SSPI
if (fileSuffix ==
".xls")
connString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + path +
";" +
";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connString =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + path +
";" +
";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
//读取文件
string sql_select =
" SELECT * FROM [Sheet1$]";
using (OleDbConnection conn =
new OleDbConnection(connString))
using (OleDbDataAdapter cmd =
new OleDbDataAdapter(sql_select, conn))
{
conn.Open();
cmd.Fill(ds);
ds.DataSetName =
name;
}
if (ds ==
null || ds.Tables.Count <=
0)
return null;
return ds;
}
}
public static void ConvertDataSetToXMLFile(DataSet xmlDS,
string xmlFile)
{
MemoryStream stream =
null;
XmlTextWriter writer =
null;
try
{
stream =
new MemoryStream();
//从stream装载到XmlTextReader
writer =
new XmlTextWriter(stream, Encoding.Unicode);
//用WriteXml方法写入文件.
xmlDS.WriteXml(writer);
int count = (
int)stream.Length;
byte[] arr =
new byte[count];
stream.Seek(0, SeekOrigin.Begin);
stream.Read(arr, 0, count);
//返回Unicode编码的文本
UnicodeEncoding utf =
new UnicodeEncoding();
StreamWriter sw =
new StreamWriter(xmlFile);
sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
sw.WriteLine(utf.GetString(arr).Trim());
sw.Close();
}
catch (System.Exception ex)
{
//throw ex;
MessageBox.Show(ex.Message);
//Console.WriteLine(ex.Message);
//Console.ReadLine();
}
finally
{
if (writer !=
null) writer.Close();
}
}
}
}
这个是转换工具的初代版本。
工具下载：https://pan.baidu.com/s/1KCA5E367g26GIvhJNVJKxw
转载于:https://www.cnblogs.com/qmz-blog/p/11511125.html
