如何将Openoffice的excel连接到sql server中，而不是在asp.net中使用旧的excel连接
本文关键字：excel 连接 asp net sql server Openoffice | 更新日期: 2023-09-27 18:09:23
在我的web应用程序中，我需要将OpenOffice calc数据导入SQL server数据库，我在谷歌搜索，但我没有得到我发现一个ole-db连接，而不是，我怎么能采取open office calc数据连接。
这是ole-db连接:
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
和如何打开办公室电话连接。
更新代码:
using System.Text;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;
protected void import_Click(object sender, EventArgs e)
{
XComponentContext oStrap = uno.util.Bootstrap.bootstrap();
XMultiServiceFactory oServMan = (XMultiServiceFactory)oStrap.getServiceManager();
XComponentLoader desktop = (XComponentLoader)oServMan.createInstance("com.sun.star.frame.Desktop");
string url = @"private:factory/scalc";
PropertyValue[] loadProps = new PropertyValue[1];
XComponent document = desktop.loadComponentFromURL(url, "_blank", 0, loadProps);
XSpreadsheets oSheets = ((XSpreadsheetDocument)document).getSheets();
XIndexAccess oSheetsIA = (XIndexAccess)oSheets;
XSpreadsheet sheet = (XSpreadsheet)oSheetsIA.getByIndex(0).Value;
current = ((XText)sheet.getCellByPosition(0, iRow)).getString();
}
谁能告诉我该怎么做?
谢谢
For oledb
//Read excell
string input_Excell_fileName = @"Your Path";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + input_Excell_fileName + ";Extended Properties='"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text'""; ;
DataSet ds;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
var adapter = new OleDbDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds);
}
}
和数据库连接
string WSMSconnectionstring = @"Data Source=MHAMUDUL'WALTON;Initial Catalog=WSMS;Integrated Security=True";
//Or
// string WSMSconnectionstring = @"Data Source=MHAMUDUL'WALTON;Initial Catalog=WSMS;Persist Security Info=True;User ID=sa;Password=ssssss";
using (SqlConnection con = new SqlConnection(WSMSconnectionstring))
{
con.Open();
string query1 = String.Format(@" Select * from ServiceTimeLog where ServiceID={0} and QCReleaseStatus is null
", ServiceID);
SqlCommand cmd1 = new SqlCommand(query1, con);
SqlDataReader reader = cmd1.ExecuteReader();
if (reader.HasRows)
{
reader.Dispose();
//Update
string update = String.Format(@" update ServiceTimeLog set ServiceDate=GETDATE(), StartTime=GETDATE(),EndTime=GETDATE(),QCStartTime=GETDATE(),QCEndTime=GETDATE(),QCReleaseStatus='QCPassed',ReleaseStatus='SendToQC',TechnicianID=(select UserID from Users where UserName='{0}'),QCID=(select UserID from Users where UserName='{1}') where ServiceID={2}", TechnicainID, QCID, ServiceID);
SqlCommand updateCmd = new SqlCommand(update, con);
updateCmd.ExecuteNonQuery();
}
else
{
reader.Dispose();
//Insert
string insert = String.Format(@"insert into ServiceTimeLog(ServiceID,ReleaseStatus,ServiceDate,StartTime,EndTime,QCStartTime,QCEndTime,QCReleaseStatus,TechnicianID,QCID) values({0},'SendToQC',GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),'QCPassed',(select UserID from Users where UserName='{1}'),(select UserID from Users where UserName='{2}'))", ServiceID, TechnicainID, QCID);
SqlCommand insertCmd = new SqlCommand(insert, con);
insertCmd.ExecuteNonQuery();
}
cmd1.Dispose();
reader.Dispose();
}
我最好的excel转换工具是LinqToExcel
创建一个模型，比如
public class ExcelData
{
[ExcelColumn("IMEI1")]
public String IMEI1 { get; set; }
[ExcelColumn("IMEI2")]
public String IMEI2 { get; set; }
[ExcelColumn("COLOR")]
public String COLOR { get; set; }
}
和一行代码
var excel = new ExcelQueryFactory();
List<ExcelData> allListData = new List<ExcelData>();
try
{
excel = new LinqToExcel.ExcelQueryFactory(@"C:'Users'Mhamudul Hasan'Desktop'FulllData.xlsx");
allListData = (from c in excel.Worksheet<ExcelData>("Sheet1")
select c).ToList();
}
catch (Exception exception)
{
return;
}
List<ExcelFullData> entities = new List<ExcelFullData>();
using (var ctx = new testEntities())
{
foreach (var data in allListData)
{
ExcelFullData excel1 = new ExcelFullData
{
IMEI1 = data.IMEI1,
IMEI2 = data.IMEI2,
Color = data.COLOR
};
//entities.Add(excel1);
ctx.ExcelFullDatas.Add(excel1);
}
ctx.SaveChanges();
}
Openoffice给它一个尝试:)