static private SQLiteData ToSQLiteData(Excel.Worksheet workSheet)
{
Excel.Range range = workSheet.UsedRange;
Excel.Range rows = range.Rows;
Excel.Range columns = range.Columns;
SQLiteData dataSet = SQLiteData.Create(workSheet.Name, workSheet.UsedRange.Value2);
Program.Release(workSheet);
return(dataSet);
}
static public void Insert(SQLiteConnection connection, SQLiteData dataSet, Action <float> percent = null)
{
string defaultCommand = String.Format(Constant._COMMAND_INSERT_INTO, dataSet.SheetName, dataSet.GetColumns());
StringBuilder command = null;
int rowLength = dataSet.Datas.GetLength(0);
int columnLength = dataSet.Columns.Length;
Command(connection, Constant._BEGIN);
for (int j = Constant._ROW_START_INDEX; j <= rowLength; j++)
{
command = new StringBuilder(defaultCommand);
for (int i = 1; i <= columnLength; i++)
{
if (1 != i)
{
command.Append("\', ");
}
command.Append("\'");
dynamic data = dataSet.Datas[j, i];
if (null != data)
{
if (data.GetType() == typeof(string))
{
string stringData = data as string;
stringData = stringData.Replace("\'", "\'\'");
data = stringData;
}
command.Append(data);
}
}
command.Append("\')");
Command(connection, command.ToString());
float lowLength = 0.0f != (float)(rowLength - Constant._ROW_START_INDEX) ? (float)(rowLength - Constant._ROW_START_INDEX) : 1;
percent?.Invoke((j - Constant._ROW_START_INDEX) / lowLength);
}
Command(connection, Constant._COMMIT);
}
public SQLiteData[] Read(string filePath)
{
List <SQLiteData> result = new List <SQLiteData>();
if (!System.IO.File.Exists(filePath))
{
Debug.LogError("{0} 파일 찾기 실패", filePath);
return(null);
}
Excel.Workbook workBook = null;
try
{
workBook = _application.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
if (null != workBook)
{
// UsedRange.Value2로 가져온 배열의 인덱스가 1부터 시작
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
SQLiteData data = ToSQLiteData(workBook.Worksheets.get_Item(i));
if (null != data)
{
result.Add(data);
}
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
Program.Release(workBook);
}
return(result.ToArray());
}
static public void CreateTable(SQLiteConnection connection, SQLiteData dataSet)
{
string command = String.Format(Constant._COMMAND_CREATE_TABLE, dataSet.SheetName, dataSet.GetColumnAndTypes());
Command(connection, command);
}