经常会遇见把excel数据读出来,转变成自己需要的格式,代码如下:
static void ChangeFileFormat(string strExcelPath, string newFileName, int rowOff=0,params int[] arrRmColumn)
{
DataTable dataTable = GetExcelTableByOleDB(strExcelPath);
StringBuilder strContent = new StringBuilder();
for (int i = rowOff; i < dataTable.Rows.Count; i++)
{
Object[] arrItem = dataTable.Rows[i].ItemArray;
string rowData = string.Empty;
for (int column = 0; column < arrItem.Length; column++)
{
bool bAddColumn = true;
if (arrRmColumn != null)
{
for (int rmcolumnIndex = 0; rmcolumnIndex < arrRmColumn.Length; ++rmcolumnIndex)
{
if (arrRmColumn[rmcolumnIndex] == column)
{
bAddColumn = false;
break;
}
}
}
if (bAddColumn)
{
rowData += (arrItem[column] + ">");
}
}
strContent.Append(rowData + "\n");
}
string strTargetFilePath = System.Environment.CurrentDirectory + "\\TargetFile\\" + newFileName;
if (File.Exists(strTargetFilePath))
{
File.Delete(strTargetFilePath);
}
using (StreamWriter write = new StreamWriter(strTargetFilePath))
{
write.Write(strContent.ToString());
write.Flush();
}
Console.WriteLine("change successfully!");
}
public static DataTable GetExcelTableByOleDB(string strExcelPath)
{
try
{
DataTable dtExcel = new DataTable();
//数据表
DataSet ds = new DataSet();
//获取文件扩展名
string strExtension = System.IO.Path.GetExtension(strExcelPath);
string strFileName = System.IO.Path.GetFileName(strExcelPath);
//Excel的连接
OleDbConnection objConn = null;
switch (strExtension)
{
case ".xls":
objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
break;
case ".xlsx":
objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");
break;
default:
objConn = null;
break;
}
if (objConn == null)
{
return null;
}
objConn.Open();
//获取Excel中所有Sheet表的信息
DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet表名
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
//获取Excel指定Sheet表中的信息
//OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
objConn.Close();
//dtExcel即为excel文件中指定表中存储的信息
dtExcel = ds.Tables[tableName];
return dtExcel;
}
catch
{
return null;
}
}