public void Import(ExcelFileType fileType, Stream file)
{
// FIELDS THAT CANNOT BE NULL!
// Reference
// FirstName
// LastName
// Salutation
// [Archived] - Not in template.
try
{
importer.Open(file, fileType, hasHeaderRow: true);
const int SheetIndex = 0; // TODO: Hard-coded for now
var rowsAsKeyValuePairs = importer.GetRowsAsKeyValuePairs(SheetIndex).ToList();
AddArchivedFieldToData(rowsAsKeyValuePairs); // This is a non-null field
foreach (Member member in rowsAsKeyValuePairs.Select(memberData => memberFactory.CreateMember(memberData)))
{
memberService.Insert(member);
}
InvokeImportDataCompleted();
}
catch (Exception ex)
{
// The only exception that is explicitly thrown by the underlying code is an InvalidOperationException.
// I would like to make this less catch-all, but there's probably a large number of exceptions that
// could be thrown by the import code, none of which are declared in the code itself.
InvokeImportErrorFailed(ex);
}
}
/// <summary>
/// 将内存中的DataTable转成Excel
/// </summary>
/// <param name="fileName">Excel保存路径</param>
/// <param name="sourceTable">内存中的DataTable</param>
/// <param name="excelType"></param>
/// <param name="firstRowasColumnName">firstRowasColumnName</param>
public static void WriteExcel(string fileName, DataTable sourceTable, ExcelFileType excelType, bool firstRowasColumnName)
{
string sheetName = sourceTable.TableName;
// // 必须要firstRowasColumnName=true，否则不能Insert
using (OleDbConnection conn = GetConnection(fileName, excelType, true))
{
conn.Open();
//检查表单是否已存在
string[] sheets = GetSheets(conn);
for (int i = 0; i < sheets.Length; i++)
{
if (sheets[i].Equals(sheetName, StringComparison.OrdinalIgnoreCase))
{
//如果存在，删除
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("DROP TABLE {0} ", sheetName);
cmd.ExecuteNonQuery();
}
break;
}
}
//创建表单
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("CREATE TABLE {0} ({1});", sheetName, BuildColumnsString(sourceTable));
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = conn.CreateCommand())
{
foreach (DataRow sRow in sourceTable.Rows)
{
string colName = string.Empty;
string colValue = string.Empty;
string colInsert = string.Empty;
for (int i = 0; i < sourceTable.Columns.Count; i++)
{
if (sRow[i].ToString().IndexOf('\'') > 0)
{
Table2Excel(conn, sourceTable);
return;
}
if (i > 0)
{
colName += ",";
colValue += ",";
}
colName += "[" + sourceTable.Columns[i].ColumnName.ToString() + "]";
colValue += sRow[i] == System.DBNull.Value ? "NULL" : "'" + sRow[i].ToString() + "'";
}
cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2});", sheetName, colName, colValue);
cmd.ExecuteNonQuery();
}
}
}
}
//Insert row into spreadsheet
public void UpdateExcelDocument(string ExcelFilePath, string SheetName, ExcelFileType FileType, ref OleDbConnection con, string UpdateString)
{
string ParentDirectory = ExcelFilePath.Substring(0, ExcelFilePath.LastIndexOf('\\'));
string FileName = ExcelFilePath.Split('\\').Last();
string UpdateCommand = "";
if (FileType == ExcelFileType.xlsx)
{
UpdateCommand = "Update [" + SheetName + "$] " + UpdateString;
}
else if (FileType == ExcelFileType.xls)
{
UpdateCommand = "Update [" + SheetName + "$] " + UpdateString;
}
else if (FileType == ExcelFileType.csv)
{
UpdateCommand = "Update [" + FileName + "] " + UpdateString;
}
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = UpdateCommand;
cmd.ExecuteNonQuery();
}
/// <summary>
/// 把DataTables写入Excel（xml格式和xls格式）
/// 每个DataTable一个Sheet，Sheet.Name = DataTable.TableName。
/// 类型要一致（例如DataTable是DateTime类型，Excel中列也要是时间类型）
/// </summary>
/// <param name="dts"></param>
/// <param name="fileName"></param>
/// <param name="excelType"></param>
/// <param name="firstRowasColumnName">第一列是否Column名字。</param>
public static void WriteExcel(IList <DataTable> dts, string fileName, ExcelFileType excelType, bool firstRowasColumnName)
{
switch (excelType)
{
case ExcelFileType.Xml:
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName))
{
ExcelXmlHelper.WriteExcelXmlHead(sw);
foreach (DataTable dt in dts)
{
ExcelXmlHelper.WriteExcelXmlTableHead(sw, dt.TableName);
ExcelXmlHelper.WriteExcelXmlRows(dt, sw, firstRowasColumnName);
ExcelXmlHelper.WriteExcelXmlTableTail(sw);
}
ExcelXmlHelper.WriteExcelXmlTail(sw);
}
break;
case ExcelFileType.Xls:
case ExcelFileType.Xlsx:
foreach (DataTable dt in dts)
{
Excel.OleHelper.WriteExcel(fileName, dt, excelType, firstRowasColumnName);
}
break;
}
}
public void Import(ExcelFileType fileType, Stream file)
{
// FIELDS THAT CANNOT BE NULL!
// Reference
// FirstName
// LastName
// Salutation
// [Archived] - Not in template.
try
{
importer.Open(file, fileType, hasHeaderRow: true);
const int SheetIndex = 0; // TODO: Hard-coded for now
var rowsAsKeyValuePairs = importer.GetRowsAsKeyValuePairs(SheetIndex).ToList();
AddArchivedFieldToData(rowsAsKeyValuePairs); // This is a non-null field
foreach (Member member in rowsAsKeyValuePairs.Select(memberData => memberFactory.CreateMember(memberData)))
{
memberService.Insert(member);
}
InvokeImportDataCompleted();
}
catch (Exception ex)
{
// The only exception that is explicitly thrown by the underlying code is an InvalidOperationException.
// I would like to make this less catch-all, but there's probably a large number of exceptions that
// could be thrown by the import code, none of which are declared in the code itself.
InvokeImportErrorFailed(ex);
}
}
/// <summary>
/// NPOI IWorkbook 인스턴스를 생성하여 반환한다.
/// </summary>
/// <param name="filePath">파일 확장자를 포함한 경로</param>
private static IWorkbook CreateWorkbook(string filePath, ExcelFileType type)
{
try
{
using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
if (type == ExcelFileType.xlsx)
{
workbook = new XSSFWorkbook(fs);
}
else if (type == ExcelFileType.xls)
{
workbook = new HSSFWorkbook(fs);
}
else
{
throw new NotImplementedException($"ExcelFileType: '{type}'");
}
return(workbook);
}
}
catch (Exception ex)
{
if (ex is NotImplementedException)
{
ExceptionDispatchInfo.Capture(ex).Throw();
// not reached
}
throw new FileLoadException($"파일을 읽을 수 없습니다. 관리자에게 문의 바랍니다.", ex);
}
}
//Returns the connection object
public OleDbConnection OpenExcelFile(string ExcelFilePath, string SheetName, ExcelFileType FileType, bool HasHeaderRow, bool ForceMixedDataAsText, int TypeGuessRows, string CSVDelimiter)
{
string ConnectionStringOptions = "";
DataTable sheet1 = new DataTable();
OleDbDataAdapter adapter;
DataSet ds = new DataSet();
adapter = new OleDbDataAdapter();
string ParentDirectory = ExcelFilePath.Substring(0, ExcelFilePath.LastIndexOf('\\'));
string FileName = ExcelFilePath.Split('\\').Last();
if (HasHeaderRow)
{
ConnectionStringOptions += "HDR=YES;";
}
else
{
ConnectionStringOptions += "HDR=NO;";
}
if (ForceMixedDataAsText)
{
ConnectionStringOptions += "IMEX=1;";
}
ConnectionStringOptions += "TypeGuessRows=" + TypeGuessRows + ";";
ConnectionStringOptions = ConnectionStringOptions.Substring(0, ConnectionStringOptions.Length - 1); //remove trailing ; character
OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
if (FileType == ExcelFileType.xlsx)
{
csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
csbuilder.DataSource = ExcelFilePath;
csbuilder.Add("Extended Properties", "Excel 12.0 Xml;" + ConnectionStringOptions);
}
else if (FileType == ExcelFileType.xls)
{
csbuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
csbuilder.DataSource = ExcelFilePath;
csbuilder.Add("Extended Properties", "Excel 8.0;" + ConnectionStringOptions);
}
else if (FileType == ExcelFileType.csv)
{
csbuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
csbuilder.DataSource = ParentDirectory;
csbuilder.Add("Extended Properties", "text;FMT=Delimited(" + CSVDelimiter + ");" + ConnectionStringOptions);
}
else
{
throw new System.ArgumentException("Invalid file type specified. File must end with .xls, .xlsx or .csv");
}
OleDbConnection con = new OleDbConnection(csbuilder.ConnectionString);
con.Open();
return(con);
}
public static void SaveContentTypes(XFile file, MemoryFolder mFolder, ExcelFileType workbookType)
{
CT_Types types = GetContentTypes(file, mFolder, workbookType);
if (types != null)
{
mFolder.CreateMemoryFile("[Content_Types].xml", CreateStreamFromObject(types, typeof(CT_Types)));
}
}
public void IsOpenXml_xlsExtensionUppercase_ReturnsFalse()
{
string filename = "file.XLS";
const bool expected = false;
bool result = ExcelFileType.IsOpenXml(filename);
result.Should().Be(expected);
}
///// <summary>
///// 实例化一个执行T-Sql语句的SqlCommand对象实例
///// </summary>
///// <param name="que">要执行的SQL语句</param>
///// <param name="FileName"></param>
///// <param name="FileType"></param>
///// <returns></returns>
//private static OleDbCommand BuildCommand(string que, string FileName, ExcelFileType FileType)
//{
// OleDbConnection conn = GetConnection(FileName, FileType);
// try
// {
// if (conn.State == ConnectionState.Closed)
// conn.Open();
// OleDbCommand cmd = new OleDbCommand(que, conn);
// return cmd;
// }
// catch (OleDbException EX)
// {
// throw EX;
// }
//}
///// <summary>
///// 关闭连接和CMD命令并清理资源
///// </summary>
//private static void CloseConn()
//{
// if (conn != null)
// {
// if (conn.State == ConnectionState.Open)
// {
// conn.Close();
// conn.Dispose();
// }
// }
//}
#endregion
/// <summary>
/// 执行一个T-SQL查询，返回DataTable对象
/// </summary>
/// <param name="fileName"></param>
/// <param name="fileType"></param>
/// <param name="firstRowasColumnName"></param>
/// <returns></returns>
public static IList <DataTable> ReadExcel(string fileName, ExcelFileType fileType, bool firstRowasColumnName)
{
OleDbConnection conn = GetConnection(fileName, fileType, firstRowasColumnName);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
// dt = new DataTable();
cmdExcel.Connection = conn;
//Get the name of Sheet
conn.Open();
DataTable dtExcelSchema;
dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });//(OleDbSchemaGuid.Tables, null);
IList <string> list = new List <string>();
for (int i = 0; i <= dtExcelSchema.Rows.Count - 1; i++)
{
list.Add(dtExcelSchema.Rows[i]["TABLE_NAME"].ToString());
}
// string SheetName = dtExcelSchema.Rows[1]["TABLE_NAME"].ToString();
conn.Close();
IList <DataTable> listable = new List <DataTable>();
//Read Data from Sheet
for (int i = 0; i < list.Count; ++i)
{
string sheetName = list[i];
// 当中文时，可能会有''，例如'十二月$'
sheetName = sheetName.Replace("'", "").Replace("\"", "");
if (sheetName.EndsWith("$"))
{
conn.Open();
DataTable dtb = new DataTable(sheetName.Replace("$", "").ToString());
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dtb);
listable.Add(dtb);
conn.Close();
}
}
return(listable);
}
public void IsBinary_Null_ReturnsFalse()
{
string filename = null;
const bool expected = false;
bool result = ExcelFileType.IsBinary(filename);
result.Should().Be(expected);
}
public void IsBinary_xlsExtensionUppercase_ReturnsTrue()
{
string filename = "file.XLS";
const bool expected = true;
bool result = ExcelFileType.IsBinary(filename);
result.Should().Be(expected);
}
public void IsBinary_xlsxExtensionLowercase_ReturnsFalse()
{
string filename = "file.xlsx";
const bool expected = false;
bool result = ExcelFileType.IsBinary(filename);
result.Should().Be(expected);
}
public void IsExcelFileExtension_xlsExtensionLowercase_ReturnsTrue()
{
string filename = "file.xls";
const bool expected = true;
bool result = ExcelFileType.IsExcelFileExtension(filename);
result.Should().Be(expected);
}
public void IsExcelFileExtension_NonExcelExtension_ReturnsFalse()
{
string filename = "file.txt";
const bool expected = false;
bool result = ExcelFileType.IsExcelFileExtension(filename);
result.Should().Be(expected);
}
/// <summary>
/// Exports the data supplied to an excel workbook
/// </summary>
/// <param name="rowData">The data to be exported to the workbook</param>
/// <param name="formatter"></param>
public void WriteDataToExport(IEnumerable<IEnumerable<Cell>> rowData, CellFormatter formatter, ExcelFileType excelFileType)
{
if (excelFileType == ExcelFileType.XLS)
{
ExcelExporter = new ExcelXlsExporter();
}
ExcelExporter.WriteDataToExport(rowData, formatter, "Sheet 1");
}
public void IsOpenXml_EmptyString_ReturnsFalse()
{
string filename = string.Empty;
const bool expected = false;
bool result = ExcelFileType.IsOpenXml(filename);
result.Should().Be(expected);
}
/// <summary>
/// 执行一个T-SQL查询，返回DataTable对象
/// </summary>
/// <param name="fileName"></param>
/// <param name="fileType"></param>
/// <param name="firstRowasColumnName"></param>
/// <returns></returns>
public static IList<DataTable> ReadExcel(string fileName, ExcelFileType fileType, bool firstRowasColumnName)
{
OleDbConnection conn = GetConnection(fileName, fileType, firstRowasColumnName);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
// dt = new DataTable();
cmdExcel.Connection = conn;
//Get the name of Sheet
conn.Open();
DataTable dtExcelSchema;
dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });//(OleDbSchemaGuid.Tables, null);
IList<string> list = new List<string>();
for (int i = 0; i <= dtExcelSchema.Rows.Count - 1; i++)
{
list.Add(dtExcelSchema.Rows[i]["TABLE_NAME"].ToString());
}
// string SheetName = dtExcelSchema.Rows[1]["TABLE_NAME"].ToString();
conn.Close();
IList<DataTable> listable = new List<DataTable>();
//Read Data from Sheet
for(int i=0; i<list.Count; ++i)
{
string sheetName = list[i];
// 当中文时，可能会有''，例如'十二月$'
sheetName = sheetName.Replace("'", "").Replace("\"", "");
if (sheetName.EndsWith("$"))
{
conn.Open();
DataTable dtb = new DataTable(sheetName.Replace("$","").ToString());
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dtb);
listable.Add(dtb);
conn.Close();
}
}
return listable;
}
/// <summary>
/// Saves the specified out stream.
/// </summary>
/// <param name="outStream">The out stream.</param>
/// <param name="workbookType">Type of the workbook.</param>
/// <param name="password">The password.</param>
public void Save(Stream outStream, ExcelFileType workbookType, string password)
{
if (workbookType == ExcelFileType.XLS)
{
this.SaveToCompoundStorage(outStream, password);
}
else
{
this.SaveToOffice12File(outStream, password, workbookType);
}
}
/// <summary>
/// 생성자
/// </summary>
/// <param name="filePath">엑셀 확장자를 포함한 파일 경로</param>
public ExcelParser(string filePath, ExcelFileType type)
{
filePath.ThrowIfNullOrWhiteSpace(nameof(filePath));
if (!File.Exists(filePath))
{
throw new FileNotFoundException($"Path: '{filePath}'");
}
this.FilePath = filePath;
this.FileType = type;
this._workbook = CreateWorkbook(filePath, type);
}
private void btnSelectPath_Click(object sender, EventArgs e)
{
DialogResult result = dlgSelectFile.ShowDialog();
if (result == DialogResult.OK)
{
this.txtPath.Text = dlgSelectFile.FileName;
string fileName = Path.GetFileNameWithoutExtension(this.txtPath.Text).ToLower();
string fileExt = Path.GetExtension(this.txtPath.Text).ToLower();
if (fileName.Contains("fedra"))
{
this.excelFileType = ExcelFileType.Fedra;
this.rbFedra.Checked = true;
}
else if (fileName.Contains("adecco"))
{
this.excelFileType = ExcelFileType.Adecco;
this.rbAdecco.Checked = true;
}
else if (fileName.Contains("omladinci"))
{
this.excelFileType = ExcelFileType.Youth;
this.rbYouth.Checked = true;
}
else if (fileName.Contains("unos") || fileName.Contains("rucni"))
{
this.excelFileType = ExcelFileType.ManualEntry;
this.rbAdditionalData.Checked = true;
}
else
{
this.excelFileType = ExcelFileType.Unspecified;
}
if (this.excelFileType == ExcelFileType.Unspecified)
{
MessageBox.Show("Excel fajl nije ispravan!\nExcel fajl u nazivu mora imati oznaku za tip unosa (npr. 'fedra', 'adecco', 'omladinci' ili 'rucni unos')");
ClearAll();
return;
}
if (fileExt != ".xlsx")
{
MessageBox.Show("Morate izabrati Excel fajl (nastavak '.xlsx')!");
ClearAll();
return;
}
}
}
public static string ToText(this ExcelFileType excelFileType)
{
switch (excelFileType)
{
case ExcelFileType.Xlsx:
return(Resource.PresentationExcelFileFormatXlsx);
case ExcelFileType.Xml:
return(Resource.PresentationExcelFileFormatXml);
default:
return(string.Empty);
}
}
/// <summary>
/// 创建Excel工作薄
/// </summary>
public void CreateExcel(ExcelFileType fileType = ExcelFileType.XLS)
{
if (ExcelFileType.XLS == fileType)
{
_myExcel = new HSSFWorkbook();
}
else
{
_myExcel = new XSSFWorkbook();
}
_myExcel.CreateSheet();
_myExcel.SetActiveSheet(0);
_activeSheet = _myExcel.GetSheetAt(0);
}
/// <summary>
/// 获取Connection
/// </summary>
/// <param name="FileName"></param>
/// <param name="FileType"></param>
/// <param name="firstRowasColumnName"></param>
private static OleDbConnection GetConnection(string FileName, ExcelFileType FileType, bool firstRowasColumnName)
{
string conStr = "";
switch (FileType)
{
case ExcelFileType.Xls: //Excel 97-03
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
break;
case ExcelFileType.Xlsx: //Excel 07
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1}'";
break;
}
conStr = String.Format(conStr, FileName, firstRowasColumnName ? "Yes" : "No");
return(new OleDbConnection(conStr));
}
public void Open(Stream streamToProcess, ExcelFileType fileType, bool hasHeaderRow)
{
if (streamToProcess == null) throw new ArgumentNullException("streamToProcess");
this.hasHeaderRow = hasHeaderRow;
if (fileType == ExcelFileType.XLS)
{
ExcelImporter = new ExcelXlsImporter();
}
else
{
ExcelImporter = new ExcelXlsxImporter();
}
ExcelImporter.Open(streamToProcess);
}
public static IExcelDataReader CreateReader(Stream fileStream, ExcelFileType excelFileType) {
IExcelDataReader reader=null;
switch (excelFileType) {
case ExcelFileType.Binary:
reader=new ExcelBinaryReader();
reader.Initialize(fileStream);
break;
case ExcelFileType.OpenXml:
reader=new ExcelOpenXmlReader();
reader.Initialize(fileStream);
break;
default:
break;
}
return reader;
}
private void SaveToOffice12File(Stream stream, string password, ExcelFileType workbookType)
{
if (stream != null)
{
bool flag = false;
if (((password != null) && (password.Length > 0)) && (password.Length < 0x100))
{
flag = true;
}
this._exportedExcelVersion = ExcelVersion.Excel2007;
XlsxWriter writer = new XlsxWriter(this._excelWriter, this._documentInfo, workbookType);
if (flag)
{
stream = (Stream) new MemoryStream();
}
writer.Save(stream);
}
}
/// <summary>Create a new Workbook.</summary>
/// <param name="type">The version of Excel to target.</param>
/// <returns>A facade over the specific Excel libraries.</returns>
public static ExcelFile Create(ExcelFileType type)
{
ExcelFile newFile;
switch (type)
{
case ExcelFileType.XLS:
newFile = new NpoiExcelFile();
break;
case ExcelFileType.XLSX:
newFile = new EPPlusExcelFile();
break;
default:
throw new InvalidOperationException();
}
return(newFile);
}
/// <summary>Read an Excel file into memory.</summary>
/// <param name="inputStream">The workbook to read.</param>
/// <param name="type">The version of file being read.</param>
/// <returns>A facade over the specific Excel libraries.</returns>
public static ExcelFile Read(System.IO.Stream inputStream, ExcelFileType type)
{
ExcelFile file;
switch (type)
{
case ExcelFileType.XLS:
file = new NpoiExcelFile(inputStream);
break;
case ExcelFileType.XLSX:
file = new EPPlusExcelFile(inputStream);
break;
default:
throw new InvalidOperationException();
}
return(file);
}
public static IExcelDataReader CreateReader(Stream fileStream, ExcelFileType excelFileType)
{
IExcelDataReader reader = null;
switch (excelFileType)
{
case ExcelFileType.Binary:
reader = new ExcelBinaryReader();
reader.Initialize(fileStream);
break;
case ExcelFileType.OpenXml:
reader = new ExcelOpenXmlReader();
reader.Initialize(fileStream);
break;
}
return(reader);
}
public static CT_Types GetContentTypes(XFile rootFile, MemoryFolder mFolder, ExcelFileType workbookType)
{
if (((rootFile == null) || (rootFile.RelationFiles == null)) || ((rootFile.RelationFiles.Count == 0) || (mFolder == null)))
{
return(null);
}
List <object> defaultType = GetDefaultType();
if (defaultType == null)
{
return(null);
}
if (!UpdateFileList(defaultType, rootFile, mFolder, workbookType))
{
return(null);
}
return(new CT_Types {
Items = defaultType.ToArray()
});
}
public void Open(Stream streamToProcess, ExcelFileType fileType, bool hasHeaderRow)
{
if (streamToProcess == null)
{
throw new ArgumentNullException("streamToProcess");
}
this.hasHeaderRow = hasHeaderRow;
if (fileType == ExcelFileType.XLS)
{
ExcelImporter = new ExcelXlsImporter();
}
else
{
ExcelImporter = new ExcelXlsxImporter();
}
ExcelImporter.Open(streamToProcess);
}
public static ExcelFileType GetFileType(string FilePath)
{
ExcelFileType FileType = ExcelFileType.unknown;
if (FilePath != null & FilePath.Length > 3)
{
if (FilePath.Substring(FilePath.Length - 3).ToLower() == "xls")
{
FileType = ExcelFileType.xls;
}
if (FilePath.Substring(FilePath.Length - 4).ToLower() == "xlsx")
{
FileType = ExcelFileType.xlsx;
}
if (FilePath.Substring(FilePath.Length - 3).ToLower() == "csv")
{
FileType = ExcelFileType.csv;
}
}
return(FileType);
}
private static void ProcessOpenCommand(string command)
{
string filename = command.Substring(command.IndexOf(" ") + 1);
if (!File.Exists(filename))
{
Console.WriteLine("File '{0}' doesn't exist.", filename);
Environment.Exit((int)ExitCode.FileNotFound);
}
_fileType = ExcelManager.GetFileType(filename);
if (_fileType == ExcelFileType.INVALID)
{
Console.WriteLine("File '{0}' is of an unrecognized file type.", filename);
Environment.Exit((int)ExitCode.InvalidFileType);
}
_dataFile = filename;
mgr = new ExcelManager(filename);
Console.WriteLine("\nOPEN: {0}\n", filename);
}
//Insert row into spreadsheet
public void InsertRowIntoExcel(string ExcelFilePath, string SheetName, ExcelFileType FileType, ref OleDbConnection con,
string ColName1, string ColVal1, string ColName2, string ColVal2, string ColName3, string ColVal3, string ColName4, string ColVal4,
string ColName5, string ColVal5, string ColName6, string ColVal6, string ColName7, string ColVal7, string ColName8, string ColVal8,
string ColName9, string ColVal9, string ColName10, string ColVal10, string ColName11, string ColVal11, string ColName12, string ColVal12,
string ColName13, string ColVal13, string ColName14, string ColVal14, string ColName15, string ColVal15, string ColName16, string ColVal16,
string ColName17, string ColVal17, string ColName18, string ColVal18, string ColName19, string ColVal19, string ColName20, string ColVal20)
{
string ParentDirectory = ExcelFilePath.Substring(0, ExcelFilePath.LastIndexOf('\\'));
string FileName = ExcelFilePath.Split('\\').Last();
string InsertStatement = "";
if (FileType == ExcelFileType.xlsx)
{
InsertStatement = "Insert INTO [" + SheetName + "$] ([" + ColName1 + "]";
}
else if (FileType == ExcelFileType.xls)
{
InsertStatement = "Insert INTO [" + SheetName + "$] ([" + ColName1 + "]";
}
else if (FileType == ExcelFileType.csv)
{
InsertStatement = "Insert INTO [" + FileName + "] ([" + ColName1 + "]";
}
if (ColName2 != "")
{
InsertStatement += ",[" + ColName2 + "]";
}
if (ColName3 != "")
{
InsertStatement += ",[" + ColName3 + "]";
}
if (ColName4 != "")
{
InsertStatement += ",[" + ColName4 + "]";
}
if (ColName5 != "")
{
InsertStatement += ",[" + ColName5 + "]";
}
if (ColName6 != "")
{
InsertStatement += ",[" + ColName6 + "]";
}
if (ColName7 != "")
{
InsertStatement += ",[" + ColName7 + "]";
}
if (ColName8 != "")
{
InsertStatement += ",[" + ColName8 + "]";
}
if (ColName9 != "")
{
InsertStatement += ",[" + ColName9 + "]";
}
if (ColName10 != "")
{
InsertStatement += ",[" + ColName10 + "]";
}
if (ColName11 != "")
{
InsertStatement += ",[" + ColName11 + "]";
}
if (ColName12 != "")
{
InsertStatement += ",[" + ColName12 + "]";
}
if (ColName13 != "")
{
InsertStatement += ",[" + ColName13 + "]";
}
if (ColName14 != "")
{
InsertStatement += ",[" + ColName14 + "]";
}
if (ColName15 != "")
{
InsertStatement += ",[" + ColName15 + "]";
}
if (ColName16 != "")
{
InsertStatement += ",[" + ColName16 + "]";
}
if (ColName17 != "")
{
InsertStatement += ",[" + ColName17 + "]";
}
if (ColName18 != "")
{
InsertStatement += ",[" + ColName18 + "]";
}
if (ColName19 != "")
{
InsertStatement += ",[" + ColName19 + "]";
}
if (ColName20 != "")
{
InsertStatement += ",[" + ColName20 + "]";
}
InsertStatement += ") VALUES('" + ColVal1 + "'";
if (ColName2 != "")
{
InsertStatement += ",'" + ColVal2 + "'";
}
if (ColName3 != "")
{
InsertStatement += ",'" + ColVal3 + "'";
}
if (ColName4 != "")
{
InsertStatement += ",'" + ColVal4 + "'";
}
if (ColName5 != "")
{
InsertStatement += ",'" + ColVal5 + "'";
}
if (ColName6 != "")
{
InsertStatement += ",'" + ColVal6 + "'";
}
if (ColName7 != "")
{
InsertStatement += ",'" + ColVal7 + "'";
}
if (ColName8 != "")
{
InsertStatement += ",'" + ColVal8 + "'";
}
if (ColName9 != "")
{
InsertStatement += ",'" + ColVal9 + "'";
}
if (ColName10 != "")
{
InsertStatement += ",'" + ColVal10 + "'";
}
if (ColName11 != "")
{
InsertStatement += ",'" + ColVal11 + "'";
}
if (ColName12 != "")
{
InsertStatement += ",'" + ColVal12 + "'";
}
if (ColName13 != "")
{
InsertStatement += ",'" + ColVal13 + "'";
}
if (ColName14 != "")
{
InsertStatement += ",'" + ColVal13 + "'";
}
if (ColName15 != "")
{
InsertStatement += ",'" + ColVal15 + "'";
}
if (ColName16 != "")
{
InsertStatement += ",'" + ColVal16 + "'";
}
if (ColName17 != "")
{
InsertStatement += ",'" + ColVal17 + "'";
}
if (ColName18 != "")
{
InsertStatement += ",'" + ColVal18 + "'";
}
if (ColName19 != "")
{
InsertStatement += ",'" + ColVal19 + "'";
}
if (ColName20 != "")
{
InsertStatement += ",'" + ColVal20 + "'";
}
InsertStatement += ")";
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = InsertStatement;
cmd.ExecuteNonQuery();
}
/// <summary>Create a new Workbook.</summary>
/// <param name="type">The version of Excel to target.</param>
/// <returns>A facade over the specific Excel libraries.</returns>
public static ExcelFile Create(ExcelFileType type)
{
ExcelFile newFile;
switch (type)
{
case ExcelFileType.XLS:
newFile = new NpoiExcelFile();
break;
case ExcelFileType.XLSX:
newFile = new EPPlusExcelFile();
break;
default:
throw new InvalidOperationException();
}
return newFile;
}
/// <summary>
/// 把DataTables写入Excel（xml格式和xls格式）
/// 每个DataTable一个Sheet，Sheet.Name = DataTable.TableName。
/// 类型要一致（例如DataTable是DateTime类型，Excel中列也要是时间类型）
/// </summary>
/// <param name="dts"></param>
/// <param name="fileName"></param>
/// <param name="excelType"></param>
/// <param name="firstRowasColumnName">第一列是否Column名字。</param>
public static void WriteExcel(IList<DataTable> dts, string fileName, ExcelFileType excelType, bool firstRowasColumnName)
{
switch (excelType)
{
case ExcelFileType.Xml:
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName))
{
ExcelXmlHelper.WriteExcelXmlHead(sw);
foreach (DataTable dt in dts)
{
ExcelXmlHelper.WriteExcelXmlTableHead(sw, dt.TableName);
ExcelXmlHelper.WriteExcelXmlRows(dt, sw, firstRowasColumnName);
ExcelXmlHelper.WriteExcelXmlTableTail(sw);
}
ExcelXmlHelper.WriteExcelXmlTail(sw);
}
break;
case ExcelFileType.Xls:
case ExcelFileType.Xlsx:
foreach (DataTable dt in dts)
{
Excel.OleHelper.WriteExcel(fileName, dt, excelType, firstRowasColumnName);
}
break;
}
}
/// <summary>Read an Excel file into memory.</summary>
/// <param name="inputStream">The workbook to read.</param>
/// <param name="type">The version of file being read.</param>
/// <returns>A facade over the specific Excel libraries.</returns>
public static ExcelFile Read(System.IO.Stream inputStream, ExcelFileType type)
{
ExcelFile file;
switch (type)
{
case ExcelFileType.XLS:
file = new NpoiExcelFile(inputStream);
break;
case ExcelFileType.XLSX:
file = new EPPlusExcelFile(inputStream);
break;
default:
throw new InvalidOperationException();
}
return file;
}
/// <summary>
/// 获取Connection
/// </summary>
/// <param name="FileName"></param>
/// <param name="FileType"></param>
/// <param name="firstRowasColumnName"></param>
private static OleDbConnection GetConnection(string FileName, ExcelFileType FileType, bool firstRowasColumnName)
{
string conStr = "";
switch (FileType)
{
case ExcelFileType.Xls: //Excel 97-03
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
break;
case ExcelFileType.Xlsx: //Excel 07
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1}'";
break;
}
conStr = String.Format(conStr, FileName, firstRowasColumnName ? "Yes" : "No");
return new OleDbConnection(conStr);
}
/// <summary>
/// 将内存中的DataTable转成Excel
/// </summary>
/// <param name="fileName">Excel保存路径</param>
/// <param name="sourceTable">内存中的DataTable</param>
/// <param name="excelType"></param>
/// <param name="firstRowasColumnName">firstRowasColumnName</param>
public static void WriteExcel(string fileName, DataTable sourceTable, ExcelFileType excelType, bool firstRowasColumnName)
{
string sheetName = sourceTable.TableName;
// // 必须要firstRowasColumnName=true，否则不能Insert
using (OleDbConnection conn = GetConnection(fileName, excelType, true))
{
conn.Open();
//检查表单是否已存在
string[] sheets = GetSheets(conn);
for (int i = 0; i < sheets.Length; i++)
{
if (sheets[i].Equals(sheetName, StringComparison.OrdinalIgnoreCase))
{
//如果存在，删除
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("DROP TABLE {0} ", sheetName);
cmd.ExecuteNonQuery();
}
break;
}
}
//创建表单
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("CREATE TABLE {0} ({1});", sheetName, BuildColumnsString(sourceTable));
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = conn.CreateCommand())
{
foreach (DataRow sRow in sourceTable.Rows)
{
string colName = string.Empty;
string colValue = string.Empty;
string colInsert = string.Empty;
for (int i = 0; i < sourceTable.Columns.Count; i++)
{
if (sRow[i].ToString().IndexOf('\'') > 0)
{
Table2Excel(conn, sourceTable);
return;
}
if (i > 0)
{
colName += ",";
colValue += ",";
}
colName += "[" + sourceTable.Columns[i].ColumnName.ToString() + "]";
colValue += sRow[i] == System.DBNull.Value ? "NULL" : "'" + sRow[i].ToString() + "'";
}
cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2});", sheetName, colName, colValue);
cmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// 读入Excel文件。一个Sheet一个DataTable。
/// </summary>
/// <param name="fileName"></param>
/// <param name="excelType"></param>
/// <param name="firstRowasColumnName"></param>
/// <returns></returns>
public static IList<DataTable> ReadExcel(string fileName, ExcelFileType excelType, bool firstRowasColumnName)
{
return Excel.OleHelper.ReadExcel(fileName, excelType, firstRowasColumnName);
}
