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()));
}
/// <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");
}
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));
}
}
}
}
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();
}
}
}
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);
}
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"));
}
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自己设置
}
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()));
}
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.
}
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();
}
}
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);
}
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;
}
}
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();
}
}
/// <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();
}
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;
}
}
}
/// <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);
}
}
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 ();
*/
}
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"
}
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"));
}
}
//////////////////////////
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();
}