/// <summary> /// 创建工作单多条件查询 /// </summary> /// <returns></returns> public List <WorkSheet> GetWorkSelect(string number, string TaskName) { string sql = string.Format("select Work_Order_Number,Responsible_Person,Work_CreatNumber,Worksheet_CreatDate,Work_DealPeople,Work_UploadType,Work_Describle,Work_State, Work_DueTime, Work_DueDeal, Work_TaskName, Work_Reminderlevel, Work_ReminderTime from MS_Worksheet where Work_Order_Number like '%{0}%'and Work_TaskName like '%{1}%'", number, TaskName); var table = DBHelper.Select(sql); List <WorkSheet> list = new List <WorkSheet>(); foreach (DataRow item in table.Rows) { WorkSheet user = new WorkSheet { WorkOrderNumber = item["Work_Order_Number"].ToString(), ResponsiblePerson = item["Responsible_Person"].ToString(), WorkDealPeople = item["Work_DealPeople"].ToString(), WorksheetCreatDate = item["Worksheet_CreatDate"].ToString(), WorkUploadType = item["Work_UploadType"].ToString(), MyProWorkDescribleperty = item["Work_Describle"].ToString(), WorkState = item["Work_State"].ToString(), WorkCreatNumber = item["Work_CreatNumber"].ToString(), WorkDueTime = item["Work_DueTime"].ToString(), WorkDueDeal = item["Work_DueDeal"].ToString(), WorkTaskName = item["Work_TaskName"].ToString(), WorkReminderlevel = item["Work_Reminderlevel"].ToString(), WorkReminderTime = item["Work_ReminderTime"].ToString() }; list.Add(user); } return(list); }
/// <summary> /// 工作单创建明细加载数据库 /// </summary> /// <returns></returns> public List <WorkSheet> getCha() { string sql = string.Format("select * from MS_Worksheet"); var table = DBHelper.Select(sql); List <WorkSheet> list = new List <WorkSheet>(); foreach (DataRow item in table.Rows) { WorkSheet user = new WorkSheet { WorksheetID = Convert.ToInt32(item["WorksheetID"]), WorkOrderNumber = item["Work_Order_Number"].ToString(), ResponsiblePerson = item["Responsible_Person"].ToString(), WorkDealPeople = item["Work_DealPeople"].ToString(), WorksheetCreatDate = item["Worksheet_CreatDate"].ToString(), WorkUploadType = item["Work_UploadType"].ToString(), MyProWorkDescribleperty = item["Work_Describle"].ToString(), WorkState = item["Work_State"].ToString(), WorkCreatNumber = item["Work_CreatNumber"].ToString(), WorkDueTime = item["Work_DueTime"].ToString(), WorkDueDeal = item["Work_DueDeal"].ToString(), WorkTaskName = item["Work_TaskName"].ToString(), WorkReminderlevel = item["Work_Reminderlevel"].ToString(), WorkReminderTime = item["Work_ReminderTime"].ToString() }; list.Add(user); } return(list); }
/// <summary> /// ПЕРЕНОС СЛОВ В ЯЧЕЙКЕ /// </summary> /// <param name="range"></param> /// <param name="Value"></param> public void SetWrapText(string range, bool Value) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); object[] args = new object[] { Value }; Range.GetType().InvokeMember("WrapText", BindingFlags.SetProperty, null, Range, args); }
private static void CreateMatrixHeaderRow(WorkSheet sheet, WfActivityMatrixResourceDescriptor activityMatrix, int startRowIndex) { Row headerRow = new Row(startRowIndex); headerRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid); headerRow.Style.Border.Top.Style = ExcelBorderStyle.Thin; headerRow.Style.Border.Top.Color.SetColor(Color.Black); headerRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; headerRow.Style.Border.Bottom.Color.SetColor(Color.Black); headerRow.Style.Border.Left.Style = ExcelBorderStyle.Thin; headerRow.Style.Border.Left.Color.SetColor(Color.Black); headerRow.Style.Font.Bold = true; sheet.Rows.Add(headerRow); int columnIndex = 1; foreach (SOARolePropertyDefinition dimension in activityMatrix.PropertyDefinitions) { sheet.Cells[headerRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name; sheet.Names.Add(CellAddress.Parse(columnIndex, headerRow.Index).ToString(), dimension.Name); columnIndex++; } }
public FileXlsxUI(string fileToRead, string fileToSave, uint row, char column) { if (!string.IsNullOrEmpty(fileToRead) && !fileToRead.Equals(fileToSave)) { FileToRead = $"{fileToRead}.xlsx"; FileToSave = $"{fileToSave}.xlsx"; if (File.Exists(FileToRead)) { workBook = WorkBook.Load(FileToRead); workSheet = workBook.DefaultWorkSheet; } else { workBook = WorkBook.Create(ExcelFileFormat.XLSX); workSheet = workBook.CreateWorkSheet("Main"); } Row = row == 0 ? row + 1 : row; Column = column; RangeOfColumns = Column; } else { throw new ArgumentException("Name of a file can not be null or empty and have the same name as another file!"); } }
public void printXLS(string filename, List<List<int>> allIterations, List<string> files) { WorkBook workbook = WorkBook.Load(filename); WorkSheet sheet = workbook.DefaultWorkSheet; for (int i = 0; i < allIterations.Count; i++) { sheet.SetCellValue(0, i, files[i]); for (int j = 1; j < allIterations[i].Count + 1; j++) { sheet.SetCellValue(j, i, allIterations[i][j - 1]); } } //Save Changes workbook.SaveAs(filename); /* //iterate over range of cells foreach (var cell in range) { Console.WriteLine("Cell {0} has value '{1}", cell.RowIndex, cell.Value); if (cell.IsNumeric) { //Get decimal value to avoid floating numbers precision issue total += cell.DecimalValue; } } //check formula evaluation if (sheet["A11"].DecimalValue == total) { Console.WriteLine("Basic Test Passed"); } */ }
private void Form1_Load(object sender, EventArgs e) { workbook = WorkBook.Create(ExcelFileFormat.XLSX); workbook.Metadata.Author = "User"; sheet = workbook.CreateWorkSheet("new_sheet"); UpdateTable(); }
public static Range Parse(WorkSheet workSheet, string rangeAddress) { rangeAddress.IsNullOrEmpty().TrueThrow<ArgumentNullException>("Rang地址不能为空"); int _StartRow, _StartColumn, _EndRow, _EndColumn; ExcelHelper.GetRowColFromAddress(rangeAddress, out _StartRow, out _StartColumn, out _EndRow, out _EndColumn); return new Range() { _WorkSheet = workSheet, StartRow = _StartRow, StartColumn = _StartColumn, EndRow = _EndRow, EndColumn = _EndColumn }; }
/// <summary> /// 读取数据到二维数组 /// </summary> /// <param name="sheetName">WorkSheet名</param> /// <param name="range">读取的区域,如"D1:F15"</param> /// <returns>一个二维数组</returns> /// 例如:Object[,] val = excel.getValues("Sheet1", "D1:D18"); public Object[,] getValues(string sheetName, string range) { Object[,] rtnValue; try { WorkSheet = WorkSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, WorkSheets, new object[] { sheetName }); Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); if (range == "A1:A1") { //定义一个只包含一个[1,1]元素的二维数组,数组下标从1开始 Array array = Array.CreateInstance(typeof(Object), new int[] { 1, 1 }, new int[] { 1, 1 }); //把range中的唯一元素赋值给数组 array.SetValue(Range.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, Range, null), 1, 1); rtnValue = (Object[, ])array; //把Array类型的二维数组转换成Object类型后赋值rtnValue } else { rtnValue = (Object[, ])Range.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, Range, null); } return(rtnValue); } catch (Exception) { return(null); } }
private void openToolStripMenuItem_Click(object sender, EventArgs e) { string messageBoxText = "Do you want to save changes?"; string caption = "MyExcel"; MessageBoxButtons button = MessageBoxButtons.YesNo; MessageBoxIcon icon = MessageBoxIcon.Warning; // Display message box DialogResult result = MessageBox.Show(messageBoxText, caption, button, icon); EventArgs y = new EventArgs(); // Process message box results switch (result) { case DialogResult.Yes: saveToolStripMenuItem_Click(sender, y); //invoking save dialog method break; case DialogResult.No: //not to save changes break; } openFileDialog1.Filter = "xlsx files (*.xlsx)|*.xlsx|xls files (*.xls)|*.xls"; openFileDialog1.FilterIndex = 2; openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog() == DialogResult.OK) { workbook = WorkBook.Load(openFileDialog1.FileName); sheet = workbook.WorkSheets.First(); UpdateTable(); } }
/// <summary> /// 缩放 /// </summary> /// <param name="Percent"></param> public void SetZoom(int Percent) { //Range.PageSetup.Zoom(打印的范围) object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty, null, WorkSheet, null); PageSetup.GetType().InvokeMember("Zoom", BindingFlags.SetProperty, null, PageSetup, new object[] { Percent }); }
/// <summary> /// 设置工作表的大小 /// </summary> /// <param name="Size"></param> public void SetPaperSize(xlPaperSize Size) { //Range.PageSetup.PaperSize(纸张尺寸) object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty, null, WorkSheet, null); PageSetup.GetType().InvokeMember("PaperSize", BindingFlags.SetProperty, null, PageSetup, new object[] { Size }); }
/// <summary> /// 设置单元格的背景颜色 /// </summary> /// <param name="range"></param> /// <param name="r"></param> public void SetColor(string range, int r) { //Range.Interior.ColorIndex Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); Interior = Range.GetType().InvokeMember("Interior", BindingFlags.GetProperty, null, Range, null); Range.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty, null, Interior, new object[] { r }); }
private static void BuildRows(WorkSheet sheet, DataTable table) { for (int rowIndex = StartRow + 1; rowIndex < sheet.Rows.Count; rowIndex++) { DataRow row = null; for (int columnIndex = 1; columnIndex <= table.Columns.Count; columnIndex++) { object cellValue = sheet.Cells[rowIndex, columnIndex].Value; if (cellValue != null) { if (row == null) { row = table.NewRow(); table.Rows.Add(row); } row[table.Columns[columnIndex - 1].ColumnName] = cellValue; } else { if (columnIndex == 1) { break; } } } } }
public void WriteToSheet(List <string> headings, List <string[]> collection, string sheetName = "Sheet1") { if (Excel == null) { CreateDocument(); } if (WorkSheet == null) { CreateWorkSheet(sheetName); } WorkSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; WorkSheet.Row(1).Style.Font.Bold = true; for (int i = 1; i <= headings.Count; i++) { WorkSheet.Cells[1, i].Value = headings.ElementAt(i - 1); WorkSheet.Column(i).AutoFit(); } for (int i = 2; i <= collection.Count + 1; i++) { var count = 1; foreach (var item in collection.ElementAt(i - 2)) { WorkSheet.Cells[i, count].Value = item; count++; } } }
private void ReadAppsExcel() { try { WorkBook wb = WorkBook.Load(FFile); WorkSheet ws = wb.WorkSheets.First(); foreach (RangeRow row in ws.Rows) { if (row.First().Value.ToString().Trim().Equals("Type")) { continue; } else { Console.WriteLine("New apps Row >> "); List <IronXL.Cell> rowDatas = row.ToList(); StudentModel st; st = new StudentModel(rowDatas[3].Value.ToString(), rowDatas[4].Value.ToString(), rowDatas[2].Value.ToString()); StudentModel tok = st; mouldedStudents.Add(tok); Console.WriteLine("$$$ " + st.Name + " : " + st.Phone + " : " + st.Email + " : " + st.Params + " : " + st.Errors); st = null; } } } catch (Exception e) { Console.WriteLine("Error Apps : " + e.Message); } }
/// <summary> /// ЗАПИСАТЬ ЗНАЧЕНИЕ В ЯЧЕЙКУ /// </summary> /// <param name="range"></param> /// <param name="value"></param> public void SetValue(string range, string value) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); value = value.Replace("=", "-"); Range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, Range, new object[] { value }); }
private void buttonOpenAndSaveFile_Click(object sender, EventArgs e) { if (openExceFileDialog.ShowDialog() == DialogResult.OK) { WorkBook workbook = WorkBook.Load(openExceFileDialog.FileName); DataTable dt = PrepareDataTable(); FillDataTableData(dt, 10); WorkSheet sheet = workbook.Sheets["任务单"]; if (sheet != null && sheet.Tables.Count > 0) { Table table = sheet.Tables[0]; table.FillData(dt.DefaultView); table.FillData(dt.DefaultView, (cell, cellParameters) => { cell.Value = cellParameters.PropertyValue; //cell.Style.Font.Color.SetColor(Color.Blue); cell.Style.Font.Color.SetColor(Color.White); cell.Style.Fill.SetBackgroundColor(Color.Blue, ExcelFillStyle.Solid); }); } string dir = Path.GetDirectoryName(Application.ExecutablePath); string path = Path.Combine(dir, "output.xlsx"); workbook.Save(path); Shell32.Shell shell = new Shell32.Shell(); shell.Open(path); } }
/// <summary> /// УСТАНОВКА НАПРАВЛЕНИЯ ТЕКСТА /// </summary> /// <param name="range"></param> /// <param name="Orientation"></param> public void SetTextOrientation(string range, int Orientation) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); object[] args = new object[] { Orientation }; Range.GetType().InvokeMember("Orientation", BindingFlags.SetProperty, null, Range, args); }
/// <summary> /// 工作单申请保存按钮 /// </summary> /// <returns></returns> private void Worksqing_Baoc() { BLL.WorkOrderBLL orderBLL = new WorkOrderBLL(); JavaScriptSerializer js = new JavaScriptSerializer(); WorkSheet workSheet = new WorkSheet(); workSheet.WorksheetID = Convert.ToInt32(HttpContext.Current.Request.Form["WorksheetID"]); workSheet.WorkOrderNumber = HttpContext.Current.Request.Form["WorkOrderNumber"]; //工作单单号 workSheet.ResponsiblePerson = HttpContext.Current.Request.Form["ResponsiblePerson"]; //责任人/处理人姓名 workSheet.WorkCreatNumber = HttpContext.Current.Request.Form["WorkCreatNumber"]; //创建人工号 workSheet.WorksheetCreatDate = HttpContext.Current.Request.Form["WorksheetCreatDate"]; //工作单创建日期 workSheet.WorkDealPeople = HttpContext.Current.Request.Form["WorkDealPeople"]; //处理人(默认为当前创建人,可以是上级) workSheet.WorkUploadType = HttpContext.Current.Request.Form["WorkUploadType"]; ////上传类型 workSheet.MyProWorkDescribleperty = HttpContext.Current.Request.Form["MyProWorkDescribleperty"]; //描述 workSheet.WorkState = HttpContext.Current.Request.Form["WorkState"]; //状态 workSheet.WorkDueTime = HttpContext.Current.Request.Form["WorkDueTime"]; //到期时间 workSheet.WorkDueDeal = HttpContext.Current.Request.Form["WorkDueDeal"]; //到期处理 workSheet.WorkTaskName = HttpContext.Current.Request.Form["WorkTaskName"]; //任务名称 workSheet.WorkReminderlevel = HttpContext.Current.Request.Form["WorkReminderlevel"]; //催办等级 workSheet.WorkReminderTime = HttpContext.Current.Request.Form["WorkReminderTime"]; //催办时效 var obj = new { msg = "保存成功!", code = 200 }; if (!orderBLL.Worksqing_Baoc(workSheet)) { obj = new { msg = "保存失败!", code = 201 }; } string rs = js.Serialize(workSheet); HttpContext.Current.Response.Write(rs); HttpContext.Current.Response.End(); }
public void WriteData()//Insert data to excel file with a signle name { string fname, lname; Console.WriteLine("What is your first name?"); fname = Console.ReadLine(); Console.WriteLine("What is your last name?"); lname = Console.ReadLine(); //Ask for First Name and Last Name WorkBook workbook = WorkBook.Load(@"..\..\App-data\Book1.xlsx"); //Open the file WorkSheet sheet = workbook.GetWorkSheet("Sheet1"); //Open the sheet //Initalize first row with First name and Last name labels sheet["A1"].Value = "First name"; sheet["B1"].Value = "Last name"; //Find the next blank row in the file to insert the name while ((sheet["A" + count].Value) != "" && (sheet["B" + count].Value) != "") { count++; } //Insert the name in the avalavable row sheet["A" + count].Value = fname; sheet["B" + count].Value = lname; count++;//Keep track of the available row workbook.SaveAs(@"..\..\App-data\Book1.xlsx"); }
//УДАЛЕНИЕ ПРИМЕЧАНИЯ public void DeleteComment(string range) { //Range.ClearComment Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); Range.GetType().InvokeMember("ClearComments", BindingFlags.InvokeMethod, null, Range, null); }
//УСТАНОВИТЬ ВЫСОТУ СТРОК public void SetRowHeight(string range, double Height) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); object[] args = new object[] { Height }; Range.GetType().InvokeMember("RowHeight", BindingFlags.SetProperty, null, Range, args); }
/// <summary> /// УСТАНОВИТЬ ВЫРАВНИВАНИЕ В ЯЧЕЙКЕ ПО ГОРИЗОНТАЛИ /// </summary> /// <param name="range"></param> /// <param name="Alignment"></param> public void SetHorisontalAlignment(string range, int Alignment) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); object[] args = new object[] { Alignment }; Range.GetType().InvokeMember("HorizontalAlignment", BindingFlags.SetProperty, null, Range, args); }
/// <summary> /// ЧТЕНИЕ ДАННЫХ ИЗ ВЫБРАННОЙ ЯЧЕЙКИ /// </summary> /// <param name="range">ячейка</param> /// <returns></returns> public string GetValue(string range) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); return(Range.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, Range, null).ToString()); }
private void Btn_Export_Click(object sender, EventArgs e) { try { WorkBook ExcelPodatak = WorkBook.Create(ExcelFileFormat.XLS); ExcelPodatak.Metadata.Author = "IronXL"; WorkSheet SpremiPodatak = ExcelPodatak.CreateWorkSheet("new_sheet"); SpremiPodatak["A1"].Value = "Mjesec"; SpremiPodatak["B1"].Value = cb_VMjesec.SelectedItem.ToString(); SpremiPodatak["A2"].Value = "Godina"; SpremiPodatak["B2"].Value = cb_Godina.SelectedItem.ToString(); SpremiPodatak["A3"].Value = "Dnevno Staro Stanje"; SpremiPodatak["B3"].Value = tb_Staro.Text; SpremiPodatak["A4"].Value = "Dnevno Novo Stanje"; SpremiPodatak["B4"].Value = tb_novo.Text; SpremiPodatak["A5"].Value = "Dnevna Razlika"; SpremiPodatak["B5"].Value = tb_RazlikaVoda.Text; SpremiPodatak["B6"].Value = lbl_VodaIzracun.Text; ExcelPodatak.SaveAs(@"C:\Users\Dado\Desktop\Voda.xls"); Form DatotekaSpremljena = new DatotekaSpremljena(); DatotekaSpremljena.Show(); } catch { MessageBox.Show("Zatvorite postojecu datoteku prvo"); } }
/// <summary> /// УСТАНОВИТЬ ШИРИНУ СТОЛБЦОВ /// </summary> /// <param name="range">ячейка</param> /// <param name="Width">чирина</param> public void SetColumnWidth(string range, double Width) { Range = WorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, WorkSheet, new object[] { range }); object[] args = new object[] { Width }; Range.GetType().InvokeMember("ColumnWidth", BindingFlags.SetProperty, null, Range, args); }
public void CreateExcelReport <T>(List <T> toReport, string path) { WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX); WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("main_sheet"); var props = typeof(T).GetProperties().Where((x) => FieldType.Split(' ').Contains(x.Name)).ToList(); int propsLength = props.Capacity; string[] cells = { "A", "B", "C", "D", "E", "F", "G" }; int j = 0; string cell; foreach (var p in toReport) { for (int i = 1; i < propsLength; i++) { cell = cells[i - 1]; cell += j + 1; xlsSheet[cell].Value = props[i - 1].GetValue(p).ToString(); } j++; } xlsxWorkbook.SaveAs(path + ".xlsx"); }
public void WriteUsersToFile() { var saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel|*.xls|Excel 2010|*.xlsx"; if (saveFileDialog.ShowDialog() == DialogResult.Cancel) { return; } WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLS); xlsWorkbook.Metadata.Author = "KLB"; WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("new_sheet"); var users = _userRepository.GetAllUsers(); xlsSheet["A1"].Value = "Last Name"; xlsSheet["B1"].Value = "Year of Connection"; xlsSheet["C1"].Value = "Phone Number"; xlsSheet["D1"].Value = "Address"; for (int i = 2; i <= users.Count + 1; i++) { xlsSheet[$"A{i}"].Value = users[i - 2].SecondName; xlsSheet[$"B{i}"].Value = users[i - 2].YearOfConnection; xlsSheet[$"C{i}"].Value = users[i - 2].PhoneNumber; xlsSheet[$"D{i}"].Value = users[i - 2].Address.ToString(); } xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600"); xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double; xlsWorkbook.SaveAs(saveFileDialog.FileName); }
private void Btn_ExportPlin_Click(object sender, EventArgs e) { try { if (tb_Cijena.Text == "" || tb_Tvrtka.Text == "") { Form PopunitePolja = new PopunitePolja(); PopunitePolja.Show(); } else { WorkBook ExcelPodatak = WorkBook.Create(ExcelFileFormat.XLS); ExcelPodatak.Metadata.Author = "IronXL"; WorkSheet SpremiPodatak = ExcelPodatak.CreateWorkSheet("new_sheet"); SpremiPodatak["A1"].Value = "Mjesec"; SpremiPodatak["B1"].Value = cb_SMjesec.SelectedItem.ToString(); SpremiPodatak["A2"].Value = "Godina"; SpremiPodatak["B2"].Value = cb_Godina.SelectedItem.ToString(); SpremiPodatak["A3"].Value = "Tvrtka"; SpremiPodatak["B3"].Value = tb_Tvrtka.Text; SpremiPodatak["A4"].Value = "Cijena Računa"; SpremiPodatak["B4"].Value = tb_Cijena.Text; ExcelPodatak.SaveAs(@"C:\Users\Dado\Desktop\Smece.xls"); Form DatotekaSpremljena = new DatotekaSpremljena(); DatotekaSpremljena.Show(); } } catch { MessageBox.Show("Zatvorite postojeću datoteku prvo"); } }
//Write Data to Excel file with multiple times public void WriteData(int times) { //Open the file and the sheet WorkBook workbook = WorkBook.Load(@"..\..\App-data\Book1.xlsx"); WorkSheet sheet = workbook.GetWorkSheet("Sheet1"); //Initalize the first row sheet["A1"].Value = "First name"; sheet["B1"].Value = "Last name"; for (var i = 0; i < times; i++) { string fname, lname; Console.WriteLine("What is your first name?"); fname = Console.ReadLine(); Console.WriteLine("What is your last name?"); lname = Console.ReadLine(); //Find the next available row while ((sheet["A" + count].Value) != "" && (sheet["B" + count].Value) != "") { count++; } //Insert the data sheet["A" + count].Value = fname; sheet["B" + count].Value = lname; count++; } //Save the changes workbook.SaveAs(@"..\..\App-data\Book1.xlsx"); }
/// <summary> /// 将WfActivityMatrixResourceDescriptor填充到Excel的WorkBook中 /// </summary> /// <param name="workBook"></param> /// <param name="activityMatrix"></param> public static void FillActivityMatrixResourceDescriptor(this WorkBook workBook, WfActivityMatrixResourceDescriptor activityMatrix) { workBook.NullCheck("workBook"); workBook.Sheets.Remove("Matrix"); WorkSheet sheet = new WorkSheet(workBook, "Matrix"); workBook.Sheets.Add(sheet); sheet.FillActivityMatrixResourceDescriptor(activityMatrix); }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet/sheetData</para> /// </summary> private XElement WriteWorkSheet_sheetData(WorkSheet sheet, List<Comment> sheetComments, List<Cell> sheetLinks) { XElement sheetData = new XElement(ExcelCommon.Schema_WorkBook_Main + "sheetData"); //用于保存生成的行信息 SortedDictionary<int, XElement> rowDict = new SortedDictionary<int, XElement>(); //用于保存行内列信息 Dictionary<int, SortedDictionary<int, XElement>> rowCellsDict = new Dictionary<int, SortedDictionary<int, XElement>>(); foreach (Row r in sheet.Rows) { rowDict.Add(r.Index, WriteWorkSheet_sheetData_row(sheet, r)); rowCellsDict.Add(r.Index, new SortedDictionary<int, XElement>()); } foreach (var cell in sheet.Cells) { SortedDictionary<int, XElement> rcDict = rowCellsDict[cell.Row.Index]; WriteWorkSheet_sheetData_c(sheet, rcDict, cell); if (cell._Comment != null) { sheetComments.Add(cell._Comment); } if (cell._Hyperlink != null) { sheetLinks.Add(cell); } } foreach (var rowKV in rowDict) { var cellDict = rowCellsDict[rowKV.Key]; foreach (var cellKV in cellDict) { rowKV.Value.Add(cellKV.Value); } sheetData.Add(rowKV.Value); } return sheetData; }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet/sheetFormatPr</para> /// </summary> private XElement WriteWorkSheet_sheetFormatPr(WorkSheet sheet) { /* "baseColWidth", (double) "defaultColWidth", (double) "defaultRowHeight", (double) "customHeight", (bool) "zeroHeight", (bool) "thickTop", (bool) "thickBottom", (bool) "outlineLevelRow", (bool) "outlineLevelCol", (bool) "dyDescent" (bool) */ XElement sheetFormatPr = null; if (sheet.DefaultColumnWidth != ExcelCommon.WorkSheet_DefaultColumnWidth || sheet.CustomHeight) { sheetFormatPr = new XElement(ExcelCommon.Schema_WorkBook_Main + "sheetFormatPr"); if (sheet.DefaultColumnWidth != ExcelCommon.WorkSheet_DefaultColumnWidth) sheetFormatPr.Add(new XAttribute("defaultColWidth", sheet.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); if (sheet.CustomHeight) { sheetFormatPr.Add(new XAttribute("customHeight", "1")); if (sheet.DefaultRowHeight != ExcelCommon.WorkSheet_DefaultRowHeight) sheetFormatPr.Add(new XAttribute("defaultRowHeight", sheet.DefaultRowHeight.ToString(CultureInfo.InvariantCulture))); } } return sheetFormatPr; }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet/sheetViews</para> /// </summary> private XElement WriteWorkSheet_sheetViews(WorkSheet sheet) { XElement sheetViews = new XElement(ExcelCommon.Schema_WorkBook_Main + "sheetViews"); XElement sheetView = new XElement(ExcelCommon.Schema_WorkBook_Main + "sheetView", new XAttribute("workbookViewId", "0")); WriteWorkSheet_sheetViews_Attributes(sheetView, sheet.SheetView); sheetViews.Add(sheetView); return sheetViews; }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet/dimension</para> /// </summary> private XElement WriteWorkSheet_dimension(WorkSheet sheet) { XElement dimension = new XElement(ExcelCommon.Schema_WorkBook_Main + "dimension"); if (sheet.Dimension.StartColumn <= 0 || sheet.Dimension.StartRow <= 0) dimension.Add(new XAttribute("ref", "A1")); else dimension.Add(new XAttribute("ref", sheet.Dimension.ToAddress())); return dimension; }
private void WriteWorkSheet_sheetPr_outlinePr(XElement sheetPr, WorkSheet sheet) { if (sheet.OutLineApplyStyle || sheet.ShowOutlineSymbols || sheet.OutLineSummaryBelow || sheet.OutLineSummaryRight) { XElement outlinePr = new XElement(ExcelCommon.Schema_WorkBook_Main + "outlinePr"); if (sheet.OutLineApplyStyle) outlinePr.Add(new XAttribute("applyStyles", "1")); if (sheet.ShowOutlineSymbols) outlinePr.Add(new XAttribute("showOutlineSymbols", "1")); if (sheet.OutLineSummaryBelow) outlinePr.Add(new XAttribute("summaryBelow", "1")); else outlinePr.Add(new XAttribute("summaryBelow", "0")); if (sheet.OutLineSummaryRight) outlinePr.Add(new XAttribute("summaryRight", "1")); sheetPr.Add(outlinePr); } }
private XElement WriteWorkSheet_sheetProtection(WorkSheet sheet) { if (sheet.SheetProtection.Attributes.Count == 0) return null; XElement sheetProtection = new XElement(ExcelCommon.Schema_WorkBook_Main + sheet.SheetProtection.NodeName); foreach (var item in sheet.SheetProtection.Attributes) { sheetProtection.Add(new XAttribute(item.Key, item.Value)); } return sheetProtection; }
private XElement WriteWorkSheet_legacyDrawing(WorkSheet sheet) { XElement commentsParts = new XElement(ExcelCommon.Schema_WorkBook_Main + "legacyDrawing"); CommentCollection comments = new CommentCollection(sheet); ((IPersistable)comments).Save(this.Context); commentsParts.Add(new XAttribute(XName.Get("id", ExcelCommon.Schema_Relationships), this.Context.CommentsSheetRelationships[sheet.Name])); return commentsParts; }
private XElement WriteWorkSheet_hyperLinks(WorkSheet sheet, List<Cell> sheetHyperLink, PackagePart worksheetPart) { XElement sheethyperlinks = new XElement(XName.Get("hyperlinks", ExcelCommon.Schema_WorkBook_Main.NamespaceName)); Dictionary<string, string> hyps = new Dictionary<string, string>(); foreach (Cell celllink in sheetHyperLink) { XElement hyperlinkNode = new XElement(XName.Get("hyperlink", ExcelCommon.Schema_WorkBook_Main.NamespaceName)); #region "location" //if (string.IsNullOrEmpty(celllink._Hyperlink.AbsoluteUri)) if (celllink._Hyperlink.AbsoluteUri == "xl://internal/") { if (celllink._Hyperlink is ExcelHyperLink) { ExcelHyperLink hyperlink = celllink._Hyperlink as ExcelHyperLink; string address = Range.Parse(sheet, celllink.Column.Index, celllink.Row.Index, hyperlink.ColSpann + celllink.Column.Index, celllink.Row.Index + hyperlink.RowSpann).ToAddress(); hyperlinkNode.Add(new XAttribute("ref", address)); if (hyperlink.ReferenceAddress.IsNotEmpty()) { string strlocation = hyperlink.ReferenceAddress; if (strlocation.Contains("!")) { if (strlocation[0] != '\'') { hyperlinkNode.Add(new XAttribute("location", string.Format("'{0}'!{1}", strlocation.Substring(0, strlocation.IndexOf('!')), strlocation.Substring(strlocation.IndexOf('!') + 1)))); } else { hyperlinkNode.Add(new XAttribute("location", strlocation)); } } else { hyperlinkNode.Add(new XAttribute("location", string.Format("'{0}'!{1}", sheet.Name, strlocation))); } } if (hyperlink.Display.IsNotEmpty()) { hyperlinkNode.Add(new XAttribute("display", hyperlink.Display)); } if (hyperlink.ToolTip.IsNotEmpty()) { hyperlinkNode.Add(new XAttribute("tooltip", hyperlink.Display)); } } } else { if (hyperlinkNode.Attribute(XName.Get("ref", ExcelCommon.Schema_WorkBook_Main.NamespaceName)) == null) { hyperlinkNode.Add(new XAttribute("ref", celllink.ToString())); } string idRelations = string.Empty; if (hyps.ContainsKey(celllink._Hyperlink.AbsoluteUri)) { idRelations = hyps[celllink._Hyperlink.AbsoluteUri]; } else { idRelations = worksheetPart.CreateRelationship(celllink._Hyperlink, TargetMode.External, ExcelCommon.Schema_Hyperlink).Id; hyps.Add(celllink._Hyperlink.AbsoluteUri, idRelations); } hyperlinkNode.Add(new XAttribute(XName.Get("id", ExcelCommon.Schema_Relationships), idRelations)); } #endregion sheethyperlinks.Add(hyperlinkNode); } return sheethyperlinks; }
private void WriteWorkSheet_HeaderFooter_AddPictures(WorkSheet sheet, HeaderFooterText headerText) { if (headerText.LeftImag != null) sheet._HeaderFooter.Pictures.Add(headerText.LeftImag); if (headerText.CenteredImag != null) sheet._HeaderFooter.Pictures.Add(headerText.CenteredImag); if (headerText.RightAlignedImag != null) sheet._HeaderFooter.Pictures.Add(headerText.RightAlignedImag); }
private void WriteWorkSheet_HeaderFooter_Pictures(WorkSheet sheet, PackagePart worksheetPart) { if (sheet._HeaderFooter._Pictures != null) { if (sheet._HeaderFooter._Pictures.Count > 0) { sheet._HeaderFooter._Pictures.PictureUri = this.Context.Package.GetNewUri(@"/xl/drawings/vmlDrawing{0}.vml"); PackageRelationship rel = worksheetPart.CreateRelationship(PackUriHelper.GetRelativeUri(sheet.SheetUri, sheet._HeaderFooter._Pictures.PictureUri), TargetMode.Internal, ExcelCommon.Schema_Relationships + "/vmlDrawing"); sheet._HeaderFooter._Pictures.RelationshipID = rel.Id; ((IPersistable)sheet._HeaderFooter._Pictures).Save(this.Context); } else { sheet._HeaderFooter._Pictures.RelationshipID = string.Empty; } } }
private XElement WriteWorkSheet_HeaderFooter(WorkSheet sheet, PackagePart worksheetPart) { XElement headerFooterNode = new XElement(XName.Get("headerFooter", ExcelCommon.Schema_WorkBook_Main.NamespaceName)); WriteWorkSheet_HeaderFooter_Attribute(sheet._HeaderFooter, headerFooterNode); if (sheet._HeaderFooter._OddHeader != null) { WriteWorkSheet_HeaderFooter_oddHeader("oddHeader", sheet._HeaderFooter._OddHeader, headerFooterNode); WriteWorkSheet_HeaderFooter_AddPictures(sheet, sheet._HeaderFooter._OddHeader); } if (sheet._HeaderFooter._OddFooter != null) { WriteWorkSheet_HeaderFooter_oddHeader("oddFooter", sheet._HeaderFooter._OddFooter, headerFooterNode); WriteWorkSheet_HeaderFooter_AddPictures(sheet, sheet._HeaderFooter._OddFooter); } if (sheet._HeaderFooter.DifferentOddEven) { headerFooterNode.Add(new XAttribute("differentOddEven", 1)); if (sheet._HeaderFooter._EvenHeader != null) { WriteWorkSheet_HeaderFooter_oddHeader("evenHeader", sheet._HeaderFooter._EvenHeader, headerFooterNode); WriteWorkSheet_HeaderFooter_AddPictures(sheet, sheet._HeaderFooter._EvenHeader); } if (sheet._HeaderFooter._EvenFooter != null) { WriteWorkSheet_HeaderFooter_oddHeader("evenFooter", sheet._HeaderFooter._EvenFooter, headerFooterNode); WriteWorkSheet_HeaderFooter_AddPictures(sheet, sheet._HeaderFooter._EvenFooter); } } if (sheet._HeaderFooter.DifferentFirst) { headerFooterNode.Add(new XAttribute("differentFirst", 1)); if (sheet._HeaderFooter._FirstHeader != null) { WriteWorkSheet_HeaderFooter_oddHeader("firstHeader", sheet._HeaderFooter._FirstHeader, headerFooterNode); WriteWorkSheet_HeaderFooter_AddPictures(sheet, sheet._HeaderFooter._FirstHeader); } if (sheet._HeaderFooter._FirstFooter != null) { WriteWorkSheet_HeaderFooter_oddHeader("firstFooter", sheet._HeaderFooter._FirstFooter, headerFooterNode); WriteWorkSheet_HeaderFooter_AddPictures(sheet, sheet._HeaderFooter._FirstFooter); } } WriteWorkSheet_HeaderFooter_Pictures(sheet, worksheetPart); return headerFooterNode; }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet/sheetData</para> /// <para>NodePath:worksheet/sheetData/row</para> /// </summary> private XElement WriteWorkSheet_sheetData_row(WorkSheet sheet, Row row) { XElement rowXml = new XElement(ExcelCommon.Schema_WorkBook_Main + "row"); rowXml.Add(new XAttribute("r", row.Index.ToString(CultureInfo.InvariantCulture))); if (sheet.Dimension.StartColumn > 0 && sheet.Dimension.EndColumn > 0) { rowXml.Add(new XAttribute("spans", string.Format(CultureInfo.InvariantCulture, "{0}:{1}", sheet.Dimension.StartColumn, sheet.Dimension.EndColumn))); } if (row.Hidden) { rowXml.Add(new XAttribute("ht", "0")); rowXml.Add(new XAttribute("hidden", "1")); } if (row.Height != sheet.DefaultRowHeight && row.Height != ExcelCommon.WorkSheet_DefaultRowHeight) { if (rowXml.Attributes("ht").Count() == 0) rowXml.Add(new XAttribute("ht", row.Height.ToString(CultureInfo.InvariantCulture))); rowXml.Add(new XAttribute("customHeight", "1")); } if (row._Style != null) { rowXml.Add(new XAttribute("s", GetStyleId(row.Style))); rowXml.Add(new XAttribute("customFormat", "1")); } if (row.OutlineLevel > 0) { rowXml.Add(new XAttribute("outlineLevel", row.OutlineLevel.ToString(CultureInfo.InvariantCulture))); if (row.Collapsed) { rowXml.Add(new XAttribute("collapsed", "1")); if (row.Hidden == false) rowXml.Add(new XAttribute("hidden", "1")); } } if (row.Phonetic) rowXml.Add(new XAttribute("ph", "1")); return rowXml; }
private static void CreateHeaderRow(SOARole role, WorkSheet ws) { Row headRow = new Row(3); headRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid); headRow.Style.Border.Top.Style = ExcelBorderStyle.Thin; headRow.Style.Border.Top.Color.SetColor(Color.Black); headRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; headRow.Style.Border.Bottom.Color.SetColor(Color.Black); headRow.Style.Border.Left.Style = ExcelBorderStyle.Thin; headRow.Style.Border.Left.Color.SetColor(Color.Black); headRow.Style.Font.Bold = true; ws.Rows.Add(headRow); int columnIndex = 1; foreach (SOARolePropertyDefinition dimension in role.PropertyDefinitions) { ws.Cells[headRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name; ws.Names.Add(CellAddress.Parse(columnIndex, headRow.Index).ToString(), dimension.Name); columnIndex++; } }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet</para> /// </summary> public void WriteWorkSheet(WorkSheet sheet) { PackagePart worksheetPart = this.Context.Package.CreatePart(sheet.SheetUri, ExcelCommon.ContentType_WorkSheet, sheet.WorkBook.Compression); //存储当前工作表中所有单元评论 List<Comment> sheetComments = new List<Comment>(); List<Cell> sheetHyperLink = new List<Cell>(); XElement xmlWorkSheet = new XElement(ExcelCommon.Schema_WorkBook_Main + "worksheet"); xmlWorkSheet.Add(new XAttribute(XNamespace.Xmlns + "r", ExcelCommon.Schema_Relationships)); WriteWorkSheet_sheetPr(sheet, xmlWorkSheet); xmlWorkSheet.Add(WriteWorkSheet_dimension(sheet)); xmlWorkSheet.Add(WriteWorkSheet_sheetViews(sheet)); xmlWorkSheet.Add(WriteWorkSheet_sheetFormatPr(sheet)); WriteWorkSheet_cols(sheet, xmlWorkSheet); xmlWorkSheet.Add(WriteWorkSheet_sheetData(sheet, sheetComments, sheetHyperLink)); if (sheetHyperLink.Count > 0) { xmlWorkSheet.Add(WriteWorkSheet_hyperLinks(sheet, sheetHyperLink, worksheetPart)); } xmlWorkSheet.Add(WriteWorkSheet_mergeCells(sheet)); XElement result = WriteWorkSheet_phoneticPr(sheet); if (result != null) { xmlWorkSheet.Add(result); } WriteWorkSheet_DataValidations(xmlWorkSheet, sheet); xmlWorkSheet.Add(WriteWorkSheet_pageMargins(sheet)); xmlWorkSheet.Add(WriteWorkSheet_printOptions(sheet)); xmlWorkSheet.Add(WriteWorkSheet_sheetProtection(sheet)); //WriteWorkSheet_pageSetup(sheet, xmlWorkSheet); WriteWorkSheet_pageSetup(sheet, xmlWorkSheet); xmlWorkSheet.Add(WriteWorkSheet_tableParts(sheet)); if (sheet._HeaderFooter != null) { xmlWorkSheet.Add(WriteWorkSheet_HeaderFooter(sheet, worksheetPart)); } xmlWorkSheet.Add(WriteWorkSheet_drawing(sheet, worksheetPart)); if (sheetComments.Count > 0) { this.Context.Comments.Add(sheet.Name, sheetComments); xmlWorkSheet.Add(WriteWorkSheet_legacyDrawing(sheet)); } WriteWorkSheet_legacyDrawingHF(sheet, xmlWorkSheet); XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"), xmlWorkSheet); using (Stream stream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write)) { doc.Save(stream); stream.Flush(); } }
private void WriteWorkSheet_cols(WorkSheet sheet, XElement xmlWorkSheet) { bool isAdd = false; int ColumnMax = 0; XElement colsNode = new XElement(XName.Get("cols", ExcelCommon.Schema_WorkBook_Main.NamespaceName)); foreach (Column col in sheet.Columns) { if (ColumnMax == 0 || ColumnMax < col.Index) { if (col._Style != null || col.Hidden || (col.Width != sheet.DefaultColumnWidth && col.Width != 0 && col.Width != ExcelCommon.WorkSheet_DefaultColumnWidth)) { XElement colNode = new XElement(XName.Get("col", ExcelCommon.Schema_WorkBook_Main.NamespaceName), new XAttribute(XName.Get("min"), col.Index)); WriteWorkSheet_cols_col_ColumnMax(ref ColumnMax, col, colNode); WriteWorkSheet_cols_Col(colNode, col, sheet); colsNode.Add(colNode); isAdd = true; } } } if (isAdd) { xmlWorkSheet.Add(colsNode); } }
/// <summary> /// FileName:sheet.xml /// <para>NodePath:worksheet/sheetPr</para> /// </summary> private void WriteWorkSheet_sheetPr(WorkSheet sheet, XElement xmlWorkSheet) { //todo: /* "syncHorizontal", "syncVertical", "syncRef", "transitionEvaluation", "transitionEntry", "published", "codeName", "filterMode", "enableFormatConditionsCalculation" */ if (sheet.OutLineApplyStyle || sheet.ShowOutlineSymbols || sheet.OutLineSummaryBelow || sheet.OutLineSummaryRight || sheet.PageSetup.ShowAutoPageBreaks || sheet.PageSetup.FitToPage || sheet._TabColor != null || string.IsNullOrEmpty(sheet.SheetCode) == false) { XElement sheetPr = new XElement(ExcelCommon.Schema_WorkBook_Main + "sheetPr"); WriteWorkSheet_sheetPr_Attributes(sheet, sheetPr); WriteWorkSheet_sheetPr_outlinePr(sheetPr, sheet); WriteWorkSheet_sheetPr_pageSetUpPr(sheetPr, sheet); if (sheet._TabColor != null) { XElement tabColor = new XElement(ExcelCommon.Schema_WorkBook_Main + "tabColor"); tabColor.Add(WriteColor(sheet._TabColor)); sheetPr.Add(tabColor); } xmlWorkSheet.Add(sheetPr); } }
private XElement WriteWorkSheet_tableParts(WorkSheet sheet) { int tableCount = sheet.Tables.Count; if (tableCount == 0) { return null; } XElement tableParts = new XElement(ExcelCommon.Schema_WorkBook_Main + "tableParts", new XAttribute("count", tableCount)); foreach (Table table in sheet.Tables) { ((IPersistable)table).Save(this.Context); var xName = XName.Get("id", ExcelCommon.Schema_Relationships); XElement tablePart = new XElement(ExcelCommon.Schema_WorkBook_Main + "tablePart", new XAttribute(xName, table.RelationshipID)); tableParts.Add(tablePart); } return tableParts; }
private void WriteWorkSheet_sheetPr_Attributes(WorkSheet sheet, XElement sheetPr) { /* "syncHorizontal", "syncVertical", "syncRef", "transitionEvaluation", "transitionEntry", "published", "codeName", "filterMode", "enableFormatConditionsCalculation" */ if (string.IsNullOrEmpty(sheet.SheetCode) == false) { sheetPr.Add(new XAttribute("codeName", sheet.SheetCode)); } }
private void WriteWorkSheet_sheetPr_pageSetUpPr(XElement sheetPr, WorkSheet sheet) { if (sheet.PageSetup.ShowAutoPageBreaks || sheet.PageSetup.FitToPage) { XElement pageSetUpPr = new XElement(ExcelCommon.Schema_WorkBook_Main + "pageSetUpPr"); if (sheet.PageSetup.ShowAutoPageBreaks) { pageSetUpPr.Add(new XAttribute("autoPageBreaks", "1")); } if (sheet.PageSetup.FitToPage) { pageSetUpPr.Add(new XAttribute("fitToPage", "1")); } sheetPr.Add(pageSetUpPr); } }
private void WriteWorkSheet_cols_Col(XElement colNode, Column col, WorkSheet sheet) { if (col.Width != sheet.DefaultColumnWidth && col.Width != ExcelCommon.WorkSheet_DefaultColumnWidth) { colNode.Add(new XAttribute(XName.Get("width"), col.Width)); colNode.Add(new XAttribute(XName.Get("customWidth"), 1)); } if (col.BestFit) { colNode.Add(new XAttribute(XName.Get("bestFit"), 1)); } if (col.Collapsed) { colNode.Add(new XAttribute(XName.Get("collapsed"), 1)); } if (col.Style != null) { colNode.Add(new XAttribute(XName.Get("style"), GetStyleId(col.Style))); } if (col.Phonetic) { colNode.Add(new XAttribute(XName.Get("phonetic"), 1)); } if (col.OutlineLevel != 0) { colNode.Add(new XAttribute(XName.Get("outlineLevel"), col.OutlineLevel)); } if (col.Hidden) { colNode.Add(new XAttribute(XName.Get("hidden"), 1)); } }
private void WriteWorkSheet_pageSetup(WorkSheet sheet, XElement xmlWorkSheet) { if (sheet._PageSetup != null) { XElement pageSetup = new XElement(ExcelCommon.Schema_WorkBook_Main + "pageSetup"); if (sheet.PageSetup.BlackAndWhite) pageSetup.Add(new XAttribute("blackAndWhite", "1")); if (sheet.PageSetup.Draft) pageSetup.Add(new XAttribute("draft", "1")); if (sheet.PageSetup.NumberOfCopiesToPrint != int.MinValue) pageSetup.Add(new XAttribute("copies", sheet.PageSetup.NumberOfCopiesToPrint.ToString(CultureInfo.InvariantCulture))); if (sheet.PageSetup.FirstPageNumber != int.MinValue) pageSetup.Add(new XAttribute("firstPageNumber", sheet.PageSetup.FirstPageNumber.ToString(CultureInfo.InvariantCulture))); if (sheet.PageSetup.FitToHeight != int.MinValue) pageSetup.Add(new XAttribute("fitToHeight", sheet.PageSetup.FitToHeight.ToString(CultureInfo.InvariantCulture))); if (sheet.PageSetup.FitToWidth != int.MinValue) pageSetup.Add(new XAttribute("fitToHeight", sheet.PageSetup.FitToWidth.ToString(CultureInfo.InvariantCulture))); if (sheet.PageSetup.Scale != int.MinValue) pageSetup.Add(new XAttribute("scale", sheet.PageSetup.Scale.ToString(CultureInfo.InvariantCulture))); if (sheet.PageSetup.Orientation != ExcelOrientation.Default) pageSetup.Add(new XAttribute("orientation", sheet.PageSetup.Orientation.ToString().ToLower())); if (sheet.PageSetup.PageOrder == ExcelPageOrder.OverThenDown) pageSetup.Add(new XAttribute("pageOrder", "overThenDown")); if (sheet.PageSetup.PaperSize != ExcelPaperSize.Letter) pageSetup.Add(new XAttribute("paperSize", ((int)sheet.PageSetup.PaperSize).ToString(CultureInfo.InvariantCulture))); xmlWorkSheet.Add(pageSetup); } }
private static void FillSheetData(SOARole role, WorkSheet ws) { int rowIndex = 4; SOARolePropertyRowCollection rows = SOARolePropertiesAdapter.Instance.LoadByRole(role, role.PropertyDefinitions); foreach (SOARolePropertyRow row in rows) { foreach (DefinedName name in ws.Names) { SOARolePropertyValue propertyValue = row.Values.FindByColumnName(name.Name); string dataValue = null; if (propertyValue != null) dataValue = propertyValue.Value; else { switch (name.Name.ToLower()) { case "operatortype": dataValue = row.OperatorType.ToString(); break; case "operator": dataValue = row.Operator; break; } } if (dataValue != null) ws.Cells[rowIndex, name.Address.StartColumn].Value = dataValue; } rowIndex++; } }
private void WriteWorkSheet_DataValidations(XElement xmlWorkSheet, WorkSheet sheet) { if (sheet._Validations != null && sheet._Validations.Count > 0) { XElement dataValidationsNode = new XElement(XName.Get("dataValidations", ExcelCommon.Schema_WorkBook_Main.NamespaceName)); int index = 0; foreach (IDataValidation item in sheet._Validations) { index++; XElement dataValidationNode = new XElement(XName.Get("dataValidation", ExcelCommon.Schema_WorkBook_Main.NamespaceName), new XAttribute("type", item.ValidationType.SchemaName), new XAttribute(XName.Get("sqref"), item.Address.ToAddress())); WriteWorkSheet_DataValidations_Attributes(dataValidationNode, item); WriteWorkSheet_DataValidations_formulas(item, dataValidationNode); dataValidationsNode.Add(dataValidationNode); } dataValidationsNode.Add(new XAttribute("count", index)); xmlWorkSheet.Add(dataValidationsNode); } }
private static int FillMatrixSheetData(WorkSheet sheet, WfActivityMatrixResourceDescriptor activityMatrix, int startRowIndex) { SOARolePropertyRowCollection rows = activityMatrix.Rows; foreach (SOARolePropertyRow row in rows) { foreach (DefinedName bookmark in sheet.Names) { object dataValue = GetDataValueByBookmark(bookmark, row); if (dataValue != null) sheet.Cells[startRowIndex, bookmark.Address.StartColumn].Value = dataValue; } startRowIndex++; } return startRowIndex; }
private XElement WriteWorkSheet_drawing(WorkSheet sheet, PackagePart worksheetPart) { if (sheet.Drawings.Count > 0) { ((IPersistable)sheet.Drawings).Save(this.Context); return new XElement(XName.Get("drawing", ExcelCommon.Schema_WorkBook_Main.NamespaceName), new XAttribute(XName.Get("id", ExcelCommon.Schema_Relationships), sheet.Drawings.RelationshipID)); } else { return null; } }
public HeaderFooter(WorkSheet wroksheet) { this._WorkSheet = wroksheet; }
private void WriteWorkSheet_legacyDrawingHF(WorkSheet sheet, XElement xmlWorkSheet) { if (sheet._HeaderFooter != null) { if (sheet._HeaderFooter._Pictures != null) { if (sheet._HeaderFooter._Pictures.Count > 0) { xmlWorkSheet.Add(new XElement(XName.Get("legacyDrawingHF", ExcelCommon.Schema_WorkBook_Main.NamespaceName), new XAttribute(XName.Get("id", ExcelCommon.Schema_Relationships), sheet._HeaderFooter._Pictures.RelationshipID))); } } } }