public static void SetSchemaFile(string dataSource, ExPropHRD exPropHRD, ExPropFMT exPropFMT, string strFormat)
{
string folderPath = Path.GetDirectoryName(dataSource);
string fileName = Path.GetFileName(dataSource);
switch (exPropFMT)
{
case ExPropFMT.Default:
if (File.Exists(folderPath + "\\Schema.ini"))
{
File.Delete(folderPath + "\\Schema.ini");
}
break;
case ExPropFMT.CSVDelimited:
case ExPropFMT.Delimited:
case ExPropFMT.FixedLength:
case ExPropFMT.TabDelimited:
FileStream fsOutput = new FileStream(folderPath + "\\Schema.ini", FileMode.Create, FileAccess.Write);
StreamWriter srOutput = new StreamWriter(fsOutput, Encoding.Default);
string s1, s2, s3, s4, s5, s6;
s1 = "[" + fileName + "]";
s2 = "ColNameHeader=" + (exPropHRD == ExPropHRD.Yes ? "true" : "false");
s3 = "Format=" + strFormat;
//s4 = "MaxScanRows=1000";
s5 = "CharacterSet=ANSI"; //OEM
s6 = "TextDelimiter='";//none
srOutput.WriteLine(s1 + "\r\n" + s2 + "\r\n" + s3 + "\r\n" + s5 + "\r\n" + s6); //"\r\n" + s4 +
srOutput.Close();
fsOutput.Close();
break;
}
}
/// <summary>
/// EXCEL or CSV 파일을 System.Data.DataSet 으로 변환
/// 텍스트 or 엑셀 or CSV 파일을 System.Data.DataSet 으로 변환
///
/// fileReadPath.ImportToDataSet(SO_COMMON_HRD.ExPropHRD.Yes, SO_COMMON_HRD.ExPropFMT.Default, "");
/// fileReadPath.ImportToDataSet(SO_COMMON_HRD.ExPropHRD.Yes, SO_COMMON_HRD.ExPropFMT.Default, "", "Sheet1", "Sheet2");
/// ImportToDataSet(fileReadPath, SO_COMMON_HRD.ExPropHRD.Yes, SO_COMMON_HRD.ExPropFMT.Default, "");
/// dataset = fileReadPath.ImportToDataSet(SO_COMMON_HRD.ExPropHRD.No, SO_COMMON_HRD.ExPropFMT.Delimited, "|");
/// </summary>
/// <param name="dataSource"></param>
/// <param name="exPropHRD"></param>
/// <param name="exPropFMT"></param>
/// <param name="strDelimited"></param>
/// <param name="argsSheetName"></param>
/// <returns></returns>
public static System.Data.DataSet ImportToDataSet(this string dataSource, ExPropHRD exPropHRD, ExPropFMT exPropFMT, string strDelimited, params object[] argsSheetName)
{
// Create the new table
DataSet dataset = new DataSet();
System.Data.DataTable exDataTable = new System.Data.DataTable();
exDataTable.Locale = System.Globalization.CultureInfo.CurrentCulture;
// Check file
if (!File.Exists(dataSource))
throw new FileNotFoundException("File not found", dataSource);
string sheetName = string.Empty;
string cmdString = string.Empty;
string connString = string.Empty;
string fileExtension = Path.GetExtension(dataSource).ToLower();
// Propertiex를 Text로 하면 CSV 파일을 읽어 올수 있고 Excel 8.0 을 지정하면 엑셀 파일을 읽은수 있다.
// HDR : 엑셀엑셀 데이터의 첫번째 row 를 columnname 으로 지정할지 하지 않을지 결정
switch (fileExtension)
{
#region 텍스트 파일 or 확장자가 없는 파일
case "":
case ".txt":
string strFMT = "";
string strFormat = "";
if (exPropFMT != ExPropFMT.Default)
{
strFormat = exPropFMT.ToString() + (exPropFMT == ExPropFMT.Delimited ? "(" + strDelimited + ")" : "");
strFMT = "FMT=" + strFormat; //Format
}
SetSchemaFile(dataSource, exPropHRD, exPropFMT, strFormat);
// 확장자가 없는 파일은 .txt 파일로 복사하여 만든다.
if (string.IsNullOrEmpty(fileExtension))
{
CopyFile(dataSource, Path.GetDirectoryName(dataSource), Path.GetFileName(dataSource) + ".txt");
}
connString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""{1};{2};{3}"""
, Path.GetDirectoryName(dataSource)
, "Text"
, "HDR=" + exPropHRD.ToString()
, strFMT);//strFMT
cmdString = string.Format("SELECT * FROM {0}", Path.GetFileName(dataSource) + (string.IsNullOrEmpty(fileExtension) == true ? ".txt" : ""));
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(cmdString, conn);
//adapter.FillSchema(dataset, SchemaType.Source);
adapter.Fill(dataset);
conn.Close();
};
break;
#endregion
#region CSV 파일
case ".csv":
connString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""{1};{2};"""
, Path.GetDirectoryName(dataSource)
, "Text"
, "HDR=" + exPropHRD.ToString());
//FileInfo fi = new FileInfo(dataSource);
//string filePath = fi.DirectoryName.ToString();
//string fileName = fi.Name.ToString();
cmdString = string.Format("SELECT * FROM {0}", Path.GetFileName(dataSource));
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(cmdString, conn);
adapter.Fill(dataset);
conn.Close();
};
break;
#endregion
#region 엑셀 파일
case ".xls":
case ".xlsx":
if (fileExtension == ".xls")
{
// 2014.09.18 수정 : IMEX=1 추가 - 첫번째 줄에 값이 없을때 해당 컬럼의 값이 NULL로 입력되는 현상 해결
connString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""{1};{2};IMEX=1"""
, dataSource
, "Excel 8.0"
, "HDR=" + exPropHRD.ToString());
}
else if (fileExtension == ".xlsx")
{
// 2014.09.18 수정 : IMEX=1 추가 - 첫번째 줄에 값이 없을때 해당 컬럼의 값이 NULL로 입력되는 현상 해결
connString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""{1};{2};IMEX=1"""
, dataSource
, "Excel 12.0"
, "HDR=" + exPropHRD.ToString());
}
using (OleDbConnection conn = new OleDbConnection(connString))
{
if (argsSheetName.Length == 0)
{
sheetName = "Sheet1$";
cmdString = string.Format("SELECT * FROM [{0}]", sheetName);
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(cmdString, conn);
adapter.Fill(dataset, sheetName);
conn.Close();
}
else
{
conn.Open();
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//string sheet = schemaRow["TABLE_NAME"].ToString();
for (int i = 0; i < argsSheetName.Length; i++)
{
Type tp = argsSheetName[i].GetType();
if (tp.FullName == "System.Int32")
{
int rowIdx = int.Parse(argsSheetName[i].ToString());
sheetName = schemaTable.Rows[rowIdx]["TABLE_NAME"].ToString();
}
else
sheetName = argsSheetName[i].ToString() + "$";
cmdString = string.Format("SELECT * FROM [{0}]", sheetName);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(cmdString, conn);
adapter.Fill(dataset, sheetName);
}
conn.Close();
}
};
break;
#endregion
default:
return null;//properties = "Excel 8.0";
}
return dataset;
}