public void DataOnSheet2WithRange()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <ExcelDataSheet2> source = new ExcelSource <ExcelDataSheet2>("res/Excel/DataOnSheet2.xlsx")
{
Range = new ExcelRange(2, 4, 5, 9),
SheetName = "Sheet2"
};
DBDestination <ExcelDataSheet2> dest = new DBDestination <ExcelDataSheet2>(Connection, "ExcelDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(5, RowCountTask.Count(Connection, "ExcelDestination"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert1' AND Col3 = 5 AND Col4 = 1"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 IS NULL AND Col3 = 0 AND Col4 = 1.2"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 IS NULL AND Col3 = 7 AND Col4 = 1.234"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert4' AND Col3 = 8 AND Col4 = 1.2345"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert5' AND Col3 = 9 AND Col4 = 2"));
}
public void Excel_DB()
{
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("Col1", "int", allowNulls: false),
new TableColumn("Col2", "nvarchar(100)", allowNulls: true),
new TableColumn("Col3", "decimal(10,2)", allowNulls: true)
});
stagingTable.CreateTable();
ExcelSource <ExcelData> source = new ExcelSource <ExcelData>("src/DataFlow/ExcelDataFile.xlsx")
{
Range = new ExcelRange(2, 4, 5, 9),
SheetName = "Sheet2"
};
DBDestination <ExcelData> dest = new DBDestination <ExcelData>()
{
DestinationTableDefinition = stagingTable
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(5, RowCountTask.Count("test.Staging"));
}
public void SimpleDataNoHeader()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationStringArray");
//Act
ExcelSource <string[]> source = new ExcelSource <string[]>("res/Excel/TwoColumnData.xlsx")
{
HasNoHeader = true
};
RowTransformation <string[], MyData> trans = new RowTransformation <string[], MyData>(row =>
{
MyData result = new MyData();
result.Col1 = int.Parse(row[0]);
result.Col2 = row[1];
return(result);
});
DbDestination <MyData> dest = new DbDestination <MyData>(Connection, "ExcelDestinationStringArray");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SimpleDataNoHeader()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationDynamic");
//Act
ExcelSource source = new ExcelSource("res/Excel/TwoColumnShiftedData.xlsx")
{
Range = new ExcelRange(3, 4),
HasNoHeader = true
};
RowTransformation trans = new RowTransformation(row =>
{
dynamic r = row as dynamic;
r.Col1 = r.Column1;
r.Col2 = r.Column2;
return(r);
});
DbDestination dest = new DbDestination(Connection, "ExcelDestinationDynamic");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public ActionResult ReadData()
{
string filePath = @"D:\Tech Academy\TA-C-Sharp-Coding\C# Final\ExcelSourceProject\ExcelSource.xlsx";
var excelSource = new ExcelQueryFactory(filePath);
var readSource = from x in excelSource.Worksheet() select x;
using (ExcelSourceEntities db = new ExcelSourceEntities())
{
foreach (var x in readSource)
{
string firstName = x["FirstName"];
string lastName = x["LastName"];
string emailAddress = x["EmailAddress"];
var excelsource = new ExcelSource();
excelsource.FirstName = firstName;
excelsource.LastName = lastName;
excelsource.EmailAddress = emailAddress;
db.ExcelSources.Add(excelsource);
db.SaveChanges();
}
}
return(View("ReadSuccess"));
}
private void btnEn_Click(object sender, EventArgs e)
{
string check = "0";
string checkFileName = string.Empty;
if (Chose)
{
check = "0";
//判断有无文件
if (!File.Exists(txtExcel.Text))
{
MessageBox.Show("请选择正确的Excel文件导入");
txtExcel.Text = string.Empty;
return;
}
checkFileName = txtExcel.Text;
//是Excel源,读取Excel的信息
//在这里做个检测
ExcelSource ex = new ExcelSource();
string msg = string.Empty;
List <IMovieShowList.MovieShow> list = new List <MovieShow>();
//获取一下信息
list = ex.GetList4Excel(txtExcel.Text);
if (!ex.isOk)
{
//判断里面有无错误
MessageBox.Show(ex.Msg);
return;
}
ShowFrmMain(check, checkFileName);
}
}
/// <summary>
/// 加载信息
/// </summary>
private void LoadList(string date)
{
#region 运用类库
List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>();
if (!Chose)
{
list = MovieObjFactory.GetMovieObj().GetMovieList(date); //getList.GetMovieList(date);
//运用公共方法将获取 到的表做下处理
list = Common.ParseList(list);
lbApiDate.Visible = lbApiInfo.Visible = true;
lbApiDate.Text = date;
lbExcelInfo.Visible = lbExcelSource.Visible = false;
}
else
{
tsmToday.Enabled = false;
tsmNex.Enabled = false;
dateTimePicker1.Enabled = false;
lbApiInfo.Visible = false;
lbApiDate.Visible = false;
//读取排片表的数据
ExcelSource ex = new ExcelSource();
string fileName = File.ReadAllText(SetPath.LastSet).Split('|')[1];
list = ex.GetList4Excel(fileName);
lbExcelInfo.Visible = lbExcelSource.Visible = true;
lbExcelSource.Text = fileName;
}
movieList = list;
#endregion
}
/// <summary>
/// 初始化数据信息
/// </summary>
private void InitDataSource()
{
//创建存储变量
List <IMovieShowList.MovieShow> list = new List <MovieShow>();
//判断登陆设置窗体传过来的是Excel源还是Api源
if (Chose)
{
if (Chose)
{
Point loc = btnResert.Location;
//btnResert.Enabled = false;
panelLeft.Controls.Remove(btnResert);
btnOut.Location = loc;
}
//是Excel源,读取Excel的信息
ExcelSource ex = new ExcelSource();
list = ex.GetList4Excel(File.ReadAllText(SetPath.LastSet).Split('|')[1]).OrderBy(m => m.BeginTime).ToList();
}
else
{
//是api源,获取影片排片表
List <IMovieShowList.MovieShow> listMovies = MovieObjFactory.GetMovieObj().GetMovieList(DateTime.Now.ToString("yyyyMMdd"));
//转换排片表
list = Common.ParseList(listMovies);
}
//开始异步下载
Task.Factory.StartNew(ListLoad, list, cts.Token);
//启动定时器3
}
public void WithoutErrorLinking()
{
//Arrange
ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnErrorLinking.xlsx");
MemoryDestination <MySimpleRow> dest = new MemoryDestination <MySimpleRow>();
//Act & Assert
Assert.Throws <System.FormatException>(() =>
{
source.LinkTo(dest);
source.Execute();
dest.Wait();
});
}
private IList <MyDataRow> LoadExcelIntoMemory(string filename)
{
MemoryDestination <MyDataRow> dest = new MemoryDestination <MyDataRow>();
ExcelSource <MyDataRow> source = new ExcelSource <MyDataRow>(filename)
{
Range = new ExcelRange(1, 3)
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
return(dest.Data.ToList());
}
public void SimpleDataWithHeader()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationDynamicWithHeader");
ExcelSource source = new ExcelSource("res/Excel/TwoColumnWithHeader.xlsx");
DbDestination dest = new DbDestination(Connection, "ExcelDestinationDynamicWithHeader");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SimpleData()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationWithNameAttribute");
ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnWithHeader.xlsx");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(Connection, "ExcelDestinationWithNameAttribute");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void OnlyOneExcelColumn()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <OneExcelColumn> source = new ExcelSource <OneExcelColumn>("res/Excel/TwoColumnData.xlsx");
DBDestination <OneExcelColumn> dest = new DBDestination <OneExcelColumn>(Connection, "ExcelDestination", 2);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(Connection, "ExcelDestination", "Col1 = 0 AND Col2 LIKE 'Test%'"));
}
public void SimpleData()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnData.xlsx");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "ExcelDestination", 2);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void IgnoreBlankRows()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationBlankRows");
//Act
ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnBlankRow.xlsx")
{
IgnoreBlankRows = true
};
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("ExcelDestinationBlankRows", Connection, 2);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void WithObjectErrorLinking()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelSourceErrorLinking");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(SqlConnection, "ExcelSourceErrorLinking");
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
//Act
ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnErrorLinking.xlsx");
source.LinkTo(dest);
source.LinkErrorTo(errorDest);
source.Execute();
dest.Wait();
errorDest.Wait();
//Assert
dest2Columns.AssertTestData();
Assert.Collection <ETLBoxError>(errorDest.Data,
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText))
);
}
public void Exceding20Columns()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <Excel21Cols> source = new ExcelSource <Excel21Cols>("res/Excel/MoreThan20Cols.xlsx")
{
Range = new ExcelRange(1, 2),
};
MemoryDestination <Excel21Cols> dest = new MemoryDestination <Excel21Cols>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.Collection <Excel21Cols>(dest.Data,
r => Assert.True(r.Col1 == 1 && r.Col2 == "Test1" && r.N == "N" && r.V == "V"),
r => Assert.True(r.Col1 == 2 && r.Col2 == "Test2" && r.N == "N" && r.V == "V"),
r => Assert.True(r.Col1 == 3 && r.Col2 == "Test3" && r.N == "N" && r.V == "V")
);
}
static void Main(string[] args)
{
var sw = new Stopwatch();
sw.Start();
log.Info("Reading data source...");
IInvestmentSimulator indexSimulator = new InvestmentSimulator();
IDataSource dataSource = new ExcelSource();
IExporter exporter = new ExcelExporter();
var list = dataSource.ExtractData();
log.Info("Calculating...");
var incomeList = indexSimulator.Calculate(list);
log.Info($"Final Capital: {incomeList.FinalCapital}");
log.Info($"Total Investment: {incomeList.TotalInvestment}");
log.Info($"Total Gain: {incomeList.TotalGain}");
log.Info("Exporting data to Excel...");
exporter.Export(incomeList, @"ZaraCode.xlsx");
sw.Stop();
Console.WriteLine("Time elapsed: {0}", sw.Elapsed.ToString("hh\\:mm\\:ss\\.fff"));
Console.ReadKey();
}
/// <summary>
/// 场务表的导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 场务表ToolStripMenuItem_Click(object sender, EventArgs e)
{
#region MyRegion
///创建集合用以保存排片信息
List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>();
try
{
if (newExcel)
{
ExcelSource ex = new ExcelSource();
list = ex.GetList4Excel(newFileName);
if (!ex.isOk)
{
MessageBox.Show(ex.Msg);
return;
}
}
else
{
//排片信息读取
list = movieList;
}
if (list.Count == 0)
{
MessageBox.Show("没有排片信息");
return;
}
}
catch
{
MessageBox.Show("未知错误,请重试");
return;
}
MovieEndTime end = new MovieEndTime();
list = end.GetMovieEndTimeList(list);
//将信息按时间排序
string headeValue = string.Empty;
bool ok = IsExcelOrApi(out headeValue, list);
if (!ok)
{
return;
}
//创建一个工作薄对象
hssfworkbook = new HSSFWorkbook();
//创建一张表
Sheet sheet = hssfworkbook.CreateSheet("场务表");
//创建第一行表头行
CreateR1(sheet, 0, 0, 0, 5, headeValue);
///-----设置列宽格式 开始------//
//设置第1列到第3列
SetColWidth(sheet, 0, 3, 10);
//设置第4列(电影名称列)
sheet.SetColumnWidth(3, 26 * 256 + 200);
//设置第5列到第6列
SetColWidth(sheet, 4, 2, 15);
///-----设置格式 结束------//
//创建一个单元格样式对象
CellStyle styleCell = SetCellAllCenterBorder();
#region 读取出数据
//创建行标识(第二行开始)
int count = 1;
//循环读取排片信息集合
foreach (IMovieShowList.MovieShow item in list)
{
//创建行
Row rowCell = sheet.CreateRow(count);
//设置行高
rowCell.HeightInPoints = 20;
//写入行信息
SetCellMovieInfo(rowCell, styleCell, item);
count++;
}
#endregion
//打开一个保存对话框,让用户保存数据
SaveFileDialog save = new SaveFileDialog();
save.Filter = "excel|*.xls";
save.FileName = headeValue + "--场务";//设置默认文件名
DialogResult res = save.ShowDialog();
if (res == DialogResult.OK)
{
string path = save.FileName;
try
{
FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(file);
file.Close();
MessageBox.Show("保存成功");
}
catch
{
MessageBox.Show("文件已打开,请先关闭文件");
return;
}
}
else
{
return;
}
#endregion
}
/// <summary>
/// 放映表导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 放映表ToolStripMenuItem_Click(object sender, EventArgs e)
{
//创建排片信息集合对象,用以保存信息
List <IMovieShowList.MovieShow> listMovie = new List <IMovieShowList.MovieShow>();
try
{
if (newExcel)
{
ExcelSource ex = new ExcelSource();
listMovie = ex.GetList4Excel(newFileName);
if (!ex.isOk)
{
MessageBox.Show(ex.Msg);
return;
}
}
else
{
//排片信息读取
listMovie = movieList;
}
if (listMovie.Count == 0)
{
MessageBox.Show("没有排片信息");
return;
}
}
catch
{
MessageBox.Show("未知错误,请重试");
return;
}
MovieEndTime end = new MovieEndTime();
listMovie = end.GetMovieEndTimeList(listMovie);
string headeValue = string.Empty;
//是否Excel源或Api源
bool ok = IsExcelOrApi(out headeValue, listMovie);
if (!ok)
{
return;
}
//让排片信息以厅号来排序
var iList = listMovie.OrderBy(i => i.Room[0]);
//提取所有厅号
var a = from room in iList select room.Room;
//去重之后放映厅的集合
List <string> RoomInfo = a.Distinct().ToList();
//创建放映表
hssfworkbook = new HSSFWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("放映表");
sheet.PrintSetup.Landscape = true;//设置为横向
//设定列宽
for (int i = 0; i < 12; i++)
{
if ((i + 1) % 3 == 0)
{
//将包含电影名称的列设置列宽
sheet.SetColumnWidth(i, 23 * 256 + 200);
}
else
{
//其它的列设置列宽
sheet.SetColumnWidth(i, 6 * 256 + 200);
}
}
//创建第一行
CreateR1(sheet, 0, 0, 0, 11, headeValue);
//行标识从第2行开始
int roomCout = 1;
Dictionary <string, int> roomIndex = new Dictionary <string, int>();
//创建当前行
Row row = sheet.CreateRow(roomCout);
row.HeightInPoints = 30;
int j = -1;
//保存厅号的位置信息
List <Cell> listCell = new List <Cell>();
//创建其它字段
for (int i = 0; i < RoomInfo.Count; i++)
{
//创建第四个厅的时候转折
if ((i + 1) % 5 == 0)
{
//并且前面空余8格
roomCout += 9;
row = sheet.CreateRow(roomCout);
row.HeightInPoints = 30;
j = -1;
}
j++;
//创建单元格
//创建字体样式
NPOI.SS.UserModel.Font fontHeader = hssfworkbook.CreateFont();
fontHeader.FontHeightInPoints = 12;
fontHeader.Boldweight = 700;
int count = j * 3;
Cell cell = row.CreateCell(count);
cell.SetCellValue(RoomInfo[i]);
cell.CellStyle = SetCellAllCenterBorder();
cell.CellStyle.SetFont(fontHeader);
//将后面的两个单元格设置出来
SetCell(row, count + 1, string.Empty);
SetCell(row, count + 2, string.Empty);
//进行单元格合并
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(roomCout, roomCout, count, count + 2));
listCell.Add(cell);
}
//将影片信息按厅排列
List <IMovieShowList.MovieShow> li = listMovie.OrderBy(x => x.Room[0]).ToList <IMovieShowList.MovieShow>();
int rowIndex = 0;
int indexCol;
foreach (var item in listCell)
{
//当前厅名
string room = item.StringCellValue;
//找到当前厅的所有子集并且进行排序
List <IMovieShowList.MovieShow> s = (from i in li where i.Room == room select i).OrderBy(x => x.BeginTime).ToList <IMovieShowList.MovieShow>();
//如果rowIndex与上次一致,则不创建行,直接在原有的行上创建单元格
//保存之前的rowIndex;
rowIndex = item.RowIndex + 1; //3
indexCol = item.ColumnIndex; //0
//循环子集添加
foreach (var movie in s)
{
//获得下一行
Row rowCell = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
//设置行高
rowCell.HeightInPoints = 20;
//在行下面创建三个单元格填充值
SetCell(rowCell, indexCol, movie.BeginTime);
SetCell(rowCell, indexCol + 1, movie.EndTime);
//单独处理电影名称,让其左对齐
//SetCell(rowCell, indexCol+2, movie.Name);
Cell cellName = rowCell.CreateCell(indexCol + 2);
cellName.CellStyle = cellLeftAllBorder();
cellName.SetCellValue(movie.Name);
rowIndex++;
}
}
//打开一个保存对话框,让用户保存数据
SaveFileDialog save = new SaveFileDialog();
save.Filter = "excel|*.xls";
save.FileName = headeValue + "--放映 ";
DialogResult res = save.ShowDialog();
if (res == DialogResult.OK)
{
string path = save.FileName;
try
{
FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write);
hssfworkbook.Write(file);
file.Close();
MessageBox.Show("保存成功");
}
catch
{
MessageBox.Show("文件已打开,请先关闭文件");
return;
}
}
else
{
return;
}
}
