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); }