public async Task GivenFileXlsx_UploadSuccess()
{
var client = await _factory.GetAuthenticatedClientAsync();
var fileMock = new Mock <IFormFile>();
var fileName = "test.xlsx";
//Load Workbook
Workbook workbook = new Workbook();
workbook.CreateEmptySheet();
MemoryStream stream = new MemoryStream();
workbook.SaveToStream(stream);
fileMock.Setup(_ => _.OpenReadStream()).Returns(stream);
fileMock.Setup(_ => _.FileName).Returns(fileName);
fileMock.Setup(_ => _.Length).Returns(stream.Length);
var file = fileMock.Object;
var formData = new MultipartFormDataContent();
// Add file (file, field name, file name)
formData.Add(new StreamContent(stream), "files", fileName);
var response = await client.PostAsync($"/api/Upload/", formData);
response.StatusCode.ShouldBe(HttpStatusCode.OK);
var vm = await IntegrationTestHelper.GetResponseContent <FileListDto>(response);
vm.Count.ShouldBe(1);
vm.Files[0].FileName.ShouldBe(fileName);
}
/// <summary>
/// 匯出所有資料
/// </summary>
/// <param name="contactSituationList">聯繫狀況資料</param>
/// <param name="interviewDataList">面談資料</param>
/// <param name="path">存檔路徑</param>
/// <param name="count"></param>
/// <returns></returns>
public string ExportAllData(List <ContactSituation> contactSituationList, List <InterviewData> interviewDataList, string path, int count)
{
try
{
//建立Workbook
Workbook workbook = new Workbook();
workbook.LoadTemplateFromFile(@".\Template\TalentTemplate.xlsx");
//聯繫狀況Template
Worksheet sheet = workbook.Worksheets[0];
sheet = CreateContactSituationSheet(contactSituationList, sheet, 0);
for (int i = 0; i < interviewDataList.Count; i++)
{
////面談基本資訊Template
Worksheet sheet1 = workbook.CreateEmptySheet();
sheet1.Name = "人事資料" + (i + 1);
sheet1.CopyFrom(workbook.Worksheets[1]);
sheet1 = CreateInterviewInfoSheet(interviewDataList[i].InterviewInfo, sheet1);
////專案經驗Template
Worksheet sheet2 = workbook.CreateEmptySheet();
sheet2.Name = "專案經驗" + (i + 1);
sheet2.CopyFrom(workbook.Worksheets[2]);
sheet2 = CreateProjectExperienceSheet(interviewDataList[i].ProjectExperienceList, sheet2);
////面談結果Template
Worksheet sheet3 = workbook.CreateEmptySheet();
sheet3.Name = "面談結果" + (i + 1);
sheet3.CopyFrom(workbook.Worksheets[3]);
sheet3 = CreateInterviewResualtSheet(interviewDataList[i].InterviewResults, sheet3);
}
////隱藏Template Sheet
for (int i = 1; i < 4; i++)
{
workbook.Worksheets[i].Visibility = WorksheetVisibility.Hidden;
}
//儲存到實體路徑
string strFullName = Path.Combine(path, count + ".聯繫狀況與面談資料" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
workbook.SaveToFile(strFullName, ExcelVersion.Version2010);
return("匯出成功");
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
return("匯出失敗");
}
}
public InputOnlineFile CreateExtract(decimal allEarnings, decimal allDebts)
{
var workbook = new Workbook();
workbook.CreateEmptySheet();
var sheet = workbook.Worksheets[0];
if (allEarnings == 0 && allDebts == 0)
{
sheet.Range[1, 1].Text = "Não é possível gerar extratos enquanto não houver movimentação financeira.";
var streamToReturn = new MemoryStream();
workbook.SaveToStream(streamToReturn, FileFormat.PDF);
streamToReturn.Flush();
return(streamToReturn);
}
sheet.PageSetup.LeftHeader = $"Relatório das movimentações {DateTime.Now:MM/yyyy}";
sheet.Range[2, 1].Value = " Ganhos";
sheet.Range[2, 2].Value = " Gastos";
sheet.Range[3, 1].NumberValue = (double)allEarnings;
sheet.Range[3, 2].NumberValue = (double)allDebts;
sheet.Range[3, 1, 3, 2].Style.Font.Color = Color.White;
sheet.Range[3, 1, 3, 2].Style.NumberFormat = @"""R$""0.00";
var chart = sheet.Charts.Add(ExcelChartType.Pie3D);
chart.SeriesDataFromRange = false;
chart.PlotArea.Fill.Visible = false;
chart.ChartTitle = "Soma das movimentações mensais";
var cs = chart.Series.Add();
cs.CategoryLabels = sheet.Range[2, 1, 2, 2];
cs.Values = sheet.Range[3, 1, 3, 2];
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
var profit = allEarnings - allDebts;
var spendPerDay = allDebts / DateTime.Now.Day;
var spendPerDayString = spendPerDay.ToString().Substring(0, spendPerDay.ToString().IndexOf(',') + 3);
var summaryOfMonthly = "A média dos gastos diários foi de " +
$"{spendPerDayString} reais." +
" Com base na movimentação financeira desse mês, " +
$"{(profit >= 0 ? $"restou {profit} reais." : $"faltou {profit} reais para abater suas dívidas.")}";
/// <summary>
/// 匯出多筆聯繫狀況資料
/// </summary>
/// <param name="ContactSituationList"></param>
public string ExportMultipleContactSituation(List <ContactSituation> ContactSituationList, string path)
{
try
{
//建立Workbook
Workbook workbook = new Workbook();
workbook.LoadTemplateFromFile(@".\Template\TalentTemplate.xlsx");
for (int i = 0; i < ContactSituationList.Count; i++)
{
Worksheet sheet = workbook.CreateEmptySheet();
////第一個Sheet當作Template
sheet.CopyFrom(workbook.Worksheets[0]);
////Sheet命名
if (!string.IsNullOrEmpty(ContactSituationList[i].Info.Name))
{
sheet.Name = (i + 1) + "." + ContactSituationList[i].Info.Name;
}
else if (!string.IsNullOrEmpty(ContactSituationList[i].Code))
{
string[] code = ContactSituationList[i].Code.Split(new string[] { "\n" }, StringSplitOptions.None);
sheet.Name = (i + 1) + "." + code[0];
}
sheet = CreateContactSituationSheet(ContactSituationList, sheet, i);
}
////刪除Template Sheet
workbook.Worksheets.Remove("聯繫狀況");
workbook.Worksheets.Remove("人事資料");
workbook.Worksheets.Remove("專案經驗");
workbook.Worksheets.Remove("面談結果");
//儲存到實體路徑
string strFullName = Path.Combine(path, "聯繫狀況" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
workbook.SaveToFile(strFullName, ExcelVersion.Version2010);
return("匯出成功");
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
return("匯出失敗");
}
}
public static void Builde()
{
// 从文件中读取名字
// 第一行为标题行
//string path = Environment.CurrentDirectory;
FileStream stream = new FileStream("names.txt", FileMode.Open);
StreamReader reader = new StreamReader(stream);
List <string> names = new List <string>();
while (reader.Peek() >= 0)
{
names.Add(reader.ReadLine());
}
reader.Close();
string title = names[0];
names.RemoveAt(0);
//创建xlsx文档，使用Spire.XLS
Workbook workbook = new Workbook();
workbook.CreateEmptySheet();
Worksheet worksheet = workbook.Worksheets[0];
//首先确定要生成数据表的行数和列数
WriteLine("Please input the DateTime you want create?");
//初始的时间是程序运行时间的上一个月。
int year = DateTime.Now.Year;
int month = DateTime.Now.AddMonths(-1).Month;
int tempYear = year;
int tempMonth = month;
do
{
Write($"Please input the year:(default:{year})");
try
{
tempYear = Convert.ToInt32(ReadLine());
}
catch (Exception)
{
tempYear = year;
}
Write($"Please input the month:(default:{month})");
try
{
tempMonth = Convert.ToInt32(ReadLine());
}
catch (Exception)
{
tempMonth = month;
}
} while ((tempYear <= 0) || (tempMonth <= 0) || (tempMonth > 12));
year = tempYear;
month = tempMonth;
//获取当月的天数
int days = DateTime.DaysInMonth(year, month);
//将要填充的数据先放到一个Object类型的二维数组当中
int row = names.Count + 2;
int col = days + 1;
object[,] myData = new object[row, col];
//填充值班数据
for (int j = 1; j < row; j++)
{
for (int i = 1; i < col; i++)
{
myData[j, i] = "";
}
}
myData[0, 0] = "姓名";
myData[row - 1, 0] = "备注";
myData[row - 1, 1] = "加班☆；值班△；休假□；请事（病）假★。";
//从json文件中读取节假日数据
string configPath = string.Format("holiday.json");
HolidayChecker checker = new HolidayChecker(configPath);
for (int i = 1; i <= days; i++)
{
myData[0, i] = $"{i}日";
worksheet.SetColumnWidth(i + 1, 3.5);
//修改颜色
DateTime currentDay = new DateTime(year, month, i);
DateType currentDayType = checker.CheckDayType(currentDay);
if ((currentDayType is DateType.Weekend) || (currentDayType is DateType.Holiday))
{
string range = String.Format("{0}1:{0}{1}", NumberCharsConvert.NumberToChars(i + 1), names.Count - 1);
worksheet.Range[range].Style.Color = (currentDayType is DateType.Weekend ? Color.Gray : Color.Red);
}
}
for (int j = 1; j <= names.Count; j++)
{
myData[j, 0] = names[j - 1];
worksheet.SetRowHeight(j + 1, 40);
}
worksheet.InsertArray(myData, 1, 1);
//整理表格格式
//设置列宽和行高
worksheet.SetRowHeight(1, 40);
// worksheet.SetRowHeight(names.Count + 2, 40);
// worksheet.Rows[row-1].RowHeight = 40;
//worksheet.Rows[row-1].Style.Color = Color.;
worksheet.SetColumnWidth(1, 6);
//设置边框
CellRange xlsRange = worksheet.Range[$"A1:{NumberCharsConvert.NumberToChars(col)}{row}"];
xlsRange.BorderInside(LineStyleType.Thin, Color.Black);
xlsRange.BorderAround(LineStyleType.Thin, Color.Black);
//设置对齐方式
xlsRange.Style.HorizontalAlignment = HorizontalAlignType.Center;
xlsRange.Style.VerticalAlignment = VerticalAlignType.Center;
//合并单元格
worksheet.Range[$"B{names.Count + 2}:{NumberCharsConvert.NumberToChars(days + 1)}{names.Count + 2}"].Merge();
worksheet.Range[$"B{names.Count + 2}"].HorizontalAlignment = HorizontalAlignType.Left;
//设置打印页面格式为A4
worksheet.PageSetup.PaperSize = PaperSizeType.PaperA4;
//set header&footer
worksheet.PageSetup.CenterHeader = $"{title}{year}年{month}月考勤登记表";
worksheet.PageSetup.LeftFooter = "制表人： 政工复核： 审核人：";
//保存文件
workbook.SaveToFile(@"..\..\" + $"{year}年{month}月值（加）班考勤表.xlsx", ExcelVersion.Version2016);
}
private void CreateSheet <T>(List <T> list, Workbook workbook, int index, List <int[]> mergeIndex, string sheetName) where T : new()
{
Worksheet sheet;
if (index > 2)
{
sheet = workbook.CreateEmptySheet();
}
else
{
sheet = workbook.Worksheets[index];
}
sheet.Name = sheetName;
foreach (int[] indexs in mergeIndex)
{
sheet.Range[indexs[0], indexs[1], indexs[2], indexs[3]].Merge();
}
var propertyInfos = AttributesHelper.GetProperties <T>();
#region 表数据
int j = 0, k = 0;
foreach (var property in propertyInfos)
{
var attr = AttributesHelper.GetFieldAttributeDescrition <T, TitleAttribute>(property);
var title = attr?.Title;
if (title == null)
{
continue;
}
#region 列头
sheet.Range[1, k + 1].Text = title;
#endregion
int i = 0;
foreach (var l in list)
{
var dyg = sheet.Range[i + 2, j + 1];
var type = property.PropertyType;
var value = property.GetValue(l);
if (type == typeof(int) || type == typeof(short) || type == typeof(long) || type == typeof(uint) || type == typeof(byte) ||
type == typeof(sbyte) || type == typeof(ulong))
{
dyg.NumberValue = double.Parse(property.GetValue(l).ToString());
dyg.NumberFormat = "#,##0";
}
else if (type == typeof(float) || type == typeof(double) || type == typeof(decimal))
{
dyg.NumberValue = double.Parse(property.GetValue(l).ToString());
dyg.NumberFormat = "#,##0.00";
}
else if (value == null)
{
dyg.Text = "";
}
else
{
dyg.Text = value.ToString();
}
//dyg.ColumnWidth = 22;
//dyg.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;//边框
//dyg.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
//dyg.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
//dyg.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
i++;
}
j++;
k++;
}
#endregion
}
/// <summary>
/// sheet合并到一个Excel中
/// </summary>
/// <param name="model"></param>
public static void MergeExcel(ExcelMergeModel model)
{
string saveName = model.SaveName;
if (saveName == null)
{
MessageBox.Show("保存路径没有填写！！！");
return;
}
Workbook newbook = new Workbook();
//newbook.SaveToFile("d:/123.xls", ExcelVersion.Version2013);
Spire.Xls.Workbook tempbook = null;
//创建一个新的workbook对象
newbook.Version = Spire.Xls.ExcelVersion.Version2013;
//删除文档中的工作表（新创建的文档默认包含3张工作表）
newbook.Worksheets.Clear();
if (newbook.Worksheets.Count == 0)
{
newbook.CreateEmptySheet();
}
newbook.SaveToFile(saveName, ExcelVersion.Version2013);
IList <FileNameCustom> fileNameCustoms = model.Files;
if (model.MergeModel == MergeSytle.MoreSheet)
{
tempbook = new Workbook();
foreach (FileNameCustom custom in fileNameCustoms)
{
tempbook.LoadFromFile(custom.FilePath);
//使用AddCopy方法，将文档中的所有工作表添加到新的workbook
foreach (Worksheet sheet in tempbook.Worksheets)
{
newbook.Worksheets.AddCopy(sheet);
}
}
newbook.SaveToFile(saveName, ExcelVersion.Version2013);
}
else if (model.MergeModel == MergeSytle.OneSheet)
{
//实例化一个Workbook类，加载Excel文档
for (int index = 0; index < fileNameCustoms.Count; index++)
{
FileNameCustom fileNameCustom = fileNameCustoms[index];
tempbook = new Workbook();
try
{
tempbook.LoadFromFile(fileNameCustom.FilePath);
}
catch (Exception e)
{
MessageBox.Show("文件有问题，可能有批注：" + fileNameCustom.FilePath);
continue;
}
for (int a = 0; a < tempbook.Worksheets.Count; a++)
{
//获取第1、2张工作表
Worksheet sheet1 = tempbook.Worksheets[a];
if (newbook.Worksheets.Count <= a)
{
newbook.CreateEmptySheet(sheet1.Name);
}
Worksheet newsheet = newbook.Worksheets[a];
//复制第2张工作表内容到第1张工作表的指定区1域中
CellRange range1 = sheet1.AllocatedRange;
if (range1.RowCount > model.ReduceStartRowCount + model.ReduceEndRowCount + 1)
{
//CellRange range = sheet1.Range[model.ReduceStartRowCount + 1, range1.CellsCount, range1.RowCount - model.ReduceEndRowCount, range1.CellsCount];
int cellCount = range1.CellsCount;
if (cellCount > 256)
{
cellCount = 254;
}
int rowCount = range1.RowCount;
if (rowCount > 20000)
{
rowCount = 20000;
}
CellRange range = sheet1.Range[model.ReduceStartRowCount + 1, 1, rowCount - model.ReduceEndRowCount, cellCount];
if (newsheet.LastRow == -1)
{
range.Copy(newsheet.Range[newsheet.LastRow + 2, 1]);
try
{
}
catch (Exception e)
{
MessageBox.Show("文件有问题：" + fileNameCustom.FilePath);
}
}
else
{
range.Copy(newsheet.Range[newsheet.LastRow + 1, 1]);
try
{
}
catch (Exception e)
{
MessageBox.Show("非常严重问题：" + fileNameCustoms[index - 1].FilePath);
return;
}
}
}
}
//break;
}
//保存并运行文档
newbook.SaveToFile(saveName, ExcelVersion.Version2013);
}
}
public void ExportDataTableToExcel(DataTable t, string existFileName,string sheetName)
{
Workbook book = new Workbook();
Worksheet sheet = book.CreateEmptySheet(sheetName);
sheet.Activate();
sheet.InsertDataTable(t, true, 1, 1);
for (int i = 0; i < sheet.Columns.Length; i++)
{
sheet.Columns[i].Style.VerticalAlignment = VerticalAlignType.Center;
sheet.Columns[i].Style.Font.FontName = "Arial";
sheet.Columns[i].NumberFormat = "0";
sheet.Columns[i].Style.Font.Size = 10;
sheet.Columns[i].Style.HorizontalAlignment = HorizontalAlignType.Left;
}
sheet.AllocatedRange.AutoFitRows();
sheet.AllocatedRange.AutoFitColumns();
book.SaveToFile(existFileName, ExcelVersion.Version2010);
Remove_WarningPage(existFileName);
ExcelDocViewer(existFileName);
}
