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