public static void CreateResultsWorksheet(DataSet Schedule) { // Frequenty-used variable for optional arguments. object m_objOpt = System.Reflection.Missing.Value; Application xlApp = new Application(); Worksheet ws = new Worksheet(); int outRows, outCols; //Rows and columns are transposed for printing outCols = Schedule.Tables[0].Columns.Count; outRows = Schedule.Tables[0].Rows.Count + 1; //leave roof for row and column headers object[,] OutputArray = new object[outRows, outCols]; ////Note Excel Arrays are 1-based, not 0-based for (int i = 0; i < outCols; i++) { //Put the titles into the spreadsheet. OutputArray[0, i] = Schedule.Tables[0].Columns[i].ColumnName; } for (int i = 1; i < outRows; i++) { for (int j = 0;j < outCols; j++) { DataRow dr = Schedule.Tables[0].Rows[i - 1]; OutputArray[i, j] = dr[j]; } } //Create a workbook and add a worksheet named "Schedule Results" xlApp.Workbooks.Add(m_objOpt); ws = (Worksheet) xlApp.Workbooks[1].Worksheets[1]; ws.Name = "Schedule Results"; Range r = ws.get_Range(ws.Cells[1,1],ws.Cells[outRows, outCols]); //put the output array into Excel in one step. r.Value2=OutputArray; //select the title columns and make them bold r = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, outCols + 1]); r.Font.Bold = true; r.Font.Size = 14; //format any DateTime Columns for (int i = 0; i<outCols; i++) { if (Schedule.Tables[0].Columns[i].DataType.ToString() == "System.DateTime" ) { r = ws.get_Range(ws.Cells[1, i+1], ws.Cells[outRows + 1, i+1]); r.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"; } } //Select the entire spreadsheet and autofit the contents r = ws.get_Range(ws.Cells[1, 1], ws.Cells[outRows + 1, outCols + 1]); r.Columns.AutoFit(); xlApp.Visible = true; xlApp = null; }
private static void AddComment(Worksheet worksheet, string note, byte columnIndex) { int commentIndex = worksheet.Comments.Add(0, columnIndex); Aspose.Cells.Comment comment = worksheet.Comments[commentIndex]; comment.Note = note; comment.WidthCM = 20; }
private void Delete(int? sheetNumber = null, string sheetName = null) { CheckFiles(); PrepareArchive(false); // Get worksheet details Worksheet worksheet = new Worksheet(); worksheet.GetWorksheetProperties(this, sheetNumber, sheetName); // Delete the file if (!string.IsNullOrEmpty(worksheet.FileName)) { ZipArchiveEntry entry = this.Archive.GetEntry(worksheet.FileName); if (entry != null) { entry.Delete(); } if (this.DeleteWorksheets == null) { this.DeleteWorksheets = new List<int>(); } this.DeleteWorksheets.Add(worksheet.Index); } }
public void Parse() { currentSheet = xlWorkBook.Worksheets.get_Item(1); Range range = currentSheet.UsedRange; string page = ""; for (int i = 1; i < range.Rows.Count+1; i++) { using (WebClient clien = new WebClient()) { try { page = clien.DownloadString((string)(currentSheet.Cells[i, 1] as Range).Value); } catch (Exception) { } if (page.Contains(linkurl)) { currentSheet.Cells[i, 2] = "Ссылка есть"; } else { currentSheet.Cells[i, 2] = "Ссылки нет"; } } } xlWorkBook.Save(); Dispose(); }
public static void EnsureColumn(Worksheet worksheet, uint columnIndex) { var columns = worksheet.Elements<Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new Columns(), 0); } if (columns.Elements<Column>().Where(item => item.Min == columnIndex).Count() == 0) { Column previousColumn = null; for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements<Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter(new Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } }
public IEnumerable<ExpandoObject> GetObjects(Worksheet worksheet, ISheetDefinition sheetDefinition) { if (worksheet == null) throw new ArgumentNullException("worksheet"); if (sheetDefinition == null) throw new ArgumentNullException("sheetDefinition"); var rows = worksheet.Descendants<Row>().Skip(1); return rows.Select(x => ParseFromRow(x, sheetDefinition)); }
// 打开Excel文件 public void OpenExcelFile() { OpenFileDialog opd = new OpenFileDialog(); if (opd.ShowDialog() == DialogResult.OK) excelOpenFileName = opd.FileName; textBox4.Text = System.IO.Path.GetFileNameWithoutExtension(excelOpenFileName); if (excelOpenFileName !="") { try { excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelWorkBooks = excelApplication.Workbooks; excelWorkBook = ((Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); excelWorkSheet = (Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex]; excelApplication.Visible = false; rangeOfExcel = excelWorkSheet.UsedRange.Cells.Rows.Count;//获取EXCEL行数 stateOfSave = false; } catch (Exception e) { closeApplication(); MessageBox.Show("(1)没有安装Excel;(2)或没有安装.NET 可编程性支持;\n详细信息:" + e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("未选择文件!"); closeApplication(); } }
// //********************************************************************************************** // public TAssignmentMethod ReadAssignmentMethod() { TAssignmentMethod MyMethod = TAssignmentMethod.None; MyWorkSheet = (Worksheet)MyWorkBook.Worksheets.get_Item("Control"); Range MyRange; try { MyRange = (Range)MyWorkSheet.Cells[19, 3]; Int32 M = Convert.ToInt32(MyRange.Value2); switch (M) { case 0: MyMethod = TAssignmentMethod.Interpolate; break; case 1: MyMethod = TAssignmentMethod.NearestNeighbour; break; default: MyMethod = TAssignmentMethod.None; break; } } catch (Exception e) { MessageBox.Show("Error reading method: " + e.Message); } return MyMethod; }
private static void fillSection(SvodDdr.DdrDataContext dc, Worksheet ws, List<SourceData> sqlData, List<SourceData> usedData, int minRow, int maxRow) { for (int rowIndex = minRow; rowIndex <= maxRow; rowIndex++) { fillRow(dc, rowIndex, ws, sqlData, usedData); } }
public void Test() { Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("Test"); string styleId = workbook.Styles.Add(new Style { Alignment = new Alignment { Horizontal = "Center" } }); for (int i = 1; i < 10; i++) { for (int j = 1; j <= i; j++) { string str = string.Format("{0} X {1} = {2}", j, i, i*j); worksheet.Tables.Columns[i].Width = 65; worksheet.Tables.Rows[i].Cells[j] = new Cell(str) { StyleId = styleId }; } } workbook.WorkSheets.Add(worksheet); _excel.Workbooks.Add(workbook); workbook.Save("c:\\aa.xml"); }
public ExcelReader(TableInfo structure, Worksheet worksheet) : base(structure) { _worksheet = worksheet; _array = new string[structure.ColumnCount]; _usedRange = _worksheet.UsedRange; }
public SheetViews(Worksheet worksheet, bool selected, int frozenRow, int frozenColumn) : this(worksheet) { Selected = selected; FrozenRow = frozenRow; FrozenColumn = frozenColumn; }
private static Worksheet mySheet1; //工作簿1 #endregion Fields #region Methods public static List<List<string>> Read(string fileName,int columnCount,int startRowIndex) { List<List<string>> textList = new List<List<string>>(); excelApp = new ApplicationClass(); Workbooks myWorkBooks = excelApp.Workbooks; myWorkBooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Sheets sheets = excelApp.Sheets; mySheet1 = (Worksheet)sheets[1]; int rowCount = mySheet1.UsedRange.Rows.Count; if (rowCount != 0) { for (int i = startRowIndex; i <= rowCount; i++) { string name = ((Range)mySheet1.Cells[i, 2]).Text.ToString(); if (name != "") { List<string> list = new List<string>(); list.Add((i-startRowIndex+1).ToString()); for (int j = 0; j < columnCount; j++) { list.Add(((Range)mySheet1.Cells[i, j + 1]).Text.ToString()); } textList.Add(list); } } } myWorkBooks.Close(); excelApp.Quit(); excelApp = null; return textList; }
public static void SaveExcelFile(string fileName, string tempFileName,string[,] contents, int startColumn, int startRow) { try { excelApp = new ApplicationClass(); Workbooks myWorkBooks = excelApp.Workbooks; myWorkBooks.Open(tempFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Sheets sheets = excelApp.Sheets; mySheet1 = (Worksheet)sheets[1]; mySheet1.Activate(); //写入测试信息 Range range1 = mySheet1.get_Range(mySheet1.Cells[startRow, startColumn], mySheet1.Cells[contents.GetLength(0)+startRow-1,contents.GetLength(1)+startColumn-1]); range1.Value2 = contents; mySheet1.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing); myWorkBooks.Close(); excelApp.Quit(); excelApp = null; } catch (Exception ee) { throw ee; } }
private void fillExcle(Worksheet sheet, List<Config> list, SqlCommand sqlComm) { for (int i = 0; i < list.Count; i++) { SqlDataReader reader = null; Config con = list[i]; try { sqlComm.CommandText = con.Sql; reader = sqlComm.ExecuteReader(); if (reader.Read()) { object data = reader.GetValue(0); sheet.Cells[con.Pos].PutValue(data); } reader.Close(); } catch (Exception ee) { sheet.Cells[con.Pos].PutValue(ee.Message); if (reader != null) reader.Close(); } } }
private Worksheet Read(int? sheetNumber = null, string sheetName = null, int existingHeadingRows = 0) { CheckFiles(); PrepareArchive(); Worksheet worksheet = new Worksheet(); worksheet.ExistingHeadingRows = existingHeadingRows; worksheet.GetWorksheetProperties(this, sheetNumber, sheetName); IEnumerable<Row> rows = null; List<string> headings = new List<string>(); using (Stream stream = this.Archive.GetEntry(worksheet.FileName).Open()) { XDocument document = XDocument.Load(stream); int skipRows = 0; Row possibleHeadingRow = new Row(document.Descendants().Where(d => d.Name.LocalName == "row").FirstOrDefault(), this.SharedStrings); if (worksheet.ExistingHeadingRows == 1 && possibleHeadingRow.RowNumber == 1) { foreach (Cell headerCell in possibleHeadingRow.Cells) { headings.Add(headerCell.Value.ToString()); } } rows = GetRows(document.Descendants().Where(d => d.Name.LocalName == "row").Skip(skipRows)); } worksheet.Headings = headings; worksheet.Rows = rows; return worksheet; }
public string GetCellStringValue(Worksheet sheet, string cellName) { var cell = sheet.Cells[cellName]; if (cell.Value == null) return string.Empty; return cell.Value.ToString(); }
public object GetCellValue(Worksheet sheet, SheetDataColumn column, string cellName) { var cell = sheet.Cells[cellName]; if (cell.Value == null) { return DBNull.Value; } else { switch (column.DataType) { case DataType.DateTime: DateTime dateTimeValue; if (DateTime.TryParse(cell.Value.ToString(), out dateTimeValue)) return dateTimeValue; else return DBNull.Value; case DataType.Integer: Int32 intValue; if (Int32.TryParse(cell.Value.ToString(), out intValue)) return intValue; else return DBNull.Value; case DataType.Double: Double doubleValue; if (Double.TryParse(cell.Value.ToString(), out doubleValue)) return doubleValue; else return DBNull.Value; case DataType.String: default: return (string)cell.Value; } } }
/*********************************** * DAL METHODS ************************************/ // Read internal static HeaderFooter ReadHeaderFooterFromReader(CustomOpenXmlReader reader, Worksheet worksheet) { HeaderFooter headerFooter = new HeaderFooter(worksheet); foreach (CustomOpenXmlAttribute attribute in reader.Attributes) { switch (attribute.LocalName) { case "alignWithMargins": headerFooter.AlignWithMargins = attribute.GetBoolValue(); break; default: throw new Exception(string.Format("HeaderFooter attribute {0} not coded", attribute.LocalName)); } } while (reader.ReadToEndElement<OpenXmlSpreadsheet.HeaderFooter>()) { if (reader.IsStartElementOfType<OpenXmlSpreadsheet.OddHeader>()) headerFooter.OddHeader = reader.GetText(); else if (reader.IsStartElementOfType<OpenXmlSpreadsheet.OddFooter>()) headerFooter.OddFooter = reader.GetText(); } return headerFooter; }
private Worksheet[] GetWorksheetProperties() { CheckFiles(); PrepareArchive(false); var worksheets = new List<Worksheet>(); using (Stream stream = this.Archive.GetEntry("xl/workbook.xml").Open()) { XDocument document = XDocument.Load(stream); if (document == null) { throw new Exception("Unable to load workbook.xml"); } List<XElement> sheetsElements = document.Descendants().Where(d => d.Name.LocalName == "sheet").ToList(); foreach (var sheetElement in sheetsElements) { var worksheet = new Worksheet(this); worksheet.Index = sheetsElements.IndexOf(sheetElement) + 1; worksheet.Name = (from attribute in sheetElement.Attributes() where attribute.Name == "name" select attribute.Value).FirstOrDefault(); worksheets.Add(worksheet); } } return worksheets.ToArray(); }
private void setDTOInfo(Worksheet sheet, List<HcDTOInfo> dtoList) { var name = sheet.Name; if (name.Length > 3 && name.Substring(name.Length - 3, 3).Equals("DTO")) { var dtoInfo = new HcDTOInfo(); Range range = null; range = (Range)sheet.Cells[2, 3]; dtoInfo.Name = range.Value.ToString(); range = (Range)sheet.Cells[1, 3]; dtoInfo.Caption = range.Value.ToString(); int iRow = 5; range = (Range)sheet.Cells[iRow, 3]; var cellValue = range.Value; dtoInfo.FieldArray = new List<HcFieldInfo>(); while (cellValue != null && !string.IsNullOrEmpty(cellValue.ToString())) { var field = new HcFieldInfo(); field.name = cellValue.ToString(); range = (Range)sheet.Cells[iRow, 2]; field.caption = range.Value.ToString(); range = (Range)sheet.Cells[iRow, 4]; field.FieldTypeString = range.Value.ToString(); dtoInfo.FieldArray.Add(field); iRow += 1; range = (Range)sheet.Cells[iRow, 3]; cellValue = range.Value; } dtoList.Add(dtoInfo); } }
public HeaderFooter(Worksheet worksheet, string oddHeader, string oddFooter, bool alignHeaderFooterWithMargins) : this(worksheet) { OddHeader = oddHeader; OddFooter = oddFooter; AlignWithMargins = alignHeaderFooterWithMargins; }
public bool CheckValidTemplate(string username, string language_id, string store_procedure, string file_name, string module_id, string function_id, Worksheet ws) { bool result = false; DataRow dr = db.GetDataRow("SYS_spfrmImportFileConfig", new string[] { "Activity", "Username", "LanguageID", "ExcelFile", "FunctionID", "ModuleID" }, new object[] { "CheckValidTemplate", username, language_id, file_name, function_id, module_id }); if (dr != null) result = true; else { DataTable dt = db.GetDataTable("SYS_spCommon", new string[] { "ObjectName" }, new object[] { store_procedure }); if (dt != null && dt.Rows.Count > 0) { int count = 0; for (int i = 0; i < ws.Cells.MaxColumn; i++) { if (string.IsNullOrEmpty(ws.Cells[1, i].Value + "")) break; string tmp = ws.Cells[1, i].Value.ToString().ToLower().Trim(); if (string.IsNullOrEmpty(tmp)) break; if (tmp != "stt" && tmp != "$hidecolumn$" && tmp != "$deletecolumn$") { DataRow[] rows = dt.Select("ColumnName='@" + tmp + "'"); if (rows.Length == 0) count++; } } result = (count < 3); } else result = false; } return result; }
internal ExcelWorksheet(Worksheet worksheet, ExcelDocument parent) { IsDisposed = false; Worksheet = worksheet; Parent = parent; }
/// <summary> /// 取得欄位驗證字串 /// </summary> /// <returns></returns> public static string GetChekcDataStr(int idx, Worksheet wst, Dictionary<string, int> ColIndexDic) { string chkStr = string.Empty; if (ColIndexDic.ContainsKey("學號")) chkStr += wst.Cells[idx, ColIndexDic["學號"]].StringValue; if (ColIndexDic.ContainsKey("姓名")) chkStr += wst.Cells[idx, ColIndexDic["姓名"]].StringValue; //if (ColIndexDic.ContainsKey("學年度")) // chkStr += wst.Cells[idx, ColIndexDic["學年度"]].StringValue; //if (ColIndexDic.ContainsKey("學期")) // chkStr += wst.Cells[idx, ColIndexDic["學期"]].StringValue; if (ColIndexDic.ContainsKey("異動日期")) chkStr += wst.Cells[idx, ColIndexDic["異動日期"]].StringValue; if (ColIndexDic.ContainsKey("異動代碼")) chkStr += wst.Cells[idx, ColIndexDic["異動代碼"]].StringValue; if (ColIndexDic.ContainsKey("原因及事項")) chkStr += wst.Cells[idx, ColIndexDic["原因及事項"]].StringValue; return chkStr; }
//Open Excel file public int FnOpenExcel(string sPath, string iSheet) { int functionReturnValue = 0; try { application = new Microsoft.Office.Interop.Excel.Application { Visible = true }; //Microsoft.Office.Interop.Excel.Application(); workbook = application.Workbooks.Open(sPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // get all sheets in workbook //worksheet = (Worksheet) workbook.Worksheets; // get some sheet //string currentSheet = "Main Invoice"; worksheet = (Worksheet) workbook.Worksheets["" + iSheet + ""]; // Get the active sheet worksheet = (Worksheet)workbook.ActiveSheet; functionReturnValue = 0; } catch (Exception ex) { functionReturnValue = -1; MessageBox.Show(ex.Message); } return functionReturnValue; }
public Export(bool defaultBackgroundIsWhite) { app = new Application(); app.Visible = true; workbook = app.Workbooks.Add(1); worksheet = (Worksheet)workbook.Sheets[1]; }
/// <summary> /// 添加Excel头部 /// </summary> /// <param name="dt"></param> private void AddHeader(DataTable dt, Worksheet sheetNew = null, Hashtable ht = null) { if (sheetNew == null) { sheetNew = sheet; } Cell cell = null; for (int col = 0; col < dt.Columns.Count; col++) { cell = sheetNew.Cells[0, col]; if (ht == null) { cell.PutValue(dt.Columns[col].ColumnName); } else { string name = ht[dt.Columns[col].ColumnName].ToString() ?? dt.Columns[col].ColumnName; cell.PutValue(name); } Style style = new Style(); style.Font.IsBold = true; cell.SetStyle(style); } }
public DataGridViewExport(DataGridView dgv) { _workbook = new Workbook(); _workbook.Worksheets.Clear(); _worksheet = _workbook.Worksheets[_workbook.Worksheets.Add()]; _worksheet.Name = "Sheet1"; _colIndexes = new List<int>(); int sheetRowIndex = 0; int sheetColIndex = 0; foreach (DataGridViewColumn col in dgv.Columns) { if (col.Visible == false) continue; _colIndexes.Add(col.Index); _worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue(col.HeaderText); } foreach (DataGridViewRow row in dgv.Rows) { sheetRowIndex++; sheetColIndex = 0; foreach (int colIndex in _colIndexes) _worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue("" + row.Cells[colIndex].Value); } _worksheet.AutoFitColumns(); }
private void setInDTO(Worksheet sheet, HcServiceInfo serviceInfo, ref int irow) { var inDto = new HcDTOInfo(); inDto.Caption = serviceInfo.Caption + "的InDTO"; var range = (Range)sheet.Cells[19, 2]; inDto.Name = range.Value.ToString(); range = (Range)sheet.Cells[20, 15]; var cellValue = range.Value; inDto.FieldArray = new List<HcFieldInfo>(); while (cellValue != null && !string.IsNullOrEmpty(cellValue.ToString())) { var field = new HcFieldInfo(); field.name = cellValue.ToString(); range = (Range)sheet.Cells[irow, 3]; field.caption = range.Value.ToString(); range = (Range)sheet.Cells[irow, 22]; field.FieldTypeString = range.Value.ToString(); inDto.FieldArray.Add(field); irow += 1; range = (Range)sheet.Cells[irow, 15]; cellValue = range.Value; } serviceInfo.InDTO = inDto; }
private static void Main(string[] args) { DateTime today = DateTime.Now; System.Data.DataTable dataCenso = new System.Data.DataTable(); List <Censo> censos = new List <Censo>(); WebRequest request = WebRequest.Create(URL); try { using (var twitpicResponse = (HttpWebResponse)request.GetResponse()) { using (var reader = new StreamReader(twitpicResponse.GetResponseStream())) { JsonSerializer json = new JsonSerializer(); var objText = reader.ReadToEnd(); censos = JsonConvert.DeserializeObject <List <Censo> >(objText); foreach (var obj in censos) { obj.tempo = BlankFunction(obj.tempo); obj.tempo = BlankFunctionTempo(obj.tempo); } dataCenso = CreateDataTable(censos); } } } catch (Exception ex) { String error = ex.Message; Console.ReadKey(); } String excelFilePath = "\\\\hspmins2\\NIR_Nucleo_Interno_Regulacao\\2359\\Censo" + today.ToString().Replace('/', '_').Replace(' ', '_').Replace(':', '_'); try { if (dataCenso == null || dataCenso.Columns.Count == 0) { throw new Exception("ExportToExcel: Null or empty input table!\n"); } Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbook wb = app.Workbooks.Add(XlSheetType.xlWorksheet); Worksheet workSheet = (Worksheet)app.ActiveSheet; // load excel, and create a new workbook //var excelApp = new Microsoft.Office.Interop.Excel.Application(); //excelApp.Workbooks.Add(excelApp); // single worksheet // Microsoft.Office.Interop.Excel._Worksheet workSheet = (Microsoft.Office.Interop.Excel._Worksheet)excelApp.ActiveSheet; // column headings for (var i = 0; i < dataCenso.Columns.Count; i++) { workSheet.Cells[1, i + 1] = dataCenso.Columns[i].ColumnName; } // rows for (var i = 0; i < dataCenso.Rows.Count; i++) { // to do: format datetime values before printing for (var j = 0; j < dataCenso.Columns.Count; j++) { /* if (j==9 || j == 10 || j == 13 || j == 24 || j == 25 ) * { * var dt = dataCenso.Rows[i][j]; * workSheet.Cells[i + 2, j + 1] = Convert.ToDateTime(dataCenso.Rows[i][j]); * * } * else * {*/ workSheet.Cells[i + 2, j + 1] = dataCenso.Rows[i][j]; // } } } // check file path if (!string.IsNullOrEmpty(excelFilePath)) { try { //workSheet.Name = "Censo" + today.ToString().Replace('/', '_'); workSheet.Name = "Censo"; workSheet.SaveAs(excelFilePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing); app.Quit(); Console.WriteLine("Excel file saved!"); } catch (Exception ex) { throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n" + ex.Message); } } else { // no file path is given app.Visible = true; } } catch (Exception ex) { throw new Exception("ExportToExcel: \n" + ex.Message); } }
public Worksheet Write_Result_Excel(Worksheet ws, string result, int row) { Range rng = null; Range rngErrormessage = null; object missing = Type.Missing; string screenshothref = ""; FileHandler file = new FileHandler(); DateTime now = DateTime.Now; string date = now.ToString(); date = date.Replace(":", "-"); date = date.Replace("/", "-"); date = date.Replace(" ", "-"); FileHandler CurPath = new FileHandler(); string c = Directory.GetCurrentDirectory(); var stringSeparators = new[] { "bin" }; string[] str1 = c.Split(stringSeparators, StringSplitOptions.None); string imagepath = Parallel_Quest.ImagePath; try { rng = ws.get_Range("F" + row, missing); rng.Font.Bold = true; string StepName = ws.Name + row.ToString(); if ((result.ToLower() == "pass")) { rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); rng.Value2 = result.ToUpper(); } else if ((result.ToLower() == "fail")) { screenshothref = Take_Screenshot(ws.Name, row, "fail", imagepath); rng.Hyperlinks.Add(rng, screenshothref); rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); rng.Value2 = "FAIL"; rngErrormessage = ws.get_Range("G" + row, missing); rngErrormessage.Value2 = result.ToUpper(); } else if ((result.ToLower() != "fail")) { screenshothref = Take_Screenshot(ws.Name, row, "fail", imagepath); rng.Hyperlinks.Add(rng, screenshothref); rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); rng.Value2 = "FAIL"; rngErrormessage = ws.get_Range("G" + row, missing); rngErrormessage.Value2 = result.ToLower(); } return(ws); } catch (System.Exception) { Marshal.FinalReleaseComObject(rng); return(null); } finally { Marshal.FinalReleaseComObject(rng); } }
public static void QWSetup(Workbook WB) { #region Setup // Create the Worksheet Worksheet QW = WB.Worksheets.Add(); QW.Name = "Quotation Worksheet"; QW.Activate(); WB.Windows[1].Zoom = 130; // Set defaults QW.Cells.Font.Name = "Ebrima"; QW.Cells.Font.Size = 10; // Sets the widths of columns A through X double[] ColumnWidths = new double[] { 4.43, 9.29, 9.29, 5.00, 7.86, 2.29, // A : F 4.57, 7.43, 5.00, 5.00, 5.86, 3.29, // G : L 4.00, 4.86, 5.14, 1.14, 1.14, 5.43, // M : R 1.29, 6.71, 6.71, 4.00, 4.00, 4.00 // S : X }; // TODO: Set ActiveRange scope to be determined by data source Range ActiveRange_Start = QW.Range["A1"]; Range ActiveRange_End = QW.Range["X42"]; // Header <- Variable heights, # of rows, used on multiple pages double[] HeaderHeights = new double[] { 22.50, 15.00, 12.75, 18.00, 6.00, 18.00, 6.00 }; // Markup <- Variable heights, # of rows, used on first page only double[] MarkupHeights = new double[] { 15.00, 14.25, 14.25, 14.25, 14.25, 14.25, // 8 : 13 14.25, 14.25, 14.25, 14.25, 14.25, 14.25, // 14 : 19 14.25, 14.25, 14.25, 14.25, 6.00 // 20 : 24 }; // Materials List Header <- Set height, 1 row, used on first page only // Materials List Column Headers <- Set heights, 2 rows, used on multiple pages double[] MaterialsListHeights = new double[] { 18.00, 14.25, 15.75 }; // Materials List <- static height @ 14.25pts, dynamic # of rows, expanded to multiple pages if necessary double MaterialsList_StdHeight = 14.25; Range QWActiveRange = QW.Range["A1", "X42"]; SetColumnWidths(QWActiveRange, ColumnWidths); SetHeaderRows(QWActiveRange, HeaderHeights); SetMarkupRows(QWActiveRange, MarkupHeights, 8); SetMaterialsListHeaders(QWActiveRange, MaterialsListHeights, 25); SetMaterialsList(QWActiveRange, 15, 28, MaterialsList_StdHeight); #endregion Setup #region Header Object Declarations // Set Header Objects Shape APC_Logo = QW.Shapes.AddPicture(LogoPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 0.00F, 0.00F, 227.52F, 54F); Range Title = QW.Range["I1", "O1"]; Range Version = QW.Range["I2", "O2"]; Range CfdBanner = QW.Range["S1", "X1"]; Range ApproverLbl = QW.Range["U2", "V2"]; Range ApproverTxt = QW.Range["W2", "X2"]; Range PageLbl = QW.Range["U4"]; Range PageTxt = QW.Range["V4", "X4"]; Range JobNameLbl = QW.Range["A6", "B6"]; Range JobNameTxt = QW.Range["C6", "G6"]; Range JobNumLbl = QW.Range["H6", "I6"]; Range JobNumTxt = QW.Range["J6", "K6"]; Range SalesLbl = QW.Range["L6"]; Range SalesTxt = QW.Range["M6", "N6"]; Range PMLbl = QW.Range["O6"]; Range PMTxt = QW.Range["P6", "R6"]; Range DateLbl = QW.Range["S6", "T6"]; Range DateTxt = QW.Range["U6", "X6"]; #endregion Header Object Declarations #region Header Formatting // Format Header Objects APC_Logo.PictureFormat.Contrast = 0.6F; #region Title Title.Merge(); Title.Font.Size = 9; Title.Font.Bold = true; Title.Font.Italic = true; AlignRange(Title, V: Align.VCenter); // TODO: Set value to be dynamically adjusted based on data source. Title.Value = "Commercial Quotation Worksheet {C#}"; #endregion Title #region Version Version.Merge(); Version.Font.Superscript = true; AlignRange(Version, V: Align.VTop); // TODO: Set value to be dynamically adjusted based on data source. Version.Value = "Version: 0.0.1"; #endregion Version #region Cfd Banner CfdBanner.Merge(); CfdBanner.Borders.Weight = XlBorderWeight.xlMedium; CfdBanner.Borders.Color = Black; CfdBanner.Font.Bold = true; CfdBanner.Font.Color = White; CfdBanner.Interior.Color = APC_Blue; AlignRange(CfdBanner, V: Align.VCenter); CfdBanner.Value = "CONFIDENTIAL DOCUMENT"; #endregion Cfd Banner #region Approver ApproverLbl.Merge(); ApproverLbl.Font.Size = 8; ApproverLbl.Font.Bold = true; AlignRange(ApproverLbl, H: Align.HRight); ApproverLbl.Value = "Approved By:"; ApproverTxt.Merge(); ApproverTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; AlignRange(ApproverTxt); #endregion Approver #region Page Number PageLbl.Font.Size = 8; PageLbl.Font.Bold = true; PageLbl.HorizontalAlignment = XlHAlign.xlHAlignRight; PageLbl.Value = "Page"; PageTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; PageTxt.HorizontalAlignment = XlHAlign.xlHAlignCenter; PageTxt[1, 1].Value = 1; PageTxt[1, 2].Font.Size = 8; PageTxt[1, 2].Font.Bold = true; PageTxt[1, 2].Value = "of"; // TODO: Dynamically set Page # based on data source. PageTxt[1, 3].Value = 1; #endregion Page Number #region Job Bar JobNameLbl.Merge(); JobNameLbl.Font.Bold = true; AlignRange(JobNameLbl, H: Align.HRight); JobNameLbl.Value = "JOB NAME:"; JobNameTxt.Merge(); JobNameTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; AlignRange(JobNameTxt); JobNumLbl.Merge(); JobNumLbl.Font.Bold = true; AlignRange(JobNumLbl, H: Align.HRight); JobNumLbl.Value = "JOB NO:"; JobNumTxt.Merge(); JobNumTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; AlignRange(JobNumTxt); SalesLbl.Merge(); SalesLbl.Font.Bold = true; AlignRange(SalesLbl, H: Align.HRight); SalesLbl.Value = "SP:"; SalesTxt.Merge(); SalesTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; AlignRange(SalesTxt); PMLbl.Merge(); PMLbl.Font.Bold = true; AlignRange(PMLbl, H: Align.HRight); PMLbl.Value = "PM:"; PMTxt.Merge(); PMTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; AlignRange(PMTxt); DateLbl.Merge(); DateLbl.Font.Bold = true; AlignRange(DateLbl, H: Align.HRight); DateLbl.Value = "DATE:"; DateTxt.Merge(); DateTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin; AlignRange(DateTxt); #endregion Job Bar #endregion Header Formatting #region Markup Formatting #endregion Markup Formatting #region Materials List Formatting #endregion Materials List Formatting }
public void WriteOutputExcel() { output[0, 0] = Format.headervalue[0, 0]; for (int i = 0; i < 63; i++) { output[1, i] = Format.headervalue[1, i]; } int k = 2; for (int i = 0; i < DataMerge.destrow; i++) { for (int j = 0; j < 63; j++) { output[k, j] = Format.destinationfile[i, j]; } k++; } string fileName = System.IO.Path.GetFileName(Program.sourcepath); var extension = Path.GetExtension(fileName); var fname = Path.GetFileNameWithoutExtension(fileName); if (!System.IO.Directory.Exists(Program.destinationfolder)) { System.IO.Directory.CreateDirectory(Program.destinationfolder); } destinationpath = Program.destinationfolder + "Output_ " + Program.fileno + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; try { merged_excel = new Application(); merged_wb = merged_excel.Workbooks.Add(1); merged_excel.DisplayAlerts = false; merged_ws = merged_wb.Worksheets[1]; merged_xlRange = merged_ws.UsedRange; int merged_cols = merged_xlRange.Columns.Count; merged_cols = Format.formatNumCols; var startCell = (Range)merged_ws.Cells[1, 1]; var endCell = (Range)merged_ws.Cells[DataMerge.destrow + 2, merged_cols]; var writeRange = merged_ws.Range[startCell, endCell]; var columnHeadingsRange = merged_ws.Range[merged_ws.Cells[1, 1], merged_ws.Cells[1, merged_cols]]; columnHeadingsRange.Interior.Color = 0x9C632A; columnHeadingsRange.Font.Color = XlRgbColor.rgbWhite; columnHeadingsRange.Font.Size = 13; var columnHeadingsRangeHeader = merged_ws.Range[merged_ws.Cells[2, 1], merged_ws.Cells[2, merged_cols]]; columnHeadingsRangeHeader.Interior.Color = 0xD9D9D9; columnHeadingsRangeHeader.Font.Color = XlRgbColor.rgbBlack; columnHeadingsRangeHeader.Font.Size = 12; columnHeadingsRangeHeader.EntireRow.Font.Bold = true; columnHeadingsRangeHeader.Borders.Color = XlRgbColor.rgbBlack; columnHeadingsRange.EntireColumn.AutoFit(); columnHeadingsRangeHeader.Cells.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; var columnHeadingsRangeData = merged_ws.Range[merged_ws.Cells[3, 1], merged_ws.Cells[DataMerge.destrow + 2, merged_cols]]; columnHeadingsRangeData.Font.Color = XlRgbColor.rgbBlack; columnHeadingsRangeData.Font.Size = 11; columnHeadingsRangeData.Borders.Color = XlRgbColor.rgbBlack; var columnHeadingsRangeEmail = merged_ws.Range[merged_ws.Cells[3, 11], merged_ws.Cells[DataMerge.destrow + 2, 11]]; columnHeadingsRangeEmail.Font.Color = XlRgbColor.rgbBlue; writeRange.Value = output; merged_wb.SaveAs(destinationpath); merged_wb.Close(); } catch (Exception e) { Program.continuexecution = false; Console.WriteLine("Can't create target file because of these reasons:"); Console.WriteLine(e.Message); Program.CloseAllExcelAppication(merged_ws, merged_wb, merged_excel); } finally { Program.CloseAllExcelAppication(merged_ws, merged_wb, merged_excel); } // AutoFit Columns AutoFitRowColumn fit = new AutoFitRowColumn(); fit.FittingRowColumn(destinationpath); Program.fileno++; }
/// <summary> /// Launch the ontology import wizard and generate an Excel skeleton from the ontology. /// </summary> public void LoadOntology() { this.resourcesToImport.Clear(); this.nestedProperties.Clear(); // Displays an OpenFileDialog so the user can select an ontology. OpenFileDialog openOntologyFileDialog = new OpenFileDialog(); openOntologyFileDialog.Filter = "RDF/XML (*.rdf)|*.rdf|Turtle (*.ttl)|*.ttl|JSON-LD (*.jsonld)|*.jsonld|NTriples (*.nt)|*.nt|NQuads (*.nq)|*.nq|TriG (*.trig)|*.trig"; openOntologyFileDialog.Title = "Select an ontology file"; // Show the Dialog. // If the user clicked OK in the dialog and an OWL file was selected, open it. if (openOntologyFileDialog.ShowDialog() == DialogResult.OK) { OntologyGraph g = new OntologyGraph(); FileLoader.Load(g, openOntologyFileDialog.FileName); ImportOptionsForm importOptionsForm = new ImportOptionsForm(g); if (importOptionsForm.ShowDialog() == DialogResult.OK) { // Iterate through the named bottom classes; generate one worksheet for each foreach (OntologyClass oClass in g.OwlClasses) { if (oClass.Resource.NodeType == NodeType.Uri && resourcesToImport.Contains(oClass.ToString())) { Worksheet newWorksheet = Globals.ThisAddIn.Application.Worksheets.Add(); UriNode classAsUriNode = (UriNode)oClass.Resource; newWorksheet.Name = Helper.GetLocalName(classAsUriNode.Uri); // Start iterating from the first column int column = 1; // Add column for the IRI identifier // <IRI> is a special identifier used for this purpose, signaling that a) the IRI shall // be minted from this column, and b) the subsequent row will contain the OWL class for all minted entities string identifierColumnName = Helper.GetExcelColumnName(column); string identifierColumnHeaderCellIdentifier = String.Format("{0}1", identifierColumnName); Range identifierColumnHeaderCell = newWorksheet.get_Range(identifierColumnHeaderCellIdentifier); identifierColumnHeaderCell.Value = "Identifier"; string identifierNote = "<IRI>"; identifierNote += String.Format("\n<{0}>", classAsUriNode.Uri.ToString()); identifierColumnHeaderCell.NoteText(identifierNote); column++; // Iterate through the properties for which this class is in the domain; // generate one column for each property (named from label and if that does not exist from IRI) // Order the columns by type, with datatype properties coming before object properties, // then by string representation foreach (OntologyProperty oProperty in oClass.IsDomainOf.OrderBy(o => o.Types.First()).OrderBy(o => o.ToString())) { if (oProperty.Resource.NodeType == NodeType.Uri && resourcesToImport.Contains(oProperty.ToString())) { // This is because Excel uses strange adressing, i.e., "A1" instead of something // numeric and zero-indexed such as "0,0". string headerColumnName = Helper.GetExcelColumnName(column); string headerCellIdentifier = String.Format("{0}1", headerColumnName); Range headerCellRange = newWorksheet.get_Range(headerCellIdentifier); UriNode propertyAsUriNode = (UriNode)oProperty.Resource; // TODO: the below code is extremely repetitive. Sometime, when not sick and brain is working better, // Future Karl will refactor and simplify this (hopefully) if (nestedProperties.Keys.Contains(propertyAsUriNode.Uri.AbsoluteUri)) { foreach (string nestedPropertyUri in nestedProperties[propertyAsUriNode.Uri.AbsoluteUri]) { // Repeat header cell selection for each nested property headerColumnName = Helper.GetExcelColumnName(column); headerCellIdentifier = String.Format("{0}1", headerColumnName); headerCellRange = newWorksheet.get_Range(headerCellIdentifier); // Find and assign label string headerLabel; // Assign property IRI string noteText = String.Format("<{0}>", propertyAsUriNode.Uri.ToString()); // Asign property type hinting string propertyType = oProperty.Types.First().ToString(); noteText += String.Format("\n<{0}>", propertyType); // Assign range hinting IRI // TODO: what if no range exists? see same case below and after else clause OntologyClass[] namedRanges = oProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray(); if (namedRanges.Count() > 0) { UriNode rangeAsUriNode = (UriNode)namedRanges.First().Resource; string rangeUri = rangeAsUriNode.Uri.ToString(); noteText += String.Format("\n<{0}>", rangeUri); } // Branching for special case of rdfs:label if (nestedPropertyUri.Equals(OntologyHelper.PropertyLabel)) { // Assign header label headerLabel = "rdfs:label"; // Nested property IRI (i.e., rdfs:label) noteText += String.Format("\n<{0}>", OntologyHelper.PropertyLabel); // Nested property type noteText += String.Format("\n<{0}>", OntologyHelper.OwlAnnotationProperty); // Nested property range noteText += String.Format("\n<{0}>", XmlSpecsHelper.XmlSchemaDataTypeString); } else { // Get the property from the ontology OntologyProperty nestedProperty = g.OwlProperties.Where(property => ((UriNode)property.Resource).Uri.AbsoluteUri.Equals(nestedPropertyUri)).First(); UriNode nestedPropertyAsUriNode = (UriNode)nestedProperty.Resource; // Assign header label if (nestedProperty.Label.Count() > 0) { ILiteralNode labelNode = nestedProperty.Label.First(); headerLabel = labelNode.Value; } else { headerLabel = Helper.GetLocalName(nestedPropertyAsUriNode.Uri); } // Nested property IRI noteText += String.Format("\n<{0}>", nestedPropertyAsUriNode.Uri.ToString()); // Asign nested property type hinting string nestedPropertyType; if (nestedProperty.Types.Count() > 0) { nestedPropertyType = nestedProperty.Types.First().ToString(); } else { nestedPropertyType = ""; } noteText += String.Format("\n<{0}>", nestedPropertyType); // Nested range hinting IRI OntologyClass[] namedNestedRanges = nestedProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray(); string nestedRange; if (namedNestedRanges.Count() > 0) { nestedRange = ((UriNode)namedNestedRanges.First().Resource).Uri.ToString(); } else { nestedRange = ""; } noteText += String.Format("\n<{0}>", nestedRange); } // Assign header label headerLabel = headerLabel + " (through " + Helper.GetLocalName(propertyAsUriNode.Uri) + ")"; headerCellRange.Value = headerLabel; // Assign note text headerCellRange.AddComment(noteText); column++; } } else { // Find and assign label string propertyLabel; if (oProperty.Label.Count() > 0) { ILiteralNode labelNode = oProperty.Label.First(); propertyLabel = labelNode.Value; } else { propertyLabel = Helper.GetLocalName(propertyAsUriNode.Uri); } headerCellRange.Value = propertyLabel; // Assign property IRI string noteText = String.Format("<{0}>", propertyAsUriNode.Uri.ToString()); // Asign property type hinting string propertyType = oProperty.Types.First().ToString(); noteText += String.Format("\n<{0}>", propertyType); // Assign range hinting IRI (provided simple ) OntologyClass[] namedRanges = oProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray(); if (namedRanges.Count() > 0) { UriNode rangeAsUriNode = (UriNode)namedRanges.First().Resource; string rangeUri = rangeAsUriNode.Uri.ToString(); noteText += String.Format("\n<{0}>", rangeUri); } // Assign note text headerCellRange.AddComment(noteText); column++; } } } // Bold the header row and fit the columns so things look nice Range headerRow = newWorksheet.get_Range("A1").EntireRow; headerRow.Font.Bold = true; headerRow.Columns.AutoFit(); } } } } }
private void ButtonCreateExcel_Click(object sender, RoutedEventArgs e) { //папка с exe встроенная string BasePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); // Поиск стандартной папки и наименования документа для открытия в формате Excel string query_folder = "select receipt_of_materials.Default_Folder, receipt_of_materials.Name_Of_Document " + "from receipt_of_materials where receipt_of_materials.id_Document_Of_Receipt = @DocumentID "; MySqlCommand command_folder = new MySqlCommand(query_folder, connection); command_folder.Parameters.AddWithValue("@DocumentID", DocumentId); string Default_Folder = ""; string Name_Of_Document = ""; using (DbDataReader reader = command_folder.ExecuteReader()) { while (reader.Read()) { Default_Folder = reader.GetString(0); Name_Of_Document = reader.GetString(1); } } Excel.Application excel; // Вот если сделать именно так, то будет проверка на запуск Экселя, и если все ок, то идем дальше, иначе сразу вылетаем из метода try { excel = new Excel.Application(); } catch { MessageBox.Show("На вашем компьютере не установлен Excel. Печать невозможна.", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error); return; } // Стоял здесь false, нужно оттестить!!! excel.Visible = true; // Здесь наверно генерация данных в таблицы Экселя Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value); Worksheet sheet1 = (Worksheet)workbook.Sheets[1]; excel.DisplayAlerts = false; for (int j = 0; j < receiptrecordGrid.Columns.Count; j++) { Range myRange = (Range)sheet1.Cells[1, j + 1]; sheet1.Cells[1, j + 1].Font.Bold = true; sheet1.Columns[j + 1].ColumnWidth = 15; myRange.Value2 = receiptrecordGrid.Columns[j].Header; } for (int i = 0; i < receiptrecordGrid.Columns.Count; i++) { MessageBox.Show("На вашем компьютере не установлен Excel. Печать невозможна.", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error); for (int j = 0; j < receiptrecordGrid.Items.Count; j++) { TextBlock b = receiptrecordGrid.Columns[i].GetCellContent(receiptrecordGrid.Items[j]) as TextBlock; Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[j + 2, i + 1]; myRange.Value2 = b.Text; } } // Сохранение файла System.IO.Directory.CreateDirectory(BasePath + "\\" + Default_Folder); workbook.SaveAs(BasePath + "\\" + Default_Folder + "\\" + Name_Of_Document + ".xls", Excel.XlFileFormat.xlWorkbookNormal); workbook.Close(true); excel.Quit(); MessageBox.Show("Документ " + Name_Of_Document + " создан успешно.\n" + "Путь документа: " + BasePath + "\\" + Default_Folder); }
public Workbook DashBoard_Update(string path, Workbook wb, int row, List <string> result) { string sheet = "DashBoard"; string screenshothref = ""; Worksheet ws = null; Range rng1 = null; Range rng2 = null; Range rng3 = null; Range rng4 = null; Range rng = null; FileHandler file = new FileHandler(); object missing = Type.Missing; DateTime now = DateTime.Now; string date = now.ToString(); date = date.Replace(":", "-"); date = date.Replace("/", "-"); date = date.Replace(" ", "-"); FileHandler CurPath = new FileHandler(); string c = Directory.GetCurrentDirectory(); var stringSeparators = new[] { "bin" }; string[] str1 = c.Split(stringSeparators, StringSplitOptions.None); string imagepath = Parallel_Quest.ImagePath; try { ws = (Worksheet)wb.Sheets[sheet]; ws.Select(Type.Missing); rng1 = ws.get_Range("E" + row, missing); rng1.Value2 = result[3]; rng1.Font.Bold = true; string StepName = ws.Name + row.ToString(); if ((result[3].ToLower() == "pass")) { rng1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if ((result[3].ToLower() == "fail")) { screenshothref = Take_Screenshot(sheet, row, "fail", imagepath); // rng.Hyperlinks.Add(rng, screenshothref); rng1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } rng2 = ws.get_Range("F" + row, missing); rng2.Value2 = result[0]; rng2.Font.Bold = true; rng3 = ws.get_Range("G" + row, missing); rng3.Value2 = result[1]; rng3.Font.Bold = true; rng3.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); rng4 = ws.get_Range("H" + row, missing); rng4.Value2 = result[2]; rng4.Font.Bold = true; rng4.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); string tmpName = Path.GetTempFileName(); File.Delete(tmpName); wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng1); Marshal.FinalReleaseComObject(rng2); Marshal.FinalReleaseComObject(rng3); Marshal.FinalReleaseComObject(rng4); // Marshal.FinalReleaseComObject(ws); File.Delete(path); File.Copy(tmpName, path); return(wb); } catch (Exception) { GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng1); Marshal.FinalReleaseComObject(rng2); Marshal.FinalReleaseComObject(rng3); Marshal.FinalReleaseComObject(rng4); // Marshal.FinalReleaseComObject(ws); return(null); } }
public void WriteExcel(string path, string sheet, int row, string result) { string screenshothref = ""; Application excel = null; Workbook wb = null; Worksheet ws = null; Range rng = null; FileHandler file = new FileHandler(); object missing = Type.Missing; DateTime now = DateTime.Now; string date = now.ToString(); date = date.Replace(":", "-"); date = date.Replace("/", "-"); date = date.Replace(" ", "-"); FileHandler CurPath = new FileHandler(); string c = Directory.GetCurrentDirectory(); var stringSeparators = new[] { "bin" }; string[] str1 = c.Split(stringSeparators, StringSplitOptions.None); string imagepath = Parallel_Quest.ImagePath; try { excel = new Application(); //If I use Open or _Open it gives the same wb = excel.Workbooks.Open(path, missing, //updatelinks false, //readonly missing, //format missing, //Password missing, //writeResPass true, //ignoreReadOnly missing, //origin missing, //delimiter true, //editable missing, //Notify missing, //converter missing, //AddToMru missing, //Local missing); //corruptLoad ws = (Worksheet)wb.Sheets[sheet]; ws.Select(Type.Missing); rng = ws.get_Range("F" + row, missing); rng.Value2 = result.ToUpper(); rng.Font.Bold = true; string StepName = ws.Name + row.ToString(); if ((result.ToLower() == "pass")) { rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if ((result.ToLower() == "fail")) { screenshothref = Take_Screenshot(sheet, row, "fail", imagepath); rng.Hyperlinks.Add(rng, screenshothref); rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } string tmpName = Path.GetTempFileName(); File.Delete(tmpName); wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); wb.Close(false, missing, missing); GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); File.Delete(path); File.Move(tmpName, path); excel.Quit(); Marshal.FinalReleaseComObject(excel); } catch (Exception) { GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); excel.Quit(); Marshal.FinalReleaseComObject(excel); } }
private XLRow RowShift(Int32 rowsToShift) { return(Worksheet.Row(RowNumber() + rowsToShift)); }
/// <summary> /// Write excel file of a list of object as T /// Assume that maximum of 24 columns /// </summary> /// <typeparam name="T">Object type to pass in</typeparam> /// <param name="fileName">Full path of the file name of excel spreadsheet</param> /// <param name="objects">list of the object type</param> /// <param name="sheetName">Sheet names of Excel File</param> /// <param name="headerNames">Header names of the object</param> public void Create <T>( string fileName, List <T> objects, string sheetName, List <string> headerNames) { //Open the copied template workbook. using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = myWorkbook.AddWorkbookPart(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); // Create Styles and Insert into Workbook WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>(); Stylesheet styles = new CustomStylesheet(); styles.Save(stylesPart); string relId = workbookPart.GetIdOfPart(worksheetPart); Workbook workbook = new Workbook(); FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" }; SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart); Worksheet worksheet = new Worksheet(); //PageMargins pageM = worksheet.GetFirstChild<PageMargins>(); //SheetProtection sheetProtection = new SheetProtection(); //sheetProtection.Sheet = true; //sheetProtection.Objects = true; //sheetProtection.Scenarios = true; ////add column C:Z to allow edit range, which means column A,B and after Z are locked //ProtectedRanges pRanges = new ProtectedRanges(); //ProtectedRange pRange = new ProtectedRange(); //ListValue<StringValue> lValue = new ListValue<StringValue>(); //lValue.InnerText = "D1:Z1048576"; //pRange.SequenceOfReferences = lValue; //pRange.Name = "AllowEditRange1"; //pRanges.Append(pRange); //worksheet.InsertBefore(sheetProtection, pageM); //worksheet.InsertBefore(pRanges, pageM); int numCols = headerNames.Count; int width = headerNames.Max(h => h.Length) + 5; Columns columns = new Columns(); for (int col = 0; col < numCols; col++) { Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width); if (col == 0) { c.Hidden = BooleanValue.FromBoolean(true); } columns.Append(c); } worksheet.Append(columns); Sheets sheets = new Sheets(); Sheet sheet = new Sheet { Name = sheetName, SheetId = 1, Id = relId }; sheets.Append(sheet); workbook.Append(fileVersion); workbook.Append(sheets); worksheet.Append(sheetData); worksheetPart.Worksheet = worksheet; worksheetPart.Worksheet.Save(); myWorkbook.WorkbookPart.Workbook = workbook; myWorkbook.WorkbookPart.Workbook.Save(); myWorkbook.Close(); } }
/// <summary> /// 激活工作表 /// </summary> /// <param name="sheetName">工作表名</param> public void ActivateSheet(string sheetName) { Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); }
/// <summary> /// 激活工作表 /// </summary> /// <param name="sheetNum">工作表序号</param> public void ActivateSheet(int sheetNum) { Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetNum]; worksheet.Activate(); }
/// <summary> /// 重命名工作表 /// </summary> /// <param name="sheetNum">工作表序号,从左到右,从开始</param> /// <param name="newSheetName">新的工作表名</param> public void ReNameSheet(int sheetNum, string newSheetName) { Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetNum]; worksheet.Name = newSheetName; }
/// <summary> /// 重命名工作表 /// </summary> /// <param name="oldSheetName">原有工作表名</param> /// <param name="newSheetName">新的工作表名</param> public void ReNameSheet(string oldSheetName, string newSheetName) { Worksheet worksheet = (Worksheet)myExcel.Worksheets[oldSheetName]; worksheet.Name = newSheetName; }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) { System.IO.Directory.CreateDirectory(dataDir); } // Create a Workbook. Workbook wbk = new Workbook(); // Create a Worksheet and get the first sheet. Worksheet worksheet = wbk.Worksheets[0]; // Create a Cells object ot fetch all the cells. Cells cells = worksheet.Cells; // Merge some Cells (C6:E7) into a single C6 Cell. cells.Merge(5, 2, 2, 3); // Input data into C6 Cell. worksheet.Cells[5, 2].PutValue("This is my value"); // Create a Style object to fetch the Style of C6 Cell. Style style = worksheet.Cells[5, 2].GetStyle(); // Create a Font object Font font = style.Font; // Set the name. font.Name = "Times New Roman"; // Set the font size. font.Size = 18; // Set the font color font.Color = System.Drawing.Color.Blue; // Bold the text font.IsBold = true; // Make it italic font.IsItalic = true; // Set the backgrond color of C6 Cell to Red style.ForegroundColor = System.Drawing.Color.Red; style.Pattern = BackgroundType.Solid; // Apply the Style to C6 Cell. cells[5, 2].SetStyle(style); // Save the Workbook. wbk.Save(dataDir + "mergingcells.out.xls"); // ExEnd:1 }
private void btn导入_Click(object sender, EventArgs e) { if (prevMonth.Year != Convert.ToInt32(year.Value) || prevMonth.Month != Convert.ToInt32(month.Text)) { if (MessageBox.Show("您导入的不是上月的数据,您要继续吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false) != DialogResult.Yes) { return; } } if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { CreateWaitDialog("正在导入数据...", "请稍等"); Workbook workbook = new Workbook(openFileDialog1.FileName); Worksheet sheet = workbook.Worksheets[0]; Cells cells = sheet.Cells; currRows.Clear(); int totalCount = 0; List <Row> errorRows = new List <Row>(); foreach (Row row in sheet.Cells.Rows) { try { OtherMoneyData item = new OtherMoneyData(); item.年 = Convert.ToInt32(year.Value); item.月 = Convert.ToInt32(month.Text); item.员工编号 = (string)cells[row.Index, 0].StringValue; item.姓名 = (string)cells[row.Index, 1].StringValue; item.类型 = (string)cells[row.Index, 2].StringValue; item.项目名称 = (string)cells[row.Index, 3].StringValue; item.金额 = Convert.ToDecimal(cells[row.Index, 4].Value); currRows.Add(item); totalCount++; } catch { errorRows.Add(row); } } CloseWaitDialog(); gridControl1.RefreshDataSource(); string errMsg = ""; foreach (Row row in errorRows) { if (errMsg != "") { errMsg += "、"; } errMsg += (row.Index + 1).ToString(); } string msg = "导入完毕," + totalCount + " 成功, " + errorRows.Count + " 失败。\n\n失败行:" + errMsg; MessageBox.Show(msg); } }
public byte[] GetZXListToFile(string yhm, string xm, string isrelease) { using (DBConnection dbc = new DBConnection()) { try { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 14; //文字大小 style2.Font.IsBold = true; //粗体 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线 style2.IsLocked = true; //样式3 Style style4 = workbook.Styles[workbook.Styles.Add()]; style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 11; //文字大小 style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; cells.SetRowHeight(0, 20); cells[0, 0].PutValue("专线名称"); cells[0, 0].SetStyle(style2); cells.SetColumnWidth(0, 20); cells[0, 1].PutValue("登录名"); cells[0, 1].SetStyle(style2); cells.SetColumnWidth(1, 20); cells[0, 2].PutValue("电话"); cells[0, 2].SetStyle(style2); cells.SetColumnWidth(2, 20); cells[0, 3].PutValue("线路"); cells[0, 3].SetStyle(style2); cells.SetColumnWidth(3, 20); cells[0, 4].PutValue("是否可以自行发布运费券"); cells[0, 4].SetStyle(style2); cells.SetColumnWidth(4, 20); cells[0, 5].PutValue("审核时间"); cells[0, 5].SetStyle(style2); cells.SetColumnWidth(5, 20); cells[0, 6].PutValue("开放次数"); cells[0, 6].SetStyle(style2); cells.SetColumnWidth(6, 20); string where = ""; if (!string.IsNullOrEmpty(yhm.Trim())) { where += " and " + dbc.C_Like("a.UserName", yhm.Trim(), LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(xm.Trim())) { where += " and " + dbc.C_Like("a.UserXM", xm.Trim(), LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(isrelease.Trim())) { where += " and " + dbc.C_EQ("a.IsCanRelease", Convert.ToInt32(isrelease)); } string str = @"select a.*,b.fqcs from [tb_b_user] a left join (select count(SaleRecordID) as fqcs,SaleRecordUserID from tb_b_salerecord where status=0 and SaleRecordLX!=0 group by SaleRecordUserID) b on a.UserID=b.SaleRecordUserID where a.IsSHPass=1 and a.ClientKind=1 " + where + @" order by a.AddTime desc,a.UserName,a.UserXM"; //开始取分页数据 System.Data.DataTable dt = dbc.ExecuteDataTable(str); for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 1, 0].PutValue(dt.Rows[i]["UserXM"]); cells[i + 1, 0].SetStyle(style4); cells[i + 1, 1].PutValue(dt.Rows[i]["UserName"]); cells[i + 1, 1].SetStyle(style4); if (dt.Rows[i]["UserTel"] != null && dt.Rows[i]["UserTel"].ToString() != "") { cells[i + 1, 2].PutValue(dt.Rows[i]["UserTel"]); } cells[i + 1, 2].SetStyle(style4); var xl = ""; if (dt.Rows[i]["FromRoute"] != null && dt.Rows[i]["FromRoute"].ToString() != "") { xl += dt.Rows[i]["FromRoute"].ToString(); } if (dt.Rows[i]["ToRoute"] != null && dt.Rows[i]["ToRoute"].ToString() != "") { xl += "─" + dt.Rows[i]["ToRoute"].ToString(); } cells[i + 1, 3].PutValue(xl); cells[i + 1, 3].SetStyle(style4); var can = ""; if (dt.Rows[i]["IsCanRelease"] != null && dt.Rows[i]["IsCanRelease"].ToString() != "") { if (Convert.ToInt32(dt.Rows[i]["IsCanRelease"].ToString()) == 1) { can += "可以"; } else { can += "不可以"; } } cells[i + 1, 4].PutValue(can); cells[i + 1, 4].SetStyle(style4); if (dt.Rows[i]["canReleaseTime"] != null && dt.Rows[i]["canReleaseTime"].ToString() != "") { cells[i + 1, 5].PutValue(Convert.ToDateTime(dt.Rows[i]["canReleaseTime"]).ToString("yyyy-MM-dd")); } cells[i + 1, 5].SetStyle(style4); if (dt.Rows[i]["fqcs"] != null && dt.Rows[i]["fqcs"].ToString() != "") { cells[i + 1, 6].PutValue(dt.Rows[i]["fqcs"]); } cells[i + 1, 6].SetStyle(style4); } MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
private string _ExportToExcel(ConfigurationKpiAchievementsViewModel viewModel) { string dateFormat = "dd-mmm-yy"; string workSheetName = new StringBuilder(viewModel.PeriodeType).ToString(); switch (viewModel.PeriodeType) { case "Yearly": dateFormat = "yyyy"; break; case "Monthly": dateFormat = "mmm-yy"; workSheetName = string.Format("{0}_{1}", workSheetName, viewModel.Year); break; default: dateFormat = "dd-mmm-yy"; workSheetName = string.Format("{0}_{1}-{2}", workSheetName, viewModel.Year, viewModel.Month.ToString().PadLeft(2, '0')); break; } string fileName = new StringBuilder(workSheetName).Append(".xls").ToString(); var path = System.Web.HttpContext.Current.Request.MapPath(TemplateDirectory + "/KpiAchievement/"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string resultFilePath = System.Web.HttpContext.Current.Request.MapPath(string.Format("{0}/KpiAchievement/{1}", TemplateDirectory, fileName)); Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; worksheet.Name = workSheetName; workbook.Worksheets.ActiveWorksheet = worksheet; RowCollection rows = workbook.Worksheets[0].Rows; ColumnCollection columns = workbook.Worksheets[0].Columns; Row HeaderRow = rows[0]; HeaderRow.FillColor = Color.DarkGray; HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center; HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center; Column KpiIdColumn = columns[0]; Column KpiNameColumn = columns[1]; KpiIdColumn.Visible = false; HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID"; HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name"; int i = 1; //i for row foreach (var kpi in viewModel.Kpis) { worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id; worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement); int j = 2; // for column foreach (var achievement in kpi.KpiAchievements) { worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode; worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat; worksheet.Cells[HeaderRow.Index, j].AutoFitColumns(); worksheet.Cells[i, j].Value = achievement.Value; worksheet.Cells[i, j].NumberFormat = "#,0.#0"; worksheet.Columns[j].AutoFitColumns(); j++; } Column TotalValueColumn = worksheet.Columns[j]; if (i == HeaderRow.Index + 1) { worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average"; worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM"; Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i); worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1()); worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1()); } else { // add formula Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i); worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1()); worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1()); } i++; } KpiNameColumn.AutoFitColumns(); worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index); using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite)) { workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx); stream.Close(); } //workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml); //todo create file from viewModel return(string.Format("{0}KpiAchievement/{1}", TemplateDirectory, fileName)); }
public byte[] GetZXList2ToFile(string yhm, string xm, string ispass) { using (DBConnection dbc = new DBConnection()) { try { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 14; //文字大小 style2.Font.IsBold = true; //粗体 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线 style2.IsLocked = true; //样式3 Style style4 = workbook.Styles[workbook.Styles.Add()]; style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 11; //文字大小 style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; cells.SetRowHeight(0, 20); cells[0, 0].PutValue("专线名称"); cells[0, 0].SetStyle(style2); cells.SetColumnWidth(0, 20); cells[0, 1].PutValue("登录名"); cells[0, 1].SetStyle(style2); cells.SetColumnWidth(1, 20); cells[0, 2].PutValue("电话"); cells[0, 2].SetStyle(style2); cells.SetColumnWidth(2, 20); cells[0, 3].PutValue("线路"); cells[0, 3].SetStyle(style2); cells.SetColumnWidth(3, 20); cells[0, 4].PutValue("简介"); cells[0, 4].SetStyle(style2); cells.SetColumnWidth(4, 20); cells[0, 5].PutValue("是否通过"); cells[0, 5].SetStyle(style2); cells.SetColumnWidth(5, 20); string _url = ServiceURL + "tbbuserapply.selectApply"; string jsonParam = new JavaScriptSerializer().Serialize(new { tradeCode = "tbbuserapply.selectApply", status = ispass, userid = "", username = yhm, userxm = xm, //currentPage = 1, //pageSize = 10, closePagination = true }); var request = (HttpWebRequest)WebRequest.Create(_url); request.Method = "POST"; request.ContentType = "application/json;charset=UTF-8"; var byteData = Encoding.UTF8.GetBytes(jsonParam); var length = byteData.Length; request.ContentLength = length; var writer = request.GetRequestStream(); writer.Write(byteData, 0, length); writer.Close(); var response = (HttpWebResponse)request.GetResponse(); var responseString = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding("utf-8")).ReadToEnd(); ToJsonMy2 ToJsonMy2 = JsonConvert.DeserializeObject <ToJsonMy2>(responseString); //将json数据转化为对象类型并赋值给list zxlist[] list = ToJsonMy2.list; if (list.Length > 0) { for (int i = 0; i < list.Length; i++) { cells[i + 1, 0].PutValue(list[i].UserXM); cells[i + 1, 0].SetStyle(style4); cells[i + 1, 1].PutValue(list[i].UserName); cells[i + 1, 1].SetStyle(style4); if (list[i].param.usertel != null && list[i].param.usertel != "") { cells[i + 1, 2].PutValue(list[i].param.usertel); } cells[i + 1, 2].SetStyle(style4); var xl = ""; if (list[i].param.fromroute != null && list[i].param.fromroute != "") { xl += list[i].param.fromroute; } if (list[i].param.toroute != null && list[i].param.toroute != "") { xl += "─" + list[i].param.toroute; } cells[i + 1, 3].PutValue(xl); cells[i + 1, 3].SetStyle(style4); cells[i + 1, 4].PutValue(list[i].param.usercontent); cells[i + 1, 4].SetStyle(style4); var shzt = ""; if (list[i].status != null && list[i].status != "") { if (Convert.ToInt32(list[i].status) == 1) { shzt = "通过"; } else if (Convert.ToInt32(list[i].status) == 2) { shzt = "拒绝"; } else if (Convert.ToInt32(list[i].status) == 0) { shzt = "待审核"; } } else { shzt = "待审核"; } cells[i + 1, 5].PutValue(shzt); cells[i + 1, 5].SetStyle(style4); } } MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public byte[] getZFQListToFile(string userxm, string isVerifyType, string cx_beg, string cx_end) { using (DBConnection dbc = new DBConnection()) { try { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 14; //文字大小 style2.Font.IsBold = true; //粗体 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线 style2.IsLocked = true; //样式3 Style style4 = workbook.Styles[workbook.Styles.Add()]; style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中 style4.Font.Name = "宋体"; //文字字体 style4.Font.Size = 11; //文字大小 style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; cells.SetRowHeight(0, 20); cells[0, 0].PutValue("专线名称"); cells[0, 0].SetStyle(style2); cells.SetColumnWidth(0, 20); cells[0, 1].PutValue("线路"); cells[0, 1].SetStyle(style2); cells.SetColumnWidth(1, 20); cells[0, 2].PutValue("目的地"); cells[0, 2].SetStyle(style2); cells.SetColumnWidth(2, 20); cells[0, 3].PutValue("运费券"); cells[0, 3].SetStyle(style2); cells.SetColumnWidth(3, 20); cells[0, 4].PutValue("折扣"); cells[0, 4].SetStyle(style2); cells.SetColumnWidth(4, 20); cells[0, 5].PutValue("有效时间"); cells[0, 5].SetStyle(style2); cells.SetColumnWidth(5, 20); cells[0, 6].PutValue("开放时间"); cells[0, 6].SetStyle(style2); cells.SetColumnWidth(6, 20); cells[0, 7].PutValue("货物类型"); cells[0, 7].SetStyle(style2); cells.SetColumnWidth(7, 20); cells[0, 8].PutValue("重物/泡货类型"); cells[0, 8].SetStyle(style2); cells.SetColumnWidth(8, 20); cells[0, 9].PutValue("包装要求"); cells[0, 9].SetStyle(style2); cells.SetColumnWidth(9, 20); cells[0, 10].PutValue("发车时间"); cells[0, 10].SetStyle(style2); cells.SetColumnWidth(10, 20); cells[0, 11].PutValue("审核装态"); cells[0, 11].SetStyle(style2); cells.SetColumnWidth(11, 20); cells[0, 12].PutValue("审核时间"); cells[0, 12].SetStyle(style2); cells.SetColumnWidth(12, 20); string where = ""; if (!string.IsNullOrEmpty(userxm)) { where += " and " + dbc.C_Like("a.SaleRecordUserXM", userxm, LikeStyle.LeftAndRightLike); } if (!string.IsNullOrEmpty(isVerifyType)) { where += " and " + dbc.C_EQ("a.SaleRecordVerifyType", Convert.ToInt32(isVerifyType)); } if (!string.IsNullOrEmpty(cx_beg)) { where += " and a.SaleRecordTime >= " + dbc.ToSqlValue(Convert.ToDateTime(cx_beg)); } if (!string.IsNullOrEmpty(cx_end)) { where += " and a.SaleRecordTime < " + dbc.ToSqlValue(Convert.ToDateTime(cx_end).AddDays(1)); } string str = @"select a.*,b.ZXSaleListCitys,c.ZdMxMc as ProduceLx, d.ZdMxMc as PackLx,e.ZdMxMc as Fc,f.ZdMxMc as ZhLx,g.ZdMxMc as PhLx,h.FromRoute,h.ToRoute from tb_b_salerecord a left join tb_b_zxsalelist b on a.SaleRecordID=b.SaleRecordID left join tb_b_zdmx c on b.ZXSaleListProduceLx=c.ZdMxID left join tb_b_zdmx d on b.ZXSaleListPackLx=d.ZdMxID left join tb_b_zdmx e on b.ZXSaleListFc=e.ZdMxID left join tb_b_zdmx f on b.ZXSaleListZhLx=f.ZdMxID left join tb_b_zdmx g on b.ZXSaleListPhLx=g.ZdMxID left join tb_b_user h on a.SaleRecordUserID=h.UserID where a.status=0 and a.SaleRecordLX!=0 and a.SaleRecordVerifyType!=3 and b.status=0 " + where + @" order by a.addtime desc"; System.Data.DataTable dt = dbc.ExecuteDataTable(str); for (int i = 0; i < dt.Rows.Count; i++) { cells[i + 1, 0].PutValue(dt.Rows[i]["SaleRecordUserXM"]); cells[i + 1, 0].SetStyle(style4); var xl = ""; if (dt.Rows[i]["FromRoute"] != null && dt.Rows[i]["FromRoute"].ToString() != "") { xl += dt.Rows[i]["FromRoute"].ToString(); } if (dt.Rows[i]["ToRoute"] != null && dt.Rows[i]["ToRoute"].ToString() != "") { xl += "─" + dt.Rows[i]["ToRoute"].ToString(); } cells[i + 1, 1].PutValue(xl); cells[i + 1, 1].SetStyle(style4); cells[i + 1, 2].PutValue(dt.Rows[i]["ZXSaleListCitys"]); cells[i + 1, 2].SetStyle(style4); if (dt.Rows[i]["SaleRecordPoints"] != null && dt.Rows[i]["SaleRecordPoints"].ToString() != "") { cells[i + 1, 3].PutValue(dt.Rows[i]["SaleRecordPoints"]); } cells[i + 1, 3].SetStyle(style4); if (dt.Rows[i]["SaleRecordDiscount"] != null && dt.Rows[i]["SaleRecordDiscount"].ToString() != "") { cells[i + 1, 4].PutValue(dt.Rows[i]["SaleRecordDiscount"]); } cells[i + 1, 4].SetStyle(style4); if (dt.Rows[i]["ValidHour"] != null && dt.Rows[i]["ValidHour"].ToString() != "") { cells[i + 1, 5].PutValue(dt.Rows[i]["ValidHour"]); } cells[i + 1, 5].SetStyle(style4); if (dt.Rows[i]["SaleRecordTime"] != null && dt.Rows[i]["SaleRecordTime"].ToString() != "") { cells[i + 1, 6].PutValue(Convert.ToDateTime(dt.Rows[i]["SaleRecordTime"]).ToString("yyyy-MM-dd HH:mm:ss")); } cells[i + 1, 6].SetStyle(style4); if (dt.Rows[i]["ProduceLx"] != null && dt.Rows[i]["ProduceLx"].ToString() != "") { cells[i + 1, 7].PutValue(dt.Rows[i]["ProduceLx"]); } cells[i + 1, 7].SetStyle(style4); var lx = ""; if (dt.Rows[i]["ProduceLx"] != null && dt.Rows[i]["ProduceLx"].ToString() != "") { if (dt.Rows[i]["ProduceLx"].ToString() == "重货") { if (dt.Rows[i]["ZhLx"] != null && dt.Rows[i]["ZhLx"].ToString() != "") { lx = dt.Rows[i]["ZhLx"].ToString(); } } else if (dt.Rows[i]["ProduceLx"].ToString() == "泡货") { if (dt.Rows[i]["PhLx"] != null && dt.Rows[i]["PhLx"].ToString() != "") { lx = dt.Rows[i]["PhLx"].ToString(); } } } cells[i + 1, 8].PutValue(lx); cells[i + 1, 8].SetStyle(style4); if (dt.Rows[i]["PackLx"] != null && dt.Rows[i]["PackLx"].ToString() != "") { cells[i + 1, 9].PutValue(dt.Rows[i]["PackLx"]); } cells[i + 1, 9].SetStyle(style4); if (dt.Rows[i]["Fc"] != null && dt.Rows[i]["Fc"].ToString() != "") { cells[i + 1, 10].PutValue(dt.Rows[i]["Fc"]); } cells[i + 1, 10].SetStyle(style4); var shzt = ""; if (dt.Rows[i]["SaleRecordVerifyType"] != null && dt.Rows[i]["SaleRecordVerifyType"].ToString() != "") { if (Convert.ToInt32(dt.Rows[i]["SaleRecordVerifyType"].ToString()) == 1) { shzt = "通过"; } else if (Convert.ToInt32(dt.Rows[i]["SaleRecordVerifyType"].ToString()) == 2) { shzt = "拒绝"; } else if (Convert.ToInt32(dt.Rows[i]["SaleRecordVerifyType"].ToString()) == 0) { shzt = "待审核"; } } else { shzt = "待审核"; } cells[i + 1, 11].PutValue(shzt); cells[i + 1, 11].SetStyle(style4); if (dt.Rows[i]["SaleRecordVerifyTime"] != null && dt.Rows[i]["SaleRecordVerifyTime"].ToString() != "") { cells[i + 1, 12].PutValue(Convert.ToDateTime(dt.Rows[i]["SaleRecordVerifyTime"]).ToString("yyyy-MM-dd HH:mm:ss")); } cells[i + 1, 12].SetStyle(style4); } MemoryStream ms = workbook.SaveToStream(); byte[] bt = ms.ToArray(); return(bt); } catch (Exception ex) { throw ex; } } }
public override void RunCommand(object sender) { var engine = (IAutomationEngineInstance)sender; var vKeyColumn = v_KeyColumn.ConvertUserVariableToString(engine); var vValueColumn = v_ValueColumn.ConvertUserVariableToString(engine); var excelObject = v_InstanceName.GetAppInstance(engine); var excelInstance = (Application)excelObject; Worksheet excelSheet = excelInstance.ActiveSheet; Range sourceRange = excelSheet.UsedRange; var last = excelInstance.GetAddressOfLastCell(excelSheet); Range cellValue = excelSheet.Range["A1", last]; int rw = cellValue.Rows.Count; int cl = 2; int rCnt; int cCnt; DataTable DT = new DataTable(); for (rCnt = 2; rCnt <= rw; rCnt++) { DataRow newRow = DT.NewRow(); for (cCnt = 1; cCnt <= cl; cCnt++) { if (((cellValue.Cells[rCnt, cCnt] as Range).Value2) != null) { if (!DT.Columns.Contains(cCnt.ToString())) { DT.Columns.Add(cCnt.ToString()); } newRow[cCnt.ToString()] = ((cellValue.Cells[rCnt, cCnt] as Range).Value2).ToString(); } } DT.Rows.Add(newRow); } string cKeyName = ((cellValue.Cells[1, 1] as Range).Value2).ToString(); DT.Columns[0].ColumnName = cKeyName; string cValueName = ((cellValue.Cells[1, 2] as Range).Value2).ToString(); DT.Columns[1].ColumnName = cValueName; var dictlist = DT.AsEnumerable().Select(x => new { keys = (string)x[vKeyColumn], values = (string)x[vValueColumn] }).ToList(); Dictionary <string, string> outputDictionary = new Dictionary <string, string>(); foreach (var dict in dictlist) { outputDictionary.Add(dict.keys, dict.values); } outputDictionary.StoreInUserVariable(engine, v_OutputUserVariableName, nameof(v_OutputUserVariableName), this); }
private void CopyRange(ref CellRangeTemplate sourceRange, SheetData sheetData, Worksheet worksheet, ref CellPosition target, double?rowHeight = null) { #region Sheet sheetTemplate = sourceRange.CellRange.SheetTemplate; var workbookPartTemplate = sourceRange.CellRange.WorksheetPart; MergeCells mergeCellsTemplate = sourceRange.CellRange.MergeCells; MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>(); if (false && workbookPartTemplate.DrawingsPart != null && worksheet.WorksheetPart.DrawingsPart == null) { var drawingsPart = worksheet.WorksheetPart.AddPart <DrawingsPart>(workbookPartTemplate.DrawingsPart); drawingsPart = worksheet.WorksheetPart.DrawingsPart; if (!worksheet.WorksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any()) { worksheet.WorksheetPart.Worksheet.Append(new Drawing { Id = worksheet.WorksheetPart.GetIdOfPart(drawingsPart) }); } } Dictionary <string, MergeCell> mergeCellTemplateDic = sourceRange.CellRange.MergeCellsDic; #endregion CellPosition source = sourceRange.CellRange.Start; CellPosition offset = target.CalculateOffset(source); var cellsToCopy = document.FindCellsByRange(sourceRange.CellRange); for (int i = 0; i < cellsToCopy.Count(); i++) { var rowGroup = cellsToCopy.ElementAt(i); Row keyRow = rowGroup.Key; Row targetRow = new Row() { RowIndex = (UInt32)(keyRow.RowIndex + offset.Row), Height = (short)-1, }; if (rowHeight != null) { targetRow.Height = rowHeight; targetRow.CustomHeight = true; } MoveCurrentRow((int)targetRow.RowIndex.Value); sheetData.InsertBefore(targetRow, currentRow); foreach (Cell cellToCopy in rowGroup) { Cell targetCell = (Cell)cellToCopy.Clone(); targetCell.CellReference = CellPosition.OffsetIt(targetCell.CellReference, offset); targetRow.Append(targetCell); MergeCell _findMerge; if (mergeCellTemplateDic != null && mergeCellTemplateDic.TryGetValue(cellToCopy.CellReference.Value.ToUpper(), out _findMerge)) { var positionParent = _findMerge.Reference.Value.Split(':'); CellPosition offsetStart = new CellPosition(positionParent[0]); CellPosition offsetEnd = new CellPosition(positionParent[1]); var celRefNew = new CellPosition(targetCell.CellReference); if (mergeCells == null) { var a = new MergeCells(); worksheet.InsertAfter(a, sheetData); mergeCells = worksheet.GetFirstChild <MergeCells>(); } var mergeCell = new MergeCell(); mergeCell.Reference = celRefNew.ToString() + ":" + new CellPosition(celRefNew.Row + (offsetEnd.Row - offsetStart.Row), celRefNew.Column + (offsetEnd.Column - offsetStart.Column)).ToString(); mergeCells.Append(mergeCell); mergeCells.Count = (mergeCells.Count ?? 0) + 1; } } } }
/// <summary> /// This method will return the total amount of columns of a given excel file that are not null /// </summary> /// <param name="pathToExcel"></param> /// <returns>Integer with the total amount of columns</returns> public static int GetTotalAmountOfColumns(string pathToExcel) { //Se crea una instancia de una aplicación de Excel Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application(); //False para que no abra la aplicación, sino que lo haga "por atrás" myExcel.Visible = false; //Aquí usando la instancia de Aplicación de excel, abro el libro mandando como parámetro la ruta a mi archivo Microsoft.Office.Interop.Excel.Workbook workbook = myExcel.Workbooks.Open(pathToExcel); //Después uso una instancia de Worksheet (clase de Interop) para obtener la Hoja actual del archivo Excel Worksheet worksheet = myExcel.ActiveSheet; //En ese worksheet, en la propiedad de Name, tenemos el nombre de la hoja actual, que mando en el query 1 como parámetro //Console.WriteLine("WorkSheet.Name: " + worksheet.Name); string hojaExcel = worksheet.Name; bool errorDetected = false; int initialColumn = 1; int totalColumns = 0; while (!errorDetected) { try { if (worksheet.Cells[1, initialColumn].Value2 != null) { //initialRow++; totalColumns++; initialColumn++; //Console.WriteLine(worksheet.Cells[1, initialColumn].Value2 + " " + initialColumn); } else { break; } } catch (Exception e) { errorDetected = true; } } //Al finalizar tu proceso debes cerrar tu workbook workbook.Close(); myExcel.Quit(); //Con esto de Marshal se libera de manera completa el objeto desde Interop Services, si no haces esto //El objeto sigue en memoria, no lo libera C# Marshal.FinalReleaseComObject(worksheet); Marshal.FinalReleaseComObject(workbook); Marshal.FinalReleaseComObject(myExcel); GC.Collect(); GC.WaitForPendingFinalizers(); return(totalColumns); }
private void TransferExcelDataToDataBase(string SheetPath, string Sheetname, DataSet MisDataset) { // OleDbConnection ole = null; // OleDbDataAdapter da = null; // DataTable dt = null; // string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';", Sheetname.Trim()); // if ((System.IO.Path.GetExtension(Sheetname.Trim())).ToLower() == ".xls") // { // strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + Sheetname.Trim() + ";Extended Properties=Excel 5.0;Persist Security Info=False"; // } //// string sTableName = "1234"; // string strExcel = "select * from [1234$]"; // try // { // ole = new OleDbConnection(strConn); // ole.Open(); // DataTable OleDataTable = ole.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // string SheetName = OleDataTable.Rows[0][2].ToString().Trim(); // strExcel = String.Format("select * from[" + SheetName + "]"); // da = new OleDbDataAdapter(strExcel, ole); // dt = new DataTable(); // da.Fill(dt); // this.DataViewSheetview.DataSource = dt; // //因为生成Excel的时候第一行是标题,所以要做如下操作: // //1.修改DataGridView列头的名字, // //2.数据列表中删除第一行 // for (int i = 0; i < dt.Columns.Count; i++) // { // DataViewSheetview.Columns[i].HeaderCell.Value = dt.Rows[0][i].ToString();//c# winform 用代码修改DataGridView列头的名字,设置列名,修改列名 // } // //DataGridView删除行 // DataViewSheetview.Rows.Remove(DataViewSheetview.Rows[0]);//删除第一行 // ole.Close(); // } // catch (Exception ex) // { // MessageBox.Show(ex.Message); // } // finally // { // if (ole != null) // ole.Close(); // } // DataSet ds = new DataSet(); Workbook workBook = new Workbook(SheetPath); //string SheetName = workBook.Worksheets[0].Name; //Worksheet workSheet = workBook.Worksheets[Sheetname]; Worksheet workSheet = workBook.Worksheets[0]; Cells cell = workSheet.Cells; DataTable dt = new DataTable(); int count = cell.Columns.Count; for (int i = 0; i < count; i++) { string str = cell.GetRow(0)[i].StringValue; dt.Columns.Add(new DataColumn(str)); } for (int i = 1; i < cell.Rows.Count; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j < count; j++) { dr[j] = cell[i, j].StringValue; } dt.Rows.Add(dr); } dt.AcceptChanges(); this.DataViewSheetview.DataSource = dt; MisDataset.Tables.Add(dt); // return ds; }
private static Record EncodePictures(Dictionary <Pair <int, int>, Picture> pictures, SharedResource sharedResource, Worksheet worksheet) { MSODRAWING msoDrawing = new MSODRAWING(); MsofbtDgContainer dgContainer = new MsofbtDgContainer(); msoDrawing.EscherRecords.Add(dgContainer); MsofbtDg dg = new MsofbtDg(); dg.Instance = 1; dg.NumShapes = pictures.Count + 1; dg.LastShapeID = 1024 + pictures.Count; dgContainer.EscherRecords.Add(dg); MsofbtSpgrContainer spgrContainer = new MsofbtSpgrContainer(); dgContainer.EscherRecords.Add(spgrContainer); MsofbtSpContainer spContainer0 = new MsofbtSpContainer(); spContainer0.EscherRecords.Add(new MsofbtSpgr()); MsofbtSp shape0 = new MsofbtSp(); shape0.ShapeId = 1024; shape0.Flags = ShapeFlag.Group | ShapeFlag.Patriarch; shape0.Version = 2; spContainer0.EscherRecords.Add(shape0); spgrContainer.EscherRecords.Add(spContainer0); foreach (Picture pic in pictures.Values) { if (!sharedResource.Images.Contains(pic.Image)) { sharedResource.Images.Add(pic.Image); } MsofbtSpContainer spContainer = new MsofbtSpContainer(); MsofbtSp shape = new MsofbtSp(); shape.Version = 2; shape.ShapeType = ShapeType.PictureFrame; shape.ShapeId = 1024 + spgrContainer.EscherRecords.Count; shape.Flags = ShapeFlag.Haveanchor | ShapeFlag.Hasshapetype; spContainer.EscherRecords.Add(shape); MsofbtOPT opt = new MsofbtOPT(); opt.Add(PropertyIDs.LockAgainstGrouping, 33226880); opt.Add(PropertyIDs.FitTextToShape, 262148); opt.Add(PropertyIDs.BlipId, (uint)sharedResource.Images.IndexOf(pic.Image) + 1); spContainer.EscherRecords.Add(opt); MsofbtClientAnchor anchor = new MsofbtClientAnchor(); anchor.Row1 = pic.TopLeftCorner.RowIndex; anchor.Col1 = pic.TopLeftCorner.ColIndex; anchor.DX1 = pic.TopLeftCorner.DX; anchor.DY1 = pic.TopLeftCorner.DY; anchor.Row2 = pic.BottomRightCorner.RowIndex; anchor.Col2 = pic.BottomRightCorner.ColIndex; anchor.DX2 = pic.BottomRightCorner.DX; anchor.DY2 = pic.BottomRightCorner.DY; anchor.ExtraData = new byte[0]; spContainer.EscherRecords.Add(anchor); spContainer.EscherRecords.Add(new MsofbtClientData()); spgrContainer.EscherRecords.Add(spContainer); } return(msoDrawing); }
public static Workbook CreateExcel(ExcelFormat FormatType, bool Save = true) { //实例化workbook对象 Workbook workbook = new Workbook(); //获得工作簿中的表 Worksheet sheet = (Worksheet)workbook.Worksheets[FormatType.SheetIndex]; //获得指定Sheet中的所有单元格 Cells cells = sheet.Cells; //有行标题 if (FormatType.Columns.Count > 0) { //设置第一行行高 cells.SetRowHeight(0, FormatType.RowsSize); for (int i = 0; i < FormatType.Columns.Count; i++) { //设置列宽 cells.SetColumnWidth(i, FormatType.ColumnsSize); //单元格赋值 cells[0, FormatType.Columns[i].Y].PutValue(FormatType.Columns[i].Txt_Obj); //设置样式 cells[0, FormatType.Columns[i].Y].SetStyle(FormatType.Columns[i].CreateStyle()); } } //有行标题 if (FormatType.Rows.Count > 0) { //设置第一列列宽 cells.SetColumnWidth(0, FormatType.ColumnsSize); for (int i = 0; i < FormatType.Rows.Count; i++) { //设置行高 cells.SetRowHeight(i, FormatType.RowsSize); //单元格赋值 cells[FormatType.Rows[i].X, 0].PutValue(FormatType.Rows[i].Txt_Obj); //设置样式 cells[FormatType.Rows[i].X, 0].SetStyle(FormatType.Rows[i].CreateStyle()); //cells[i, 0].SetStyle(FormatType.Rows[i].CreateStyle()); } } if (FormatType.SCells.Count > 0) { foreach (var cell in FormatType.SCells) { //设置行高 cells.SetRowHeight(cell.X, FormatType.RowsSize); //设置列宽 cells.SetColumnWidth(cell.Y, FormatType.ColumnsSize); //设置文字换行 //单元格赋值 cells[cell.X, cell.Y].PutValue(cell.Txt_Obj); //设置样式 cells[cell.X, cell.Y].SetStyle(cell.CreateStyle()); } } if (Save) { try { workbook.Save(FormatType.SavePath); } catch (Exception ex) { Console.WriteLine("保存失败"); throw ex; } } return(workbook); }
//private ABIE_Paragraph xlTopMargin; //private ABIE_Paragraph xlLeftMargin; //private ABIE_Paragraph xlBotMargin; //private ABIE_Paragraph xlRightMargin; //private ABIE_Paragraph xlLeftHeader; //private ABIE_Paragraph xlCenterHeader; //private ABIE_Paragraph xlRightHeader; //private ABIE_Paragraph xlLeftFooter; //private ABIE_Paragraph xlCenterFooter; //private ABIE_Paragraph xlRightFooter; public ABIE_WorkSheet(Worksheet worksheet) { this.Worksheet = worksheet; }
private void DataSetTableToExcel(DataSet dataset) { DataTable Table = dataset.Tables[0]; //int RowNumber = Table.Rows.Count; //int ColumnNumber = Table.Columns.Count; //if (RowNumber == 0) //{ // MessageBox.Show("没有任何数据可以打印!"); //} //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //excel.Application.Workbooks.Add(true); //excel.Visible = true; //for (int i = 0; i < ColumnNumber; i++) //{ // excel.Cells[1, i + 1] = dataset.Tables[0].Columns[i].ColumnName; //} //for (int c = 0; c < RowNumber; c++) //{ // for (int j = 0; j < ColumnNumber; j++) // { // excel.Cells[c + 2, j + 1] = Table.Rows[c].ItemArray[j]; // } //} //string json = value.Value; //DataTable dt = Utils.JsonDataTableConvert.ToDataTable(json); //string fileName = tableTemplate.Name + ".xls"; //string savePath = Server.MapPath("~/Upload/TempExcelDownLoad/" + fileName); string FilePath = ""; SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "excel表格|*.xlsx|excel表格|*.xls|所有文件|*.*"; if (sfd.ShowDialog() == DialogResult.OK) { FilePath = System.IO.Path.GetFullPath(sfd.FileName); Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; Cells cells = sheet.Cells; int Colnum = Table.Columns.Count; //表格列数 int Rownum = Table.Rows.Count; //表格行数 //生成行 列名行 for (int i = 0; i < Colnum; i++) { cells[0, i].PutValue(Table.Columns[i].ColumnName); } //生成数据行 for (int i = 0; i < Rownum; i++) { for (int k = 0; k < Colnum; k++) { cells[1 + i, k].PutValue(Table.Rows[i][k].ToString()); } } book.Save(FilePath); } }
private bool ManipulateExcel() { try { //檢查查詢日期格式是否正確 if (txtMonth.Text.SubStr(5, 2) == "00") { MessageDisplay.Error("月份輸入錯誤!"); return(false); } txtMonth.Enabled = false; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); lblProcessing.Visible = true; ShowMsg("開始轉檔..."); string rptName, rptId, file; int f; rptName = "STF報價每月獎勵活動成績得獎名單月報表"; rptId = "50070"; ShowMsg(rptId + "-" + rptName + " 轉檔中..."); //讀取資料 daoRMM = new R_MARKET_MONTHLY(); string asYM = txtMonth.Text.Replace("/", ""); DataTable dt50070 = daoRMM.ListAllByDate(asYM); if (dt50070.Rows.Count == 0) { MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName)); return(false); } //複製檔案 file = PbFunc.wf_copy_file(rptId, rptId); if (file == "") { return(false); } //切換Sheet Workbook workbook = new Workbook(); workbook.LoadDocument(file); Worksheet ws50070 = workbook.Worksheets[0]; //填資料 Sheet1 ws50070.Import(dt50070, false, 2, 0); //讀取資料 dt50070 = daoRMM.ListAll2ByDate(asYM); if (dt50070.Rows.Count == 0) { MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName)); return(false); } //切換Sheet ws50070 = workbook.Worksheets[1]; //填資料 Sheet2 ws50070.Import(dt50070, false, 0, 0); //存檔 workbook.SaveDocument(file); ShowMsg("轉檔成功"); return(true); } catch (Exception ex) { MessageDisplay.Error("輸出錯誤"); throw ex; } finally { this.Cursor = Cursors.Arrow; this.Refresh(); Thread.Sleep(5); txtMonth.Enabled = true; } }
public static List <Record> Encode(Worksheet worksheet, SharedResource sharedResource) { List <Record> records = new List <Record>(); BOF bof = new BOF(); bof.BIFFversion = 0x0600; //0600H = BIFF8 bof.StreamType = StreamType.Worksheet; bof.BuildID = 3515; bof.BuildYear = 1996; bof.RequiredExcelVersion = 6; records.Add(bof); foreach (KeyValuePair <Pair <UInt16, UInt16>, UInt16> colWidth in worksheet.Cells.ColumnWidth) { COLINFO colInfo = new COLINFO(); colInfo.FirstColIndex = colWidth.Key.Left; colInfo.LastColIndex = colWidth.Key.Right; colInfo.Width = colWidth.Value; records.Add(colInfo); } DIMENSIONS dimensions = new DIMENSIONS(); if (worksheet.Cells.Rows.Count > 0) { dimensions.FirstRow = worksheet.Cells.FirstRowIndex; dimensions.FirstColumn = (Int16)worksheet.Cells.FirstColIndex; dimensions.LastRow = worksheet.Cells.LastRowIndex + 1; dimensions.LastColumn = (Int16)(worksheet.Cells.LastColIndex + 1); } records.Add(dimensions); // each Row Block contains 32 consecutive rows List <Record> rowblock = new List <Record>(32); List <Record> cellblock = new List <Record>(); for (int rowIndex = dimensions.FirstRow; rowIndex < dimensions.LastRow; rowIndex++) { if (worksheet.Cells.Rows.ContainsKey(rowIndex)) { Row sheetRow = worksheet.Cells.Rows[rowIndex]; ROW biffRow = new ROW(); biffRow.RowIndex = (UInt16)rowIndex; biffRow.FirstColIndex = (UInt16)sheetRow.FirstColIndex; biffRow.LastColIndex = (UInt16)(sheetRow.LastColIndex + 1); biffRow.RowHeight = sheetRow.Height; biffRow.Flags = 0x0F0100; // defaul value 0x0100 rowblock.Add(biffRow); for (int colIndex = sheetRow.FirstColIndex; colIndex <= sheetRow.LastColIndex; colIndex++) { Cell cell = sheetRow.GetCell(colIndex); if (cell != Cell.EmptyCell && cell.Value != null) { CellValue cellRecord = EncodeCell(cell, sharedResource); cellRecord.RowIndex = (UInt16)rowIndex; cellRecord.ColIndex = (UInt16)colIndex; cellRecord.XFIndex = (UInt16)sharedResource.GetXFIndex(cell.Format); cellblock.Add(cellRecord); } } if (rowblock.Count == 32) { records.AddRange(rowblock); records.AddRange(cellblock); rowblock.Clear(); cellblock.Clear(); } } } if (rowblock.Count > 0) { records.AddRange(rowblock); records.AddRange(cellblock); } if (worksheet.Pictures.Count > 0) { records.Add(EncodePictures(worksheet.Pictures, sharedResource, worksheet)); for (ushort id = 1; id <= worksheet.Pictures.Count; id++) { OBJ obj = new OBJ(); CommonObjectData objData = new CommonObjectData(); objData.ObjectID = id; objData.ObjectType = 8; objData.OptionFlags = 24593; obj.SubRecords.Add(objData); obj.SubRecords.Add(new End()); records.Add(obj); } } EOF eof = new EOF(); records.Add(eof); return(records); }