示例#1
0
        public void MergeAllSheet(GetTime gt)
        {
            Spire.Xls.Workbook wb = new Spire.Xls.Workbook();
            wb.LoadFromFile(excelFilePath);
            Spire.Xls.Workbook  destWorkbook = new Spire.Xls.Workbook();
            Spire.Xls.Worksheet destSheet    = destWorkbook.Worksheets[0];
            wb.Worksheets["Sheet1"].AllocatedRange.Copy(destSheet.Range[1, 1]);
            destSheet.Range[1, 1].Text = string.Format("营盘壕煤矿{0}工作面矿压综合分析日报表", gt.getWorkFace());
            int lastrow = destSheet.Range.RowCount;

            if (wb.Worksheets["Sheet2"] != null)
            {
                wb.Worksheets["Sheet2"].AllocatedRange.Copy(destSheet.Range[destSheet.Range.RowCount + 1, 1]);
                if (wb.Worksheets["Sheet3"] != null)
                {
                    wb.Worksheets["Sheet3"].AllocatedRange.Copy(destSheet.Range[lastrow + 1, 11]);
                }
                for (int i = 4; i <= wb.Worksheets.Count + 6; i++)
                {
                    if (wb.Worksheets["Sheet" + i.ToString()] != null)
                    {
                        wb.Worksheets["Sheet" + i.ToString()].AllocatedRange.Copy(destSheet.Range[destSheet.Range.RowCount + 1, 1]);
                    }
                }
            }
            destWorkbook.SaveToFile(@"..\..\modelFile\" + string.Format("Excel_{0}.xls", gt.getDateToday()));
        }
示例#2
0
        /// <summary>
        /// Создает файл формата .doc
        /// </summary>
        /// <param name="path">Путь к Excel файлу, из которого берется таблица.</param>
        private void CreateWordFile(string path)
        {
            Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
            workbook.LoadFromFile(path);
            Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
            Document            doc   = new Document();
            Table table = doc.AddSection().AddTable(true);

            table.ResetCells(sheet.LastRow, sheet.LastColumn);
            for (int r = 1; r <= sheet.LastRow; r++)
            {
                for (int c = 1; c <= sheet.LastColumn; c++)
                {
                    CellRange xCell     = sheet.Range[r, c];
                    TableCell wCell     = table.Rows[r - 1].Cells[c - 1];
                    TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
                }
            }
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Rows[i].Cells.Count; j++)
                {
                    table.Rows[i].Cells[j].Width = 100f;
                }
            }
            doc.SaveToFile("result.doc", Spire.Doc.FileFormat.Docx);
            System.Diagnostics.Process.Start("result.doc");
        }
示例#3
0
        public static void getstudentsfromexcel2(String updir)
        {
            studentList = null;
            studentList = new ObservableCollection <Student>();
            string strFileName = updir;

            Spire.Xls.Workbook wb = new Spire.Xls.Workbook();
            wb.LoadFromFile(strFileName);
            Spire.Xls.CellRange range;
            Worksheet           sheet = wb.Worksheets[0];

            for (int i = 1; i <= 150; i++)

            {
                range = sheet.Range["A" + i.ToString()];
                String Strxvhao = range.DisplayedText.ToString().Trim();
                range = sheet.Range["C" + i.ToString()];
                String Strxuehao = range.DisplayedText.ToString().Trim();
                range = sheet.Range["D" + i.ToString()];
                String Strxm = range.DisplayedText.ToString().Trim();

                {
                    if (isxuehao(Strxuehao))
                    {
                        studentList.Add(new Student(Strxuehao, Strxm, int.Parse(Strxvhao), "11111111", -1, -1));
                    }
                }
            }
        }
示例#4
0
        public static void ReplaceText(RepalcePageViewModel model, IList <FileNameCustom> fileNameCustoms)
        {
            if (!Utils.CheckListExists(fileNameCustoms))
            {
                return;
            }
            IList <ReplaceViewModel> replaceModel = model.ReplaceViewModels;

            foreach (FileNameCustom fileNameCustom in fileNameCustoms)
            {
                string path = fileNameCustom.FilePath;

                if (Utils.CheckFileExists(path))
                {
                    Workbook workbook = new Spire.Xls.Workbook();
                    workbook.LoadFromFile(path);
                    //存在文件就替换
                    if (model.AllSheet)
                    {
                        //全部页面替换
                        ReplaceWorkbook(workbook, model.ReplaceViewModels);
                    }
                    else
                    {
                        int sheetIndex = model.ReplaceSheetIndex;
                        if (workbook.Worksheets.Count > sheetIndex)
                        {
                            //指定的sheet替换
                            ReplaceSheet(workbook.Worksheets[sheetIndex], model.ReplaceViewModels);
                        }
                    }
                    workbook.Save();
                }
            }
        }
示例#5
0
文件: Tool.cs 项目: maoshq/SSM
        public static void InitiExcel(string[] name)
        {
            Spire.Xls.Workbook  workbook  = new Spire.Xls.Workbook();
            Spire.Xls.Worksheet newSheet1 = workbook.CreateEmptySheet("Crashes");
            workbook.Worksheets.Remove("Sheet1");
            workbook.Worksheets.Remove("Sheet2");
            workbook.Worksheets.Remove("Sheet3");

            newSheet1.Range["A1:M1"].ColumnWidth = 17;
            newSheet1.Range.HorizontalAlignment  = HorizontalAlignType.Center;
            newSheet1.Range["B6"].Value          = "Date";

            for (int i = 1; i <= name.Length; i++)
            {
                newSheet1.Range[Convert.ToChar('B' + i).ToString() + "6"].Value = name[i - 1];
            }
            newSheet1.Range[Convert.ToChar('B' + (name.Length + 1)).ToString() + "6"].Value = "All Crashes";
            newSheet1.Range[Convert.ToChar('B' + (name.Length + 2)).ToString() + "6"].Value = "Percent Impacted";
            newSheet1.Range[Convert.ToChar('B' + (name.Length + 3)).ToString() + "6"].Value = "Crashes/OS";
            newSheet1.Range[Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].Value = "OS";
            newSheet1.Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].BorderInside(LineStyleType.Thin, Color.LightBlue);
            newSheet1.Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].BorderAround(LineStyleType.Medium, Color.LightBlue);

            workbook.SaveToFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx", ExcelVersion.Version2013);
        }
示例#6
0
        public ActionResult DownloadExcel()
        {
            BusinessLayer BAL      = new BusinessLayer();
            var           workbook = new Spire.Xls.Workbook();

            workbook.LoadFromFile(Server.MapPath("~/DetailFormatInExcel/DetailsFormat.xlsx"));
            var worksheet1 = workbook.Worksheets[0];
            var worksheet2 = workbook.Worksheets[1];

            byte[] array = null;
            var    dt1   = BAL.GetXlsTableBooks();

            worksheet1.InsertDataTable(dt1, false, 3, 1);
            var dt2 = BAL.GetXlsTableCourse();

            worksheet2.InsertDataTable(dt2, false, 3, 1);
            var dt3 = BAL.GetXlsTableTeacher();

            worksheet2.InsertDataTable(dt3, false, 3, 3);

            using (var ms = new System.IO.MemoryStream())
            {
                workbook.SaveToStream(ms, FileFormat.Version2010);
                ms.Seek(0, System.IO.SeekOrigin.Begin);
                array = ms.ToArray();
            }

            return(File(array, "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet", " Detail.xlsx"));
        }
示例#7
0
 public void ChangeExcel2Image(string filename)
 {
     Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
     workbook.LoadFromFile(filename);
     Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
     sheet.SaveToImage("选型结果.jpg"); //图片后缀.bmp ,imagepath自己设置
 }
示例#8
0
 public void MergeAllSheet(GetTime gt)
 {
     Spire.Xls.Workbook wb = new Spire.Xls.Workbook();
     wb.LoadFromFile(excelFilePath);
     Spire.Xls.Workbook  destWorkbook = new Spire.Xls.Workbook();
     Spire.Xls.Worksheet destSheet    = destWorkbook.Worksheets[0];
     for (int i = 1; i <= wb.Worksheets.Count + 6; i++)
     {
         if (wb.Worksheets["Sheet" + i.ToString()] != null)
         {
             wb.Worksheets["Sheet" + i.ToString()].AllocatedRange.Copy(destSheet.Range[destSheet.LastRow + 3, 1]);
         }
     }
     destWorkbook.SaveToFile(@"..\..\modelFile\" + string.Format("Excel_{0}.xls", gt.getDateToday()));
 }
示例#9
0
        public void LoadReport(string fileName, bool autoFit)
        {
            Workbook book = new Workbook();


            book.LoadFromFile(fileName);

            ReportBookTemplate tplbook = new ReportBookTemplate();

            tplbook.LoadTemplate(db, book, paramMap);
            tplbook.FillTemplate();

            List <ReportSheetTemplate> tplList = tplbook.sheetList;

            // tpl.Clear();

            // Open with Excel

/*			xlapp.Visible = false;*/

            try
            {
                ClearReport(autoFit, book, tplList);
                // xlapp.Workbooks.Close ();

                // Copy Image
            }
            finally
            {
                book.Save();
                book.Dispose();

/*				xlapp.DisplayAlerts = true ;
 *
 *                              xlapp.Visible = true;
 */
                RemoveWarning(fileName);

                Process.Start(fileName);
            }


            // remove warnning sheet.
        }
示例#10
0
        private void export_Click(object sender, EventArgs e)
        {
            var path    = @"D:\test\WorkOrder.xls";
            var outPath = @"D:\\test\\aspose";

            //HSSFWorkbook wk = null;
            //using (FileStream fs = File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
            //{
            //    wk = new HSSFWorkbook(fs);
            //    fs.Close();
            //}
            //ISheet sheet = wk.GetSheet("Sheet1");

            using (Spire.Xls.Workbook workbook = new Spire.Xls.Workbook())
            {
                workbook.LoadFromFile(path);
                Spire.Xls.Worksheet sheet    = workbook.Worksheets[0];
                const string        main_Dir = @"c:/WenTest";
                if (!Directory.Exists(main_Dir))
                {
                    //Directory.CreateDirectory(main_Dir);
                }
                else
                {
                    ExportImg.DeleteFolder(main_Dir);
                    Directory.CreateDirectory(main_Dir);
                }
                //string mypath = Environment.CurrentDirectory+"\\test";
                if (File.Exists(outPath))
                {
                    File.Delete(outPath);
                    Directory.CreateDirectory(outPath);
                }
                else
                {
                    Directory.CreateDirectory(outPath);
                }
                //sheet.SetRowHeightPixels()
                //sheet.SaveToImage(outPath+"\\msample.png");
                sheet.SaveToImage(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn).Save(outPath + "\\test.jpg");

                //IWorkbook workbook = new IWorkbook();
            }
        }
示例#11
0
文件: Tool.cs 项目: maoshq/SSM
        public static void GenerateExcel(Dictionary <string, string[]> condition)
        {
            Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
            string[]           name     = condition.GetValueOrDefault("Name");
            string             arg      = condition.GetValueOrDefault("Model")[0];

            if (File.Exists(System.Environment.CurrentDirectory + "\\RecentData.xlsx"))
            {
                workbook.LoadFromFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx");
            }
            else
            {
                InitiExcel(name);
                workbook.LoadFromFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx");
            }
            LoadData(workbook, name, arg, condition);
            workbook.SaveToFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx", ExcelVersion.Version2013);

            GenerateChart(name);
        }
示例#12
0
文件: Tool.cs 项目: maoshq/SSM
        public static void GenerateChart(string[] name)
        {
            try
            {
                if (File.Exists(System.Environment.CurrentDirectory + "\\Trend Analysis.xlsx"))
                {
                    File.Delete(System.Environment.CurrentDirectory + "\\Trend Analysis.xlsx");
                }
                Spire.Xls.Workbook  workbook = new Spire.Xls.Workbook();
                Spire.Xls.Worksheet newSheet = workbook.CreateEmptySheet("Crashes");
                workbook.Worksheets.Remove("Sheet1");
                workbook.Worksheets.Remove("Sheet2");
                workbook.Worksheets.Remove("Sheet3");

                newSheet.Range["A1:M1"].ColumnWidth = 15;
                //newSheet.Range.HorizontalAlignment = HorizontalAlignType.Center;

                Spire.Xls.Workbook workbook1 = new Spire.Xls.Workbook();
                workbook1.LoadFromFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx");
                //int lastRow = workbook1.Worksheets["Crashes"].LastRow;
                int lastRow = workbook1.Worksheets["Crashes"].LastRow;
                //workbook1.Worksheets["Crashes"].Range["B6:I6"].Copy(workbook.Worksheets["Crashes"].Range["A6:H6"]);
                //workbook1.Worksheets["Crashes"].Range["B" + (lastRow - 29) + ":I" + lastRow].Copy(workbook.Worksheets["Crashes"].Range["A7:H36"]);

                workbook1.Worksheets["Crashes"].Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].Copy(workbook.Worksheets["Crashes"].Range["A41:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + "41"]);
                if (lastRow < 37)
                {
                    workbook1.Worksheets["Crashes"].Range["B7" + ":" + Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow].Copy(workbook.Worksheets["Crashes"].Range["A42:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + (lastRow + 25)]);
                }
                else
                {
                    workbook1.Worksheets["Crashes"].Range["B" + (lastRow - 29) + ":" + Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow].Copy(workbook.Worksheets["Crashes"].Range["A42:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + "71"]);
                }
                workbook.Worksheets["Crashes"].Range["A41:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + (lastRow + 25)].HorizontalAlignment = HorizontalAlignType.Center;
                workbook.SaveToFile(System.Environment.CurrentDirectory + "\\Trend Analysis.xlsx", ExcelVersion.Version2013);
            }
            catch (Exception)
            {
                throw;
            }
        }
示例#13
0
        static void Main(string[] args)
        {
            try
            {
                Workbook workbook = new Workbook();

                workbook.LoadFromFile(@"C:\temp\CFRI_Clinical_DB_Approved_Studies_v24.xlsx");
                //Initailize worksheet
                Worksheet sheet = workbook.Worksheets[0];

                DataTable dataTable = sheet.ExportDataTable();

                DataTableToJsonObj(dataTable);


                Console.ReadKey();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadKey();
            }
        }
示例#14
0
        /// <summary>
        /// 导出
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection">数据源</param>
        /// <param name="title">列名</param>
        /// <param name="fileName">文件名</param>
        public static void Except <T>(IEnumerable <T> collection, string title, string fileName)
        {
            var tb = Utility.ToDataTable(collection);

            Spire.Xls.Workbook  workbook = new Spire.Xls.Workbook();
            Spire.Xls.Worksheet sheet    = workbook.Worksheets[0];
            sheet.InsertDataTable(tb, true, 1, 1);

            var dataRow    = sheet.Rows[0];
            var strColumns = title;

            string[] strArry = strColumns.Split(',');
            for (int i = 0; i < strArry.Length; i++)
            {
                dataRow.Cells[i].Value = strArry[i];
            }

            var resp = System.Web.HttpContext.Current.Response;

            workbook.SaveToHttpResponse(fileName + ".xls", resp);

            resp.Flush();
            resp.End();
        }
示例#15
0
文件: Tool.cs 项目: maoshq/SSM
        public static void LoadData(Spire.Xls.Workbook workbook, string[] name, string arg, Dictionary <string, string[]> condition)
        {
            driverData = new Dictionary <string, List <string> >();
            Spire.Xls.Worksheet sheet = workbook.Worksheets["Crashes"];
            int lastRow = sheet.Range.LastRow;              //2020/10/12 0:00:00

            String path = arg == "NET" ? NetPath : Path;

            //string[] fileName = Directory.GetFiles(System.Environment.CurrentDirectory + "\\ExportData", "*.csv");
            string[] fileName  = Directory.GetFiles(path, "Reliability-Crashes_Total*.csv");
            string[] fileName1 = Directory.GetFiles(path, "OSAdoption-TMAD*.csv");
            string   firstDate = DateTime.Now.Year.ToString() + "/" + fileName[0].Split("Total-")[1].Split(".")[0].Split("-")[1] + "/" + fileName[0].Split("Total-")[1].Split(".")[0].Split("-")[2]; //获取文件列表中第一项文件日期

            //string lastDate = DateTime.Now.Year.ToString() + "/" + fileName[^1].Split("Total-")[1].Split(".")[0].Split("-")[0] + "/" + fileName[^1].Split("Total-")[1].Split(".")[0].Split("-")[1];

            string[] date_1   = sheet.Range["B" + lastRow].Value == "Date" ? firstDate.Split("/") : sheet.Range["B" + lastRow].Value.Split(" ")[0].ToString().Split("/");
            DateTime dateTime = new DateTime(Convert.ToInt32(date_1[0]), Convert.ToInt32(date_1[1]), Convert.ToInt32(date_1[2]));

            string curr_Date  = DateTime.Now.ToString("yyyy-MM-dd");
            string curr_Date1 = DateTime.Now.ToString("yyyy/M/dd");

            bool flag = readCSV(path + $"\\Reliability-Crashes_Date-{curr_Date}.csv", out DataTable t);

            if (flag == false)
            {
                for (int i = 0; i < 10; i++)
                {
                    flag = readCSV(path + $"\\Reliability-Crashes_Date-{DateTime.Now.AddDays(-1)}.csv", out t);
                    if (flag)
                    {
                        break;
                    }
                }
            }
            string reportDate = t.Rows[0].ItemArray[0].ToString().Split(" ")[2];//Report Date : 2020-12-05

            for (int i = 0; i < fileName.Length; i++)
            {
                int      lastRow1  = sheet.Range.LastRow + 1;
                string   date      = DateTime.Now.Year.ToString() + "/" + fileName[i].Split("Total-")[1].Split(".")[0].Split("-")[1] + "/" + fileName[i].Split("Total-")[1].Split(".")[0].Split("-")[2];
                DateTime new_date1 = Convert.ToDateTime(date);
                if (sheet.Range["B" + lastRow].Value == "Date" || DateTime.Compare(sheet.Range["B" + lastRow].DateTimeValue, new_date1) < 0)
                {
                    string fullpath  = fileName[i];
                    string fullpath1 = fileName[i].Replace(Crashes_Total, TMAD);
                    readCSV(fullpath, out DataTable dt);

                    sheet.Range["B" + lastRow1].DateTimeValue = new_date1;
                    int[] result = GetCrashes2(dt, condition);



                    for (int x = 1; x <= name.Length; x++)
                    {
                        sheet.Range[Convert.ToChar('B' + x).ToString() + lastRow1].NumberValue = result[0];
                    }
                    sheet.Range[Convert.ToChar('B' + (name.Length + 1)).ToString() + lastRow1].NumberValue = result[1];

                    int tmad = 0;
                    if (fileName1.Contains(fullpath1))
                    {
                        readCSV(fullpath1, out DataTable dt1);
                        if (AnalyzeCSV(dt1, "TMAD", "2004 | Vb", condition)[0] != 0)
                        {
                            sheet.Range[Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow1].NumberValue = AnalyzeCSV(dt1, "TMAD", "2004 | Vb", condition)[0];
                        }
                        else
                        {
                            tmad = AnalyzeCSV(dt1, "TMAD", "Insider | Vb", condition)[0];
                            sheet.Range[Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow1].NumberValue = tmad;
                        }
                    }
                    else
                    {
                        int lastRow2 = lastRow1 - 1;
                        tmad = (int)sheet.Range[Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow2].NumberValue;
                        sheet.Range[Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow1].NumberValue = tmad;
                    }
                    driverData.Add(date, new List <string>()
                    {
                        result[0].ToString(), result[1].ToString(), tmad.ToString()
                    });
                    sheet.Range[Convert.ToChar('B' + (name.Length + 2)).ToString() + lastRow1].Value        = "||";
                    sheet.Range[Convert.ToChar('B' + (name.Length + 3)).ToString() + lastRow1].Formula      = "=SUM(" + Convert.ToChar('B' + 1).ToString() + lastRow1 + ":" + Convert.ToChar('B' + (name.Length)).ToString() + lastRow1 + ")" + "/" + Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow1;
                    sheet.Range[Convert.ToChar('B' + (name.Length + 3)).ToString() + lastRow1].NumberFormat = "0.000%";

                    sheet.Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow1].HorizontalAlignment = HorizontalAlignType.Center;
                }
            }
        }
示例#16
0
        /// <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);
            }
        }
示例#17
0
        private void ClearReport(bool autoFit, Workbook book, List <ReportSheetTemplate> tplList)
        {
            for (int i = 0; i < book.Worksheets.Count &&
                 i < tplList.Count; i++)
            {
                Spire.Xls.Worksheet worksheet = book.Worksheets [i];

                ReportSheetTemplate tpl = tplList [i];
                JoinTable(worksheet, tpl);
                // Clear Data
                Clear(worksheet, tpl.startRowIndex);

                if (autoFit || tpl.autoFit)
                {
                    CellRange range = RangeHelper.GetRange(worksheet, 1, 15, 50, 100);
                    range.AutoFitColumns();


                    /*for (int j = 1; j < 100; j++)
                     * {
                     *      try
                     *      {
                     *              worksheet.AutoFitColumn (j);
                     *      }
                     *      catch (Exception e)
                     *      {
                     *              Console.Write (e) ;
                     *      }
                     * }
                     */
                }

                // .GetType ().GetMethod ("AutoFit").Invoke (range, new object[0]) ;
                // copy image

                /*for (int j = 0; tpl.pics != null &&
                 *              j < tpl.pics.Count &&
                 *              j < worksheet.Pictures.Count; j++)
                 * {
                 *      Rectangle pic = tpl.pics [i] ;
                 *
                 *
                 *      int tmp = worksheet.Pictures [i].TopRow ;
                 *      tmp = worksheet.Pictures[i].TopRowOffset;
                 *      tmp = worksheet.Pictures[i].LeftColumn;
                 *      tmp = worksheet.Pictures[i].LeftColumnOffset;
                 *      tmp = worksheet.Pictures [i].BottomRow ;
                 *      tmp = worksheet.Pictures [i].BottomRowOffset ;
                 *      tmp = worksheet.Pictures [i].RightColumn ;
                 *      tmp = worksheet.Pictures [i].RightColumnOffset ;
                 *
                 *      worksheet.Pictures [i].Left = pic.X ;
                 *      worksheet.Pictures [i].Top = pic.Y ;
                 *      worksheet.Pictures [i].Height = pic.Height ;
                 *      worksheet.Pictures [i].Width = pic.Width ;
                 *
                 * }*/
            }

            // remove warnning sheet.

/*
 *                      IEnumerator e = xlapp.ActiveWorkbook.Worksheets.GetEnumerator();
 *                      while (e.MoveNext())
 *                      {
 *                              Worksheet sheet = (Worksheet)e.Current;
 *
 *                              if (sheet.Name.IndexOf("Warning") >= 0)
 *                                      sheet.Delete();
 *                      }
 *                      ((_Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).Activate ();
 *                      ((Worksheet)xlapp.ActiveWorkbook.Worksheets [1]).get_Range("A1", "A1").Activate();
 *                      // only save activeWorkBook
 *                      xlapp.ActiveWorkbook.Save ();
 *                      // only close activeWorkbook ;
 *                      xlapp.ActiveWorkbook.Close (true, Missing.Value, Missing.Value);
 *                      // xlapp.Workbooks.Close ();
 */
        }
示例#18
0
        private void button7_Click(object sender, EventArgs e)
        {
            _PrintExcel.Workbook workbook = new _PrintExcel.Workbook();
            workbook.LoadFromFile(filename);

            _PrintExcel.Worksheet sheet = workbook.Worksheets["printout"];
            sheet.SaveToPdf(path + "Sizing Printout.pdf");
            System.Diagnostics.Process.Start("explorer.exe", path + "Sizing Printout.pdf");
            workbook.Dispose();



            //PrintDialog dialog = new PrintDialog();
            //dialog.AllowPrintToFile = true;
            //dialog.AllowCurrentPage = true;
            //dialog.AllowSomePages = true;
            //dialog.AllowSelection = true;
            //dialog.UseEXDialog = true;
            //dialog.PrinterSettings.Duplex = Duplex.Simplex;
            //dialog.PrinterSettings.PrintRange = PrintRange.SomePages;
            //workbook.PrintDialog = dialog;
            //PrintDocument pd = workbook.PrintDocument;
            //if (dialog.ShowDialog() == DialogResult.OK)
            //{ pd.Print(); }

            //Application excel = new Application();


            //_Excel.Workbook workbook = excel.Workbooks.Open(filename, ReadOnly: false, Editable: true);
            //_Excel.Worksheet worksheet = workbook.Worksheets["printout"] as _Excel.Worksheet;



            //worksheet.PrintPreview();
            //worksheet.PrintOut(From:1,To:2,Copies:1, Preview:true, Type.Missing, PrintToFile:true, Type.Missing, Type.Missing);
            //worksheet.PrintPreview();
            //PrintDialog printDlg = new PrintDialog();

            /*PrintDocument printDoc = new PrintDocument();
             * printDoc.DocumentName = "Print Document";
             * printDlg.Document = printDoc;
             * printDlg.AllowSelection = true;
             * printDlg.AllowSomePages = true;
             * if (printDlg.ShowDialog() == DialogResult.OK) printDoc.Print();*/
            //    worksheet.PrintOut(
            //1, 1, 1, Type.Missing,
            //Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //    // Cleanup:
            //    GC.Collect();
            //    GC.WaitForPendingFinalizers();

            //    Marshal.FinalReleaseComObject(worksheet);

            //    workbook.Close(false, Type.Missing, Type.Missing);
            //    Marshal.FinalReleaseComObject(workbook);

            //    excel.Quit();
            //    Marshal.FinalReleaseComObject(excel);

            /*workbook.DisplayAlerts = false;
             * excel.ActiveWorkbook.Save();
             * excel.Application.Quit();
             * excel.Quit();*/
        }
        public FileResult GetPDF()
        {
            #region "GetSessionVariables"
            string logon_user = (string)Session["logon_user"];
            long   teamId, repId;
            int    month, year;
            teamId = (long)Session["IS_Team"];
            repId  = (long)Session["IS_Rep"];
            month  = (int)Session["IS_Month"];
            year   = (int)Session["IS_Year"];
            OSC_Team           oSC_Team           = db.Teams.Find(teamId);
            OSC_Representative oSC_Representative = db.Representatives.Find(repId);
            #endregion "GetSessionVariables"
            #region "WorkbookUsingClosedXML"
            string     templateFileName = Server.MapPath("~/Templates/Template.xlsx");
            XLWorkbook workbook         = new XLWorkbook(templateFileName);
            string     exportPath       = Server.MapPath("~/Export");
            string     excelFileName    = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".xlsx";
            string     pdfFileName      = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".pdf";
            string     tempFileName     = exportPath + "/" + excelFileName;
            string     outputFileName   = exportPath + "/" + pdfFileName;
            if (System.IO.File.Exists(tempFileName))
            {
                System.IO.File.Delete(tempFileName);
            }
            workbook.SaveAs(tempFileName);
            IXLWorksheet worksheet  = workbook.Worksheet("Scorecard");
            IXLWorksheet worksheet2 = workbook.Worksheet("Workitems");
            IXLWorksheet worksheet3 = workbook.Worksheet("NPT");

            #region "Table"
            List <IndividualScorecard> list = new List <IndividualScorecard>();
            list = af.GetIndividualScorecardFull(teamId, repId, month, year);
            int     x    = 2;
            int     y    = 29;
            IXLCell cell = worksheet.Cell(y, x);
            cell.SetValue("Attendance");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Overtime");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("NPT Hours");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Processing Time");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Total Transactions");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Rate of Production");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Processing Quality");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Total Utilization");
            x   += 1;
            cell = worksheet.Cell(29, x);
            cell.SetValue("Efficiency");

            for (int i = 1; i <= list.Count; i++)
            {
                y   += 1;
                x    = 2;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].individualActivities.Attendance_Days.ToString());
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].individualActivities.Overtime_Hours.ToString());
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].individualNonProcessing.NPTHours.ToString());
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].individualBIProd.ProcessingHours.ToString());
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].individualBIProd.Count.ToString());
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].ProductivityRating.ToString() + "%");
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].individualBIQual.QualityRating.ToString() + "%");
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].TotalUtilization.ToString() + "%");
                x   += 1;
                cell = worksheet.Cell(y, x);
                cell.SetValue(list[i - 1].Efficiency.ToString() + "%");
            }
            do
            {
                x += 1;
                worksheet.Column(x).Clear();
            }while (x <= 23);
            #endregion "Table"
            #region "Highlights"
            IndividualScorecard ind = af.GetIndividualScorecard(teamId, repId, month, year);
            cell = worksheet.Cell("A47");
            cell.SetValue(ind.Highlights);
            #endregion "Highlights"
            #region "Worktypes"
            List <IndividualWorkTypes> worktypes = af.GetIndividualWorkTypes(teamId, repId, month, year);
            y = 1;
            x = 1;
            IXLCell cell2 = worksheet2.Cell(y, x);
            for (int i = 1; i <= worktypes.Count; i++)
            {
                y    += 1;
                x     = 1;
                cell2 = worksheet2.Cell(y, x);
                cell2.SetValue(worktypes[i - 1].WorkType.ToString());
                x    += 1;
                cell2 = worksheet2.Cell(y, x);
                cell2.SetValue(((int)worktypes[i - 1].Count));
            }
            do
            {
                y += 1;
                worksheet2.Row(y).Clear();
            }while(y <= 200);
            #endregion "Worktypes"
            #region "NPT"
            List <PieList> npts = af.GetIndividualNPT(teamId, repId, month, year);
            y = 1;
            x = 1;
            IXLCell cell3 = worksheet3.Cell(y, x);
            for (int i = 1; i <= npts.Count; i++)
            {
                y    += 1;
                x     = 1;
                cell3 = worksheet3.Cell(y, x);
                cell3.SetValue(npts[i - 1].Category.ToString());
                x    += 1;
                cell3 = worksheet3.Cell(y, x);
                cell3.SetValue((double)npts[i - 1].TimeSpent);
            }
            do
            {
                y += 1;
                worksheet3.Row(y).Clear();
            }while (y <= 200);
            #endregion "NPT"
            #region "SaveWorkBook""
            workbook.SaveAs(tempFileName);
            #endregion "SaveWorkBook"
            #endregion "WorkbookUsingClosedXML"
            #region "UsingSpireXLS"
            sxl.Workbook swb = new sxl.Workbook();
            swb.LoadFromFile(tempFileName);
            sxl.Worksheet sws1 = swb.Worksheets[0];
            sxl.Worksheet sws2 = swb.Worksheets[1];
            sxl.Worksheet sws3 = swb.Worksheets[2];
            sws1.TextBoxes[0].Text = oSC_Team.TeamName + " Monthly Scorecard";
            sws1.TextBoxes[1].Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName;
            sws1.TextBoxes[2].Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString();

            sxl.Chart chart1 = sws1.Charts[0];
            chart1.DataRange = sws2.Range[1, 1, sws2.Rows.Count(), 2];
            sxl.Chart chart2 = sws1.Charts[1];
            chart2.DataRange = sws3.Range[1, 1, sws3.Rows.Count(), 2];
            swb.Save();
            #endregion "UsingSpireXLS"
            #region "WorkbookUsingInterop"
            //remove this codes when 403 - 454 when closedXML can generate this to PDF
            xl.Application app = new xl.Application();
            xl.Workbook    wb  = app.Workbooks.Open(tempFileName);
            #region "Cover"
            xl.Sheets    xs      = wb.Worksheets;
            xl.Worksheet ws      = (xl.Worksheet)xs.get_Item("Scorecard");
            xl.Worksheet ws2     = (xl.Worksheet)xs.get_Item("Workitems");
            xl.Worksheet ws3     = (xl.Worksheet)xs.get_Item("NPT");
            int          txt_ctr = 1;
            foreach (xl.Shape shp in ws.Shapes)
            {
                if (shp.Type == Microsoft.Office.Core.MsoShapeType.msoTextBox)
                {
                    if (txt_ctr == 1)
                    {
                        shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Team.TeamName + " Monthly Scorecard";
                    }
                    else if (txt_ctr == 2)
                    {
                        shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName;
                        shp.ZOrder(MsoZOrderCmd.msoBringToFront);
                    }
                    else
                    {
                        shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString();
                        shp.ZOrder(MsoZOrderCmd.msoBringToFront);
                    }
                }
                txt_ctr += 1;
            }
            int cht_ctr = 1;
            foreach (xl.ChartObject cht in ws.ChartObjects())
            {
                if (cht_ctr == 1)
                {
                    xl.Range last       = ws2.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing);
                    xl.Range chartRange = ws2.get_Range("A1", last);
                    cht.Chart.SetSourceData(chartRange);
                }
                else
                {
                    xl.Range last       = ws3.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing);
                    xl.Range chartRange = ws3.get_Range("A1", last);
                    cht.Chart.SetSourceData(chartRange);
                }
                cht_ctr += 1;
            }
            #endregion "Cover"
            wb.Save();
            wb.ExportAsFixedFormat(xl.XlFixedFormatType.xlTypePDF, outputFileName, xl.XlFixedFormatQuality.xlQualityStandard);
            wb.Close();
            app.Quit();
            #endregion "WorkbookUsingInterop"

            #region "Return"
            byte[] fileBytes = System.IO.File.ReadAllBytes(outputFileName);
            string fileName  = pdfFileName;
            return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName));

            #endregion "Return"
        }
示例#20
0
        public void CreateExcel(string ref_code)
        {
            ////AdhyapanDB adhyapanDB = new AdhyapanDB();
            StudentTestInfo studentTestScore = adhyapanDB.GetScores(ref_code);

            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
            SpreadsheetInfo.FreeLimitReached += (sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;

            //var workbook = new ExcelFile();
            //string fileName = "ich_will.mp3";
            //string path = Path.Combine(Environment.CurrentDirectory, @"wwwroot\", "ReportTemplate.xlsx");
            //var worksheet = workbook.Worksheets.Add("Chart");
            var workbook = ExcelFile.Load(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/ReportTemplate.xlsx"));
            //var workbook = ExcelFile.Load(Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, @"wwwroot\", "ReportTemplate.xlsx"));


            var worksheet = workbook.Worksheets[0];

            //Data for Emotional Regulation Score
            worksheet.Cells["C10"].Value = studentTestScore.Name;
            worksheet.Cells["C12"].Value = studentTestScore.Age;
            worksheet.Cells["C14"].Value = studentTestScore.Email_ID;
            worksheet.Cells["C16"].Value = studentTestScore.Gender;
            worksheet.Cells["C18"].Value = studentTestScore.School_Name;
            worksheet.Cells["C20"].Value = studentTestScore.TestDate;
            worksheet.Cells["B68"].Value = "Emotional Parameters";
            worksheet.Cells["B69"].Value = "Self-Blame";
            worksheet.Cells["B70"].Value = "Acceptance";
            worksheet.Cells["B71"].Value = "Rumination";
            worksheet.Cells["B72"].Value = "Positive Refocusing";
            worksheet.Cells["B73"].Value = "Refocus on Planning";
            worksheet.Cells["B74"].Value = "Positive Reappraisal";
            worksheet.Cells["B75"].Value = "Putting into Perspective";
            worksheet.Cells["B76"].Value = "Catastrophizing";
            worksheet.Cells["B77"].Value = "Other_Blame";

            worksheet.Cells["C68"].Value = "Your Score";
            worksheet.Cells["C69"].Value = studentTestScore.ER_Self_Blame;
            worksheet.Cells["C70"].Value = studentTestScore.ER_Acceptance;
            worksheet.Cells["C71"].Value = studentTestScore.ER_Rumination;
            worksheet.Cells["C72"].Value = studentTestScore.ER_PositiveRefocusing;
            worksheet.Cells["C73"].Value = studentTestScore.ER_RefocusonPlanning;
            worksheet.Cells["C74"].Value = studentTestScore.ER_PositiveReappraisal;
            worksheet.Cells["C75"].Value = studentTestScore.ER_PuttingintoPerspective;
            worksheet.Cells["C76"].Value = studentTestScore.ER_Catastrophizing;
            worksheet.Cells["C77"].Value = studentTestScore.ER_Other_blame;

            worksheet.Cells["D68"].Value = "Average";
            worksheet.Cells["D69"].Value = 7.0;
            worksheet.Cells["D70"].Value = 8.5;
            worksheet.Cells["D71"].Value = 7.5;
            worksheet.Cells["D72"].Value = 9.5;
            worksheet.Cells["D73"].Value = 9.5;
            worksheet.Cells["D74"].Value = 10.1;
            worksheet.Cells["D75"].Value = 9.5;
            worksheet.Cells["D76"].Value = 6.0;
            worksheet.Cells["D77"].Value = 6.5;

            //chart for Emotional Regulation
            var emotional_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B68", "H88");

            emotional_chart.SelectData(worksheet.Cells.GetSubrange("B68", "D77"), true);
            emotional_chart.DataLabels.Show();
            emotional_chart.Title.Text       = "Emotional Regulation";
            emotional_chart.Legend.IsVisible = true;
            emotional_chart.Legend.Position  = ChartLegendPosition.Bottom;


            //Data for Verbal Score
            worksheet.Cells["B177"].Value = "Verbal Subtest Scores";
            worksheet.Cells["B178"].Value = "Information";
            worksheet.Cells["B179"].Value = "Comprehension";
            worksheet.Cells["B180"].Value = "Arithmetic";
            worksheet.Cells["B181"].Value = "Similarities";
            worksheet.Cells["B182"].Value = "Vocabulary";

            worksheet.Cells["C177"].Value = "Your Score";
            worksheet.Cells["C178"].Value = studentTestScore.Verbal_Scaled_INF;
            worksheet.Cells["C179"].Value = studentTestScore.Verbal_Scaled_COM;
            worksheet.Cells["C180"].Value = studentTestScore.Verbal_Scaled_ARI;
            worksheet.Cells["C181"].Value = studentTestScore.Verbal_Scaled_SIM;
            worksheet.Cells["C182"].Value = studentTestScore.Verbal_Scaled_VOC;

            worksheet.Cells["D177"].Value = "Average";
            worksheet.Cells["D178"].Value = 50;
            worksheet.Cells["D179"].Value = 50;
            worksheet.Cells["D180"].Value = 50;
            worksheet.Cells["D181"].Value = 50;
            worksheet.Cells["D182"].Value = 50;

            //chart for Verbal Score
            var verbal_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B177", "H189");

            verbal_chart.SelectData(worksheet.Cells.GetSubrange("B177", "D182"), true);
            verbal_chart.DataLabels.Show();
            verbal_chart.Title.Text       = "Verbal Scores";
            verbal_chart.Legend.IsVisible = true;
            verbal_chart.Legend.Position  = ChartLegendPosition.Bottom;


            //Data for Performance Score
            worksheet.Cells["B191"].Value = "Performance Subtest Scores";
            worksheet.Cells["B192"].Value = "Digital Sympbol";
            worksheet.Cells["B193"].Value = "Picture Completion";
            worksheet.Cells["B194"].Value = "Spatial";
            worksheet.Cells["B195"].Value = "Picture Arrangement";
            worksheet.Cells["B196"].Value = "Object Assembly";

            worksheet.Cells["C191"].Value = "Your Score";
            worksheet.Cells["C192"].Value = studentTestScore.Performance_Scaled_DS;
            worksheet.Cells["C193"].Value = studentTestScore.Performance_Scaled_PC;
            worksheet.Cells["C194"].Value = studentTestScore.Performance_Scaled_SPA;
            worksheet.Cells["C195"].Value = studentTestScore.Performance_Scaled_PA;
            worksheet.Cells["C196"].Value = studentTestScore.Performance_Scaled_OA;

            worksheet.Cells["D191"].Value = "Average";
            worksheet.Cells["D192"].Value = 50;
            worksheet.Cells["D193"].Value = 50;
            worksheet.Cells["D194"].Value = 50;
            worksheet.Cells["D195"].Value = 50;
            worksheet.Cells["D196"].Value = 50;

            //chart for Performance Score
            var perf_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B191", "H203");

            perf_chart.SelectData(worksheet.Cells.GetSubrange("B191", "D196"), true);
            perf_chart.DataLabels.Show();
            perf_chart.Title.Text       = "Performance Scores";
            perf_chart.Legend.IsVisible = true;
            perf_chart.Legend.Position  = ChartLegendPosition.Bottom;

            //Data for IQ Score
            worksheet.Cells["B207"].Value = "IQ Scores";
            worksheet.Cells["B208"].Value = "Verbal Score";
            worksheet.Cells["B209"].Value = "Performance";
            worksheet.Cells["B210"].Value = "Full Scale Score";

            worksheet.Cells["C207"].Value = "IQ Score";
            worksheet.Cells["C208"].Value = studentTestScore.IQ_Verbal;
            worksheet.Cells["C209"].Value = studentTestScore.IQ_Perfromance;
            worksheet.Cells["C210"].Value = studentTestScore.Full_Scale_IQ;

            worksheet.Cells["D207"].Value = "Average IQ Score";
            worksheet.Cells["D208"].Value = 100;
            worksheet.Cells["D209"].Value = 100;
            worksheet.Cells["D210"].Value = 100;

            worksheet.Cells["E207"].Value = "Percentile Score";
            worksheet.Cells["E208"].Value = studentTestScore.Percentile_Verbal;
            worksheet.Cells["E209"].Value = studentTestScore.Percentile_Performance;
            worksheet.Cells["E210"].Value = studentTestScore.Full_Percentile;

            //chart for IQ Score
            var IQ_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B207", "H221");

            IQ_chart.SelectData(worksheet.Cells.GetSubrange("B207", "E210"), true);
            IQ_chart.DataLabels.Show();
            IQ_chart.Title.Text       = "Your IQ Scores";
            IQ_chart.Legend.IsVisible = true;
            IQ_chart.Legend.Position  = ChartLegendPosition.Bottom;

            // Make entire sheet print on a single page.
            //worksheet.PrintOptions.FitWorksheetWidthToPages = 1;
            workbook.Save(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx"));
            //workbook.Save(Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, @"wwwroot\pdf\", "Chart" + ref_code.ToString() + ".xlsx"));
            //workbook.Save("./wwwroot/pdf/Chart" + ref_code.ToString() + ".xlsx");


            Spire.Xls.Workbook pdfWorkbook = new Spire.Xls.Workbook();
            pdfWorkbook.LoadFromFile(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx"));

            pdfWorkbook.SaveToFile(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Results" + studentTestScore.Student_TestID + ".pdf"), Spire.Xls.FileFormat.PDF);

            Execute(ref_code, studentTestScore).Wait();
            if ((System.IO.File.Exists(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx"))))
            {
                System.IO.File.Delete(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx"));
            }
            if ((System.IO.File.Exists(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Results" + studentTestScore.Student_TestID + ".pdf"))))
            {
                System.IO.File.Delete(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Results" + studentTestScore.Student_TestID + ".pdf"));
            }
        }
示例#21
0
        //////////////////////////
        private void exportsum(classinfo cl1, View_class_exp vst1, V_tea_course vcourse, int poscol)
        {
            String dirsave = null;
            List <View_class_student> lstwei = new List <View_class_student>();

            // List<View_class_student> lstall = null;
            saveFileDialog2.DefaultExt = ".xlsx";
            saveFileDialog2.Filter     = "EXCEL file|*.xlsx";
            if (saveFileDialog2.ShowDialog() == DialogResult.OK)
            {
                string localFilePath = saveFileDialog2.FileName.ToString();
                // dirsave = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
                dirsave = localFilePath;
            }

            //////////////////////excel

            Spire.Xls.Workbook wb = new Spire.Xls.Workbook();
            //清除默认的工作表
            wb.Worksheets.Clear();
            //添加一个工作表并指定表名
            Worksheet sheet = wb.Worksheets.Add("score");

            sheet.Range[2, 2].Text = "序号";
            sheet.Range[3, 2].Text = "分值";
            sheet.Range[4, 2].Text = "指标";
            sheet.Range[5, 1].Text = "学号";
            sheet.Range[5, 2].Text = "姓名";

            int    intobjective = vcourse.numobjective;
            String expobjective = vst1.objective;

            String[] strexpobj = expobjective.Split('|');

            for (int i = 1; i <= intobjective + 1; i++)
            {
                if (i < intobjective + 1)
                {
                    sheet.Range[4, poscol + i - 1].Text = i.ToString();
                }
                else
                {
                    sheet.Range[4, poscol + i - 1].Text = "已交";
                }
            }
            int irow           = 6;
            int icol           = poscol;
            var questionQuery1 = from o in pp.context.View_class_student
                                 where o.classid == cl1.classid
                                 orderby o.classno
                                 select o;
            List <View_class_student> lstall = null;

            if (questionQuery1.Count <View_class_student>() > 0)
            {
                lstall = questionQuery1.ToList <View_class_student>();
            }
            ///
            ///
            if (lstall != null)
            {
                foreach (View_class_student itvst in lstall)
                {
                    sheet.Range[irow, 1].Text = itvst.studentid;
                    sheet.Range[irow, 2].Text = itvst.name;


                    var questionQuery2 = from o in pp.context.studreport
                                         where o.classid == cl1.classid && o.expid == vst1.expid && o.stid == itvst.studentid
                                         select o;

                    studreport isturep = null;
                    if (questionQuery2.Count <studreport>() > 0)
                    {
                        isturep = questionQuery2.First <studreport>();
                        String   strmarks = isturep.score;
                        String[] strmark  = strmarks.Split('|');
                        for (int i = 1; i <= intobjective + 1; i++)
                        {
                            if (i < intobjective + 1)
                            {
                                sheet.Range[irow, poscol + i - 1].Value2 = 0;
                                for (int j = 1; j < strexpobj.Length; j++)
                                {
                                    if (i == int.Parse(strexpobj[j]))
                                    {
                                        sheet.Range[irow, poscol + i - 1].Value2 = int.Parse(strmark[j]);
                                    }
                                }
                            }
                            // else
                            // sheet.Range[irow, poscol + i - 1].Text = "NO";
                        }
                    }
                    else
                    {
                        for (int i = 1; i <= intobjective + 1; i++)
                        {
                            if (i < intobjective + 1)
                            {
                                sheet.Range[irow, poscol + i - 1].Value2 = 0;
                            }
                            else
                            {
                                sheet.Range[irow, poscol + i - 1].Text = "NO";
                            }
                        }
                    }



                    irow = irow + 1;
                } //foreach (View_class_stu
            }     /// end if( lstall !=null)
               ///



            sheet.AllocatedRange.AutoFitColumns();
            wb.SaveToFile(dirsave, ExcelVersion.Version2013);
            MessageBox.Show("excel生成好了");
        }
        private void bt_baocao_Click(object sender, EventArgs e)
        {
            //Create a new workbook
            Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();

            //Initialize worksheet
            workbook.CreateEmptySheets(1);
            Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
            string        conString   = @"Data Source=DESKTOP-RGHAECC;Initial Catalog=SucKhoeNhanVien_PNT;Integrated Security=True";
            SqlConnection sqlCon      = new SqlConnection(conString);

            sqlCon.Open();

            SqlDataAdapter da = new SqlDataAdapter("SELECT LichSuBenh.Id_LichSuBenh,NhanVien.MaNV,NhanVien.TenNV,Khoa.TenKhoa,ChucVu.TenChucVu,LichSuBenh.NgayKham,LichSuBenh.PhanLoaiSK,LichSuBenh.BenhKhac from NhanVien, LichSuBenh, Khoa , ChucVu where NhanVien.IdNhanVien = LichSuBenh.IdNhanVien And NhanVien.Khoa = Khoa.IdKhoa And NhanVien.IdChucVu = ChucVu.IdChucVu", sqlCon);

            System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
            da.Fill(dtMainSQLData);
            DataColumnCollection dcCollection = dtMainSQLData.Columns;

            // Export Data into EXCEL Sheet
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            ExcelApp.Columns.ColumnWidth = 20;

            //Create a new workbook
            // Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();

            //Initialize worksheet
            // workbook.CreateEmptySheets(1);
            //Spire.Xls.Worksheet sheet = workbook.Worksheets[0];

            //Set sheet name
            sheet.Name = "Chart data";

            //Set the grid lines invisible
            sheet.GridLinesVisible = false;

            //Create a chart
            Spire.Xls.Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D);

            //Set region of chart data
            chart.DataRange           = sheet.Range["B2:B5"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn  = 1;
            chart.TopRow      = 6;
            chart.RightColumn = 9;
            chart.BottomRow   = 25;

            //Chart title
            chart.ChartTitle            = "Sales by year";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size   = 12;

            //Initialize the chart series
            Spire.Xls.Charts.ChartSerie cs = chart.Series[0];

            //Chart Labels resource
            cs.CategoryLabels = sheet.Range["A2:A5"];

            //Chart value resource
            cs.Values = sheet.Range["B2:B5"];

            //Set the value visible in the chart
            cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;

            //Year
            sheet.Range["A1"].Value = "Year";
            sheet.Range["A2"].Value = "2002";
            sheet.Range["A3"].Value = "2003";
            sheet.Range["A4"].Value = "2004";
            sheet.Range["A5"].Value = "2005";

            //Sales
            sheet.Range["B1"].Value       = "Sales";
            sheet.Range["B2"].NumberValue = 4000;
            sheet.Range["B3"].NumberValue = 6000;
            sheet.Range["B4"].NumberValue = 7000;
            sheet.Range["B5"].NumberValue = 8500;

            //Style
            sheet.Range["A1:B1"].Style.Font.IsBold = true;
            sheet.Range["A2:B2"].Style.KnownColor  = ExcelColors.LightYellow;
            sheet.Range["A3:B3"].Style.KnownColor  = ExcelColors.LightGreen1;
            sheet.Range["A4:B4"].Style.KnownColor  = ExcelColors.LightOrange;
            sheet.Range["A5:B5"].Style.KnownColor  = ExcelColors.LightTurquoise;

            //Border
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color        = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle    = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color     = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color       = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle   = LineStyleType.Thin;
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color      = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle  = LineStyleType.Thin;

            //Number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
            chart.PlotArea.Fill.Visible             = false;

            //Save the file
            // workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003);

            //Launch the file
            //System.Diagnostics.Process.Start("Sample.xls");



            // ExcelApp.Cells.CopyFromRecordset(objRS);
            for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
            {
                for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                {
                    if (i == 1)
                    {
                        ExcelApp.Cells[i, j]                     = dcCollection[j - 1].ToString();
                        ExcelApp.Cells[i, j].Font.Bold           = true;
                        ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                        Microsoft.Office.Interop.Excel.Range formatRange;
                        formatRange = ExcelApp.Cells[i, j];
                        formatRange.Interior.Color = System.Drawing.
                                                     ColorTranslator.ToOle(System.Drawing.Color.CornflowerBlue);
                    }

                    else
                    {
                        ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
                    }
                    ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                }
            }
            //Save the file
            workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003);

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
            ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Oanh\\Desktop\\BaoCaoSK.xls");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
        }