I need this code C# working for files excel 2003 and 2007 version.
我需要这个代码c#用于文件excel 2003和2007版本。
I can't get this C# code working to convert excel file (xls) on csv file.
我无法让这个c#代码在csv文件上转换excel文件(xls)。
If try with excel file extension xlsx
it's all ok but if try with extension xls
I have error in this line:
如果尝试使用excel文件扩展名xlsx,没问题,但是如果尝试使用扩展名xls,我在这一行有错误:
result.Tables[0].TableName.ToString();
My code below, what's wrong?
下面的代码,怎么了?
code-behind
后台代码
FileUploadControl.SaveAs(Server.MapPath("/public/") + filename);
System.IO.FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
Excel.IExcelDataReader excelReader = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
result.Tables[0].TableName.ToString();
string csvData = "";
int row_no = 0;
int ind = 0;
while (row_no < result.Tables[ind].Rows.Count)
{
for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
{
csvData += result.Tables[ind].Rows[row_no][i].ToString() + ",";
}
row_no++;
csvData += "\n";
}
keys = GetUniqueKey(8).ToUpper();
output = System.Web.HttpContext.Current.Server.MapPath("/public/target_" + keys.ToString() + ".csv");
StreamWriter csv = new StreamWriter(@output, false);
csv.Write(csvData);
csv.Close();
4 个解决方案
#1
1
Excel can be of two types:
Excel有两种类型:
- Binary - Excel 2003 and older -
xls
- 二进制- Excel 2003和旧- xls
- Zip - based on Open Office XML standards - Excel 2007 onwards -
xlsx
- Zip -基于Open Office XML标准- Excel 2007开始- xlsx
You should try to use following for older excel format files:
你应该尝试使用以下的旧excel格式文件:
ExcelReaderFactory.CreateBinaryReader(stream);
#2
0
Use any Xls to Xslx conversation tool. You can try Aspose libraries. I think it's licensed but you can try trail version.
使用任何Xls到Xslx对话工具。您可以尝试使用Aspose库。我认为它是有执照的,但你可以尝试试用版本。
You can do all other conversions as well using these libraries.
您还可以使用这些库进行所有其他的转换。
#3
0
Here's how I do it - OLEDB - get the first sheet name, and remove all empty rows. Replace //whatever you need to do with your logic.
下面是我的方法—OLEDB—获取第一个表名,并删除所有空行。用你的逻辑替换//任何你需要做的。
//Your Method signature
{
//create connection string
var connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
//process
using (var conn = new OleDbConnection(connStr))
{
conn.Open();
//programatically get the first sheet, whatever it is named.
var sheetName = GetSheetNames(conn)[0].SheetNameOf;
var adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", sheetName), connStr);
var ds = new DataSet();
adapter.Fill(ds, "anyNameHere");
var data = ds.Tables["anyNameHere"];
//copy and remove blank lines
var resData = data.Clone();
var filteredData = data.Rows.Cast<DataRow>().Where(
row => !row.ItemArray.All(
field => field is DBNull ||
field == null ||
(String.IsNullOrEmpty(field.ToString().Trim())))
);
filteredData.CopyToDataTable(resData, LoadOption.OverwriteChanges);
var newData = resData.AsEnumerable();
//whatever you need to do
}
public List<SheetName> GetSheetNames(OleDbConnection conn)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DataTable excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetNames = (from DataRow row in excelSchema.Rows
where !row["TABLE_NAME"].ToString().Contains("FilterDatabase")
select new SheetName { SheetNameOf = row["TABLE_NAME"].ToString() }
).ToList();
conn.Close();
return sheetNames;
}
#4
0
You can use Aspose.Cells to convert excel file like xls or xlsx into csv format with the following simple code.
您可以使用Aspose。使用以下简单代码将xls或xlsx之类的excel文件转换为csv格式的单元格。
string filePath = @"F:\Downloads\source.xlsx";
Workbook workbook = new Workbook(filePath);
workbook.Save("output.csv", SaveFormat.CSV);
Note: I am working as developer evangelist at Aspose.
注意:我在Aspose中担任开发专员。
#1
1
Excel can be of two types:
Excel有两种类型:
- Binary - Excel 2003 and older -
xls
- 二进制- Excel 2003和旧- xls
- Zip - based on Open Office XML standards - Excel 2007 onwards -
xlsx
- Zip -基于Open Office XML标准- Excel 2007开始- xlsx
You should try to use following for older excel format files:
你应该尝试使用以下的旧excel格式文件:
ExcelReaderFactory.CreateBinaryReader(stream);
#2
0
Use any Xls to Xslx conversation tool. You can try Aspose libraries. I think it's licensed but you can try trail version.
使用任何Xls到Xslx对话工具。您可以尝试使用Aspose库。我认为它是有执照的,但你可以尝试试用版本。
You can do all other conversions as well using these libraries.
您还可以使用这些库进行所有其他的转换。
#3
0
Here's how I do it - OLEDB - get the first sheet name, and remove all empty rows. Replace //whatever you need to do with your logic.
下面是我的方法—OLEDB—获取第一个表名,并删除所有空行。用你的逻辑替换//任何你需要做的。
//Your Method signature
{
//create connection string
var connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
//process
using (var conn = new OleDbConnection(connStr))
{
conn.Open();
//programatically get the first sheet, whatever it is named.
var sheetName = GetSheetNames(conn)[0].SheetNameOf;
var adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", sheetName), connStr);
var ds = new DataSet();
adapter.Fill(ds, "anyNameHere");
var data = ds.Tables["anyNameHere"];
//copy and remove blank lines
var resData = data.Clone();
var filteredData = data.Rows.Cast<DataRow>().Where(
row => !row.ItemArray.All(
field => field is DBNull ||
field == null ||
(String.IsNullOrEmpty(field.ToString().Trim())))
);
filteredData.CopyToDataTable(resData, LoadOption.OverwriteChanges);
var newData = resData.AsEnumerable();
//whatever you need to do
}
public List<SheetName> GetSheetNames(OleDbConnection conn)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DataTable excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetNames = (from DataRow row in excelSchema.Rows
where !row["TABLE_NAME"].ToString().Contains("FilterDatabase")
select new SheetName { SheetNameOf = row["TABLE_NAME"].ToString() }
).ToList();
conn.Close();
return sheetNames;
}
#4
0
You can use Aspose.Cells to convert excel file like xls or xlsx into csv format with the following simple code.
您可以使用Aspose。使用以下简单代码将xls或xlsx之类的excel文件转换为csv格式的单元格。
string filePath = @"F:\Downloads\source.xlsx";
Workbook workbook = new Workbook(filePath);
workbook.Save("output.csv", SaveFormat.CSV);
Note: I am working as developer evangelist at Aspose.
注意:我在Aspose中担任开发专员。