Transaction GetTransaction(int rowNumber)
{
IExcelSheet sheet = _app.GetActiveWorkbook().GetActiveSheet();
ExcelSheetData data = sheet.GetSheetData(1, rowNumber);
if (data.Rows.Count < 2)
{
return(null);
}
ExcelRowData headerRowData = data.Rows[0];
ExcelRowData valueRowData = data.Rows[1];
string separator = ";";
string header = headerRowData.GetValuesInLine(separator);
string values = valueRowData.GetValuesInLine(separator);
TransactionParser parser = new TransactionParser(header, separator.FirstOrDefault());
Transaction transaction = parser.ParseFromLine(values);
return(transaction);
}
public void LoadFromFile(string fileName)
{
var stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
var reader = ExcelReaderFactory.CreateReader(stream);
for (int i = 0; i < reader.ResultsCount; i++)
{
var sheet = new ExcelSheetData();
LogUtils.instance.AddLog("读取页签:" + reader.Name);
sheet.ReadFromExcel(reader.Name, reader);
if (sheet.isNeedExprot)
{
excelSheets.Add(sheet);
}
else
{
LogUtils.instance.AddLog("页签无需导出 , 跳过 :" + reader.Name);
}
reader.NextResult();
}
stream.Close();
}
protected override void ExportTiny(ExcelSheetData data, StreamWriter writer)
{
var fieldDataKey = data.filedList[0];
var fieldDataValue = data.filedList[1];
FieldData typeField = null;
if (data.filedList.Count > 2)
{
typeField = data.filedList[2];
}
for (var rowCount = 0; rowCount < fieldDataKey.dataList.Count; rowCount++)
{
var ttype = "";
if ((typeField != null) && (typeField.dataList[rowCount] != ""))
{
ttype = typeField.dataList[rowCount];
}
string str = "";
switch (ttype)
{
case "int":
{
str = string.Format("{0}={1}", fieldDataKey.dataList[rowCount], fieldDataValue.dataList[rowCount]);
}
break;
case "int_array":
{
var values = fieldDataValue.dataList[rowCount].Split(',');
var arrayValues = "{";
foreach (var v in values)
{
arrayValues += v + ",";
}
arrayValues = arrayValues.Substring(0, arrayValues.Length - 1) + "}";
str = string.Format("{0}={1}", fieldDataKey.dataList[rowCount], arrayValues);
}
break;
default: //默认当做string 处理
{
str = string.Format("{0}:\"{1}\"", fieldDataKey.dataList[rowCount], fieldDataValue.dataList[rowCount]);
}
break;
}
if (rowCount != fieldDataKey.dataList.Count - 1)
{
str += ",";
}
writer.WriteLine(str);
}
}
private void BuildSheet(ExcelSheetData sheet)
{
string outSchemaPath = FlatBufferToolConfigure.Configure.SchemaDir;
string outJsonPath = FlatBufferToolConfigure.Configure.JsonDir;
string outClassPath = FlatBufferToolConfigure.Configure.ClassDir;
string outBinPath = FlatBufferToolConfigure.Configure.BinDir;
TableFileGenerater.BuildSheet(sheet, FlatBufferToolConfigure.Configure.FlactcPath, outSchemaPath, outJsonPath, outClassPath, outBinPath);
}
public void InsertPivotTableToSheet(int year, int month)
{
_app.Connect();
IExcelWorkbook workbook = _app.GetActiveWorkbook();
IExcelSheet sheet = workbook.GetActiveSheet();
ExcelSheetData sheetData = sheet.GetSheetData();
Transaction transaction = new Transaction();
int firstCol = Transaction.GetIndex(() => transaction.Owner);
int lastCol = Transaction.GetIndex(() => transaction.Amount);
int numberOfRows = sheetData.Rows.Count;
ExcelRangeInfo dataRange = new ExcelRangeInfo(1, firstCol, numberOfRows, lastCol + 1);
ExcelRangeInfo insertRange = new ExcelRangeInfo(numberOfRows + 2, firstCol);
string[] rows =
{
transaction.GetName(() => transaction.TransactionType),
transaction.GetName(() => transaction.Category),
transaction.GetName(() => transaction.SubCategory)
};
string[] columns =
{
transaction.GetName(() => transaction.Owner)
};
string[] values =
{
transaction.GetName(() => transaction.Amount)
};
PivotTableData data = new PivotTableData
{
Range = dataRange,
InsertCell = insertRange,
Columns = columns.ToList(),
Rows = rows.ToList(),
Values = values.ToList(),
Name = "Test01",
StyleName = "PivotStyleMedium2"
};
sheet.InsertPivotTable(data);
}
public FileData GetTransactionData()
{
List <string> lines = new List <string>();
ExcelSheetData sheetData = _sheet.GetSheetData();
foreach (var row in sheetData.Rows)
{
string line = string.Join(_separator, row.Values);
lines.Add(line);
}
return(new FileData()
{
Lines = lines, Name = _sheet.Name, Separator = _separator
});
}
public static void BuildSheet(ExcelSheetData sheet, string flatcPath, string outSchemaPath, string outJsonPath, string outClassPath, string outBinPath)
{
FbsFile fbsFile = GenFbsFileObject(sheet);
string schemaPath = string.Empty;
string jsonPath = string.Empty;
TableFileGenerater.GenFbsSchemaFile(fbsFile, outSchemaPath, ref schemaPath);
TableFileGenerater.GenJsonFile(fbsFile, sheet.fieldValues, outJsonPath, ref jsonPath);
if (!Directory.Exists(outClassPath))
{
Directory.CreateDirectory(outClassPath);
}
if (!Directory.Exists(outBinPath))
{
Directory.CreateDirectory(outBinPath);
}
CmdHelper.RunFlatC(flatcPath, schemaPath, jsonPath, outClassPath, outBinPath);
}
public void SaveToFile(ExcelSheetData data, string fileName)
{
CheckCreateDir(fileName);
var stream = new FileStream(fileName, FileMode.Create);
var writer = new StreamWriter(stream);
AddHeader(data, writer);
if (data.exportSchema == "base")
{
ExportBase(data, writer);
}
else if (data.exportSchema == "tiny")
{
ExportTiny(data, writer);
}
AddEnd(data, writer);
writer.Close();
stream.Close();
}
private void UpdateSingleReport(IExcelWorkbook workBook, TransactionReport report)
{
ExcelSheetData sheetData = new ExcelSheetData();
List <string> headers = Transaction.GetTransactionHeaders().ToList();
ColorConverter converter = new ColorConverter();
int col_LAST = COL_FIRST + headers.Count - 1;
Transaction template = new Transaction();
int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => template.Category));
int col_SUB_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => template.SubCategory));
int iTransactionRow = 2;
foreach (var transaction in report.Transactions)
{
ExcelCellData categoryData = new ExcelCellData(iTransactionRow, col_CATEGORY);
categoryData.Value = transaction.Category;
sheetData.Cells.Add(categoryData);
ExcelCellData subCategoryData = new ExcelCellData(iTransactionRow, col_SUB_CATEGORY);
subCategoryData.Value = transaction.SubCategory;
sheetData.Cells.Add(subCategoryData);
Color color = Color.White;
if (transaction.Color.IsNotNull())
{
color = (Color)converter.ConvertFromString(transaction.Color);
}
ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST);
ExcelFormatData colorFormat = new ExcelFormatData(iRange);
colorFormat.Background = color;
sheetData.Formats.Add(colorFormat);
iTransactionRow++;
}
IExcelSheet sheet = workBook.GetSheet(report.Name);
sheet.InsertSheetData(sheetData);
}
public static FbsFile GenFbsFileObject(ExcelSheetData excelSheetData)
{
//一个sheet 一个table
FbsFile fbsFile = new FbsFile
{
fileName = excelSheetData.sheetName.Replace("=", "")
};
FbsTable fbsTable = new FbsTable
{
tableName = fbsFile.fileName
};
//处理table字段
for (int j = 0; j < excelSheetData.fieldNames.Count; j++)
{
FbsTableField fbsTableField = new FbsTableField();
fbsTableField.fieldName = excelSheetData.fieldNames[j];
string dataTypeStr = excelSheetData.fieldTypes[j];
TableFileGenerater.GetFbsDataTypeByString(dataTypeStr, ref fbsTableField.fieldType, ref fbsTableField.fieldTypeName, ref fbsTableField.dataType, ref fbsTableField.isArray);
fbsTable.fields.Add(fbsTableField);
}
fbsFile.tables.Add(fbsTable);
//添加root_type table
FbsTable fbsRootTable = new FbsTable();
fbsRootTable.tableName = "Root_" + fbsFile.fileName;
FbsTableField dataField = new FbsTableField();
dataField.fieldName = "data";
dataField.fieldType = FbsFieldType.TABLE;
dataField.isArray = true;
dataField.fieldTypeName = fbsTable.tableName;
fbsRootTable.fields.Add(dataField);
fbsFile.tables.Add(fbsRootTable);
//root_type
fbsFile.root_type = fbsRootTable.tableName;
fbsFile.namespaceName = "GameDataTables";
return(fbsFile);
}
//读取excel 依赖于ExcelDataReader
public static void ReadExcel(string excelFilePath, ref List <ExcelSheetData> excelSheetDatas)
{
using (var stream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
{
do
{
string tableName = reader.Name;
tableName = tableName.Trim();
int isValid = tableName.IndexOf("=");
if (isValid < 0)
{
continue;
}
if (reader.RowCount < 2)
{
continue;
}
ExcelSheetData excelSheetData = new ExcelSheetData();
List <string> fieldNames = excelSheetData.fieldNames;
List <string> fieldTypes = excelSheetData.fieldTypes;
List <string> fieldValues = excelSheetData.fieldValues;
List <int> fieldMaxSize = excelSheetData.fieldMaxSize;
excelSheetData.sheetName = tableName;
List <int> removeCols = new List <int>();
for (int row = 0; row < reader.RowCount; row++)
{
reader.Read();
for (int col = 0; col < reader.FieldCount; col++)
{
string str = ("" + reader.GetValue(col)).Trim();
//前两行是字段定义
if (row == FlatBufferToolConfigure.Configure.fieldNameRow)
{
if (str == "" || str == string.Empty)
{
if (!removeCols.Contains(col))
{
removeCols.Add(col);
}
}
else
{
fieldNames.Add(str);
fieldMaxSize.Add(0);
}
}
else
if (row == FlatBufferToolConfigure.Configure.fieldTypeRow)
{
if (!removeCols.Contains(col))
{
fieldTypes.Add(str);
}
}
else
if (row >= FlatBufferToolConfigure.Configure.fieldValueRow)
{
if (!removeCols.Contains(col))
{
fieldValues.Add(str);
}
}
int reduce = 0;
for (reduce = 0; reduce < removeCols.Count; reduce++)
{
if (col <= removeCols[reduce])
{
break;
}
}
string[] splits = str.Split('\n');
foreach (string s in splits)
{
if (!removeCols.Contains(col))
{
if (fieldMaxSize[col - reduce] < s.Length)
{
fieldMaxSize[col - reduce] = s.Length;
}
}
;
}
}
}
excelSheetDatas.Add(excelSheetData);
} while (reader.NextResult());
}
}
}
public void GenerateSheets()
{
int YEAR = 2017;
object[] headers =
{
@"Project/Activity", "Feature", "Employee", "Comment", "Business Line (BL)",
"Time(date)", "Location", "Work Units"
};
string PATH = @"c:\Maciek\Timesheets\2017";
int COL_FIRST = 1;
int COL_LAST = 8;
int ROW_FIRST = 1;
int ROW_DATA_FIRST = 2;
ExcelApp excel = new ExcelApp();
excel.CreateNewInstance();
for (int m = 12; m >= 1; m--)
{
IExcelWorkbook workbook = excel.CreateAndActivateNewWorkbook();
string workBookName = $"Protokol_MSzczudlo_{YEAR}_{m:D2}.xlsx";
string workBookPath = Path.Combine(PATH, workBookName);
int daysInMonth = DateTime.DaysInMonth(YEAR, m);
int ROW_LAST = daysInMonth + 1;
int ROW_SUM = ROW_LAST + 1;
ExcelSheetData sheetData = new ExcelSheetData();
ExcelRowData headerRowData = new ExcelRowData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowData.Values = headers.ToList();
sheetData.Rows.Add(headerRowData);
ExcelFormatData headerRowFormat = new ExcelFormatData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowFormat.Background = Color.LightGray;
headerRowFormat.IsFontBold = true;
headerRowFormat.HorizontalAlignment = ExcelHorizontalAlignmentType.Center;
sheetData.Formats.Add(headerRowFormat);
for (int d = 1; d <= daysInMonth; d++)
{
object[] values = new object[8];
int row = d + 1;
DateTime iDay = new DateTime(YEAR, m, d);
values[5] = iDay.ToShortDateString();
bool isFreeDay = iDay.DayOfWeek == DayOfWeek.Saturday || iDay.DayOfWeek == DayOfWeek.Sunday;
if (isFreeDay)
{
ExcelFormatData iFormatData = new ExcelFormatData(COL_FIRST, row, COL_LAST, row);
iFormatData.Background = Color.Salmon;
sheetData.Formats.Add(iFormatData);
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
else
{
values[0] = "IS Treasury";
values[2] = "Maciej Szczudło";
values[7] = 8;
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
ExcelBorderData borderData = new ExcelBorderData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_LAST);
borderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
borderData.Borders.AddBorder(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderData);
ExcelFormatData fontFormatData = new ExcelFormatData(borderData.Range);
fontFormatData.FontSize = 10;
sheetData.Formats.Add(fontFormatData);
}
string monthName = DateTimeTools.GetMonthName(m);
string sheetName = $"{monthName} {YEAR}";
IExcelSheet newSheet = workbook.CreateSheet(sheetName);
ExcelRangeInfo sumRange = new ExcelRangeInfo(ROW_DATA_FIRST, COL_LAST, ROW_LAST, COL_LAST);
ExcelFormula formula = ExcelFormulaHelper.GetSum(ROW_SUM, COL_LAST, sumRange);
sheetData.Formulas.Add(formula);
ExcelBorderData borderSumData = new ExcelBorderData(formula.Range);
borderSumData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderSumData);
newSheet.InsertSheetData(sheetData);
newSheet.SetColumnsAutoFit(COL_FIRST, COL_LAST);
newSheet.SetCellName(ROW_SUM, COL_LAST, "godziny");
excel.SetDisplayGridlines(false);
workbook.Save(workBookPath);
}
}
protected override void AddHeader(ExcelSheetData data, StreamWriter writer)
{
writer.WriteLine("{");
}
private void InsertSingleReport(IExcelWorkbook workBook, TransactionReport report, IComparer <Transaction> transactionComparer)
{
ExcelSheetData sheetData = new ExcelSheetData();
TransactionManager transactionManager = new TransactionManager();
ColorConverter converter = new ColorConverter();
var firstTransaction = report.Transactions.First();
List <string> headers = Transaction.GetTransactionHeaders().ToList();
sheetData.Rows.Add(new ExcelRowData(COL_FIRST, ROW_HEADERS, headers.ToArray()));
int col_LAST = COL_FIRST + headers.Count - 1;
int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Category));
int col_AMOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Amount));
int col_ACCOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.AccountNumber));
int col_TRANSACTION_NUMBER = COL_FIRST +
headers.IndexOf(
ReflectionTools.GetName(() => firstTransaction.TransactionNumber));
int col_TRANSACTION_COMMENT = COL_FIRST + headers.IndexOf(
ReflectionTools.GetName(() => firstTransaction.Comment));
int iTransactionRow = 1;
foreach (var transaction in report.Transactions)
{
iTransactionRow++;
object[] values = ReflectionTools
.GetPropertyValues(transaction, headers).ToArray();
sheetData.Rows.Add(new ExcelRowData(COL_FIRST, iTransactionRow, values));
if (transaction.Color.IsNotNull())
{
ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST);
ExcelFormatData colorFormat = new ExcelFormatData(iRange);
colorFormat.Background = (Color)converter.ConvertFromString(transaction.Color);
sheetData.Formats.Add(colorFormat);
}
}
int row_LAST_TRANSACTION = ROW_FIRST_TRANSACTION + report.Transactions.Count - 1;
int row_SUBTOTAL = row_LAST_TRANSACTION + 1;
int row_SummaryTable = row_SUBTOTAL + 2;
iTransactionRow = row_SummaryTable;
Tree <string> categoryTree = GetCategoryTree(report.Transactions);
foreach (var categoryItem in categoryTree.Children)
{
string category = categoryItem.Value;
var subCategories = categoryItem.Children.Select(i => i.Value);
foreach (string subCategory in subCategories)
{
ExcelCellData categoryCell = new ExcelCellData(iTransactionRow, col_CATEGORY);
categoryCell.Value = category;
ExcelCellData subCategoryCell = new ExcelCellData(iTransactionRow, COLUMN_SUB_CATEGORY);
subCategoryCell.Value = subCategory;
}
}
ExcelRangeInfo transactionsRange =
new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_CATEGORY, row_LAST_TRANSACTION, col_AMOUNT);
ExcelRangeInfo subTotalRange = new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_AMOUNT, row_LAST_TRANSACTION, col_AMOUNT);
ExcelFormula subTotalFormula = ExcelFormulaHelper.GetSubTotalSum(row_SUBTOTAL, col_AMOUNT, subTotalRange);
sheetData.Formulas.Add(subTotalFormula);
ExcelBorderData subTotalBorderData = new ExcelBorderData(subTotalFormula.Range);
subTotalBorderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(subTotalBorderData);
ExcelBorderData borderTable = new ExcelBorderData(COL_FIRST, ROW_HEADERS, col_LAST, row_LAST_TRANSACTION);
borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlHairline, ExcelLineStyle.xlContinuous));
borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous));
sheetData.Borders.Add(borderTable);
ExcelRangeInfo columnAccountRange = ExcelRangeInfo.CreateColumnRange(col_ACCOUNT);
ExcelFormatData columnAccountFormat = new ExcelFormatData(columnAccountRange);
columnAccountFormat.NumberFormat = "@";
sheetData.Formats.Add(columnAccountFormat);
ExcelRangeInfo columnTransactionNumber = ExcelRangeInfo.CreateColumnRange(col_TRANSACTION_NUMBER);
ExcelFormatData columnTransactionFormat = new ExcelFormatData(columnTransactionNumber);
columnTransactionFormat.NumberFormat = "0";
sheetData.Formats.Add(columnTransactionFormat);
IExcelSheet sheet = workBook.CreateSheet(report.Name);
sheet.InsertSheetData(sheetData);
int columnCount = headers.Count;
sheet.SetColumnsAutoFit(1, columnCount);
sheet.SetAutoFilter(ROW_HEADERS, COL_FIRST, ROW_HEADERS, col_LAST);
}
protected override void AddEnd(ExcelSheetData data, StreamWriter writer)
{
writer.WriteLine("}");
}
protected override void ExportBase(ExcelSheetData data, StreamWriter writer)
{
FieldData fieldData = null;
var rowCount = data.filedList[0].RowCount;
for (var rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
//处理keyCount
string key = "";
for (var i = 0; i < data.keyCount; i++)
{
key += string.Format("[{0}] = ", data.filedList[i].dataList[rowIndex]) + "{";
}
writer.WriteLine(key);
for (var i = 0; i < data.filedList.Count; i++)
{
fieldData = data.filedList[i];
//数组列表
if (fieldData.objType == FieldObjectType.ARRAY)
{
string str = " " + fieldData.fieldName + "={";
for (var arrayIndex = 0; arrayIndex < fieldData.ObjListCount(); arrayIndex++)
{
AraryFieldData afd = fieldData.GetArrayFieldByIndex(arrayIndex);
str = str + string.Format("[{0}]={1}", afd.keyName, afd.values[rowIndex]);
if (arrayIndex != fieldData.ObjListCount() - 1)
{
str = str + ",";
}
}
writer.Write(str + "}");
}
else if (fieldData.objType == FieldObjectType.ITEM)
{
string str = " " + fieldData.fieldName + " = {";
List <ItemData> itemList = fieldData.GetItemDataListByIndex(rowIndex);
for (var itemIndex = 0; itemIndex < itemList.Count; itemIndex++)
{
ItemData item = itemList[itemIndex];
str = str + "{" + string.Format("type={0},id={1},count={2}", item.type, item.id, item.count) + "}";
if (itemIndex != itemList.Count - 1)
{
str = str + ",";
}
}
str += "}";
writer.Write(str);
}
else if (fieldData.objType == FieldObjectType.INT_ARRAY)
{
writer.Write(" " + fieldData.fieldName + " = {");
if (rowIndex < fieldData.objList.Count)
{
List <int> intList = fieldData.objList[rowIndex] as List <int>;
for (var dataIndex = 0; dataIndex < intList.Count; dataIndex++)
{
writer.Write(intList[dataIndex].ToString());
if (dataIndex != intList.Count - 1)
{
writer.Write(",");
}
}
}
writer.Write("}");
}
else
{
//普通字段
if (fieldData.fieldType == typeof(string))
{
string str = string.Format(" {0}=\"{1}\"", fieldData.fieldName, fieldData.dataList[rowIndex]);
writer.Write(str);
}
else if (fieldData.fieldType == typeof(Boolean))
{
//读取出来的true 和 false 都是大写开头的这里要单独处理一下
string str = string.Format(" {0}={1}", fieldData.fieldName, fieldData.dataList[rowIndex].ToLower());
writer.Write(str);
}
else
{
string str = string.Format(" {0}={1}", fieldData.fieldName, fieldData.dataList[rowIndex]);
writer.Write(str);
}
}
//最后一行不给逗号分隔
if (i != data.filedList.Count - 1)
{
writer.WriteLine(",");
}
else
{
writer.WriteLine(" ");
}
}
if (rowIndex == rowCount - 1)
{
writer.WriteLine(" }");
}
else
{
writer.WriteLine(" },");
}
}
}
protected virtual void AddEnd(ExcelSheetData data, StreamWriter writer)
{
writer.WriteLine(data.exportEnd);
}
protected virtual void AddHeader(ExcelSheetData data, StreamWriter writer)
{
writer.WriteLine(data.exportHeader);
}
protected virtual void ExportBase(ExcelSheetData data, StreamWriter writer)
{
//nothing
}
