hi,
This code is used to import excel sheet to database and some Excel sheet functions.
public static string BuildExcelToFile(DataSet ds, string xslPath)
{
try
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(ds.GetXml());
System.Xml.Xsl.XslTransform xslt = new XslTransform();
xslt.Load(xslPath);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
string _path = xslPath.Substring(0, xslPath.Length - 4);
string xlsPath = _path + ".xls";
XmlWriter writer = new XmlTextWriter(xlsPath, Encoding.UTF8);
xslt.Transform(nav, null, writer, null);
writer.Close();
return xlsPath;
}
catch (Exception ex)
{
throw ex;
}
}
public static string BuildExcel(DataSet ds, string xslPath)
{
try
{
XmlDocument doc = new XmlDocument();//Conversion on Dataset to XML
doc.LoadXml(ds.GetXml());
XslTransform xslt = new XslTransform();//Load the XML Document
xslt.Load(xslPath);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
StringWriter sw = new StringWriter();
XmlWriter writer = new XmlTextWriter(sw);
xslt.Transform(nav, null, writer, null);
string ret = sw.ToString();
writer.Close();
sw.Close();
return ret;
}
catch (Exception ex)
{
throw ex;
}
}
public static string BuildExcel(XmlDocument doc, string xslPath)
{
try
{
XslTransform xslt = new XslTransform();
xslt.Load(xslPath);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
StringWriter sw = new StringWriter();
XmlWriter writer = new XmlTextWriter(sw);
xslt.Transform(nav, null, writer, null);
string ret = sw.ToString();
writer.Close();
sw.Close();
return ret;
}
catch (Exception ex)
{
throw ex;
}
}
public static System.Data.DataSet GetDataFromExcel(string FileName)
{
DataSet dsXls = new DataSet();
//EXCEL³s±µ¦r²Å¦ê
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + FileName + ";"
+ "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
//³s±µEXCEL
OleDbConnection objConn = new OleDbConnection(strConn);
try
{
objConn.Open();
string xlsSheetName = GetXlsSheetName(objConn);
foreach (string sheetName in xlsSheetName.Split(','))
{
//·s«Ø©R¥O
OleDbCommand cmdXls = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", objConn);
//«Ø¥ß¾A°t¾¹
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = cmdXls;
//³Ð«Ø¼Æ¾Ú¶°¨Ã¶ñ¥R
objAdapter.Fill(dsXls, sheetName);
}
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{
//Ãö³¬EXCEL³s±µ
objConn.Close();
}
//ªð¦^DataTable
return dsXls;
}
private static string GetXlsSheetName(OleDbConnection xlsConn)
{
string SheetNameString = "";
DataTable FromExcel = xlsConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string TableName = FromExcel.TableName;
int RowCount = FromExcel.Rows.Count;
string SheetName = "", StrName = "";
int ItemIndex = 0;
try
{
while (ItemIndex < RowCount)
{
DataRow TempRow = FromExcel.Rows[ItemIndex];
SheetName = TempRow[2].ToString();
if (SheetName.Substring(SheetName.Length - 1, 1) == "$")
{
System.Math.Min(System.Threading.Interlocked.Increment(ref ItemIndex), ItemIndex - 1);
StrName += SheetName;
}
else
{
ItemIndex = ItemIndex + 1;
}
}
if (StrName == null)
{
xlsConn.Close();
}
SheetNameString = StrName.Replace("$", ",");
if (SheetNameString.EndsWith(","))
{
SheetNameString = SheetNameString.Substring(0, SheetNameString.Length - 1);
}
}
catch (Exception ex)
{
xlsConn.Close();
throw ex;
}
return SheetNameString;
}
}
|
No responses found. Be the first to respond and make money from revenue sharing program.
|