/// <summary> /// Method to add an excel rows /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> /// <param name="values"></param> private void AddExcelRows (string startRange, int rowCount, int colCount, object values) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.set_Value(_optionalValue, values); }
/// <summary> /// Fill the excel sheet with data along with the position specified /// </summary> /// <param name="columnrow"></param> /// <param name="data"></param> private void FillExcelWithData() { _excelRange = _excelSheet.get_Range("A1", _value); _excelRange = _excelRange.get_Resize(RowCount + 1, ColumnCount); _excelRange.set_Value(Missing.Value, ExcelData); _excelRange.EntireColumn.AutoFit(); }
/// <summary> /// fill the header columns for the range specified and make it bold if specified /// </summary> /// <param name="headers"></param> /// <param name="startColumn"></param> /// <param name="endColumn"></param> public void FillHeaderColumn(object[] headers, string startColumn, string endColumn) { _excelRange = _excelSheet.get_Range(startColumn, endColumn); _excelRange.set_Value(_value, headers); if (BoldHeaders == true) { this.BoldRow(startColumn, endColumn); } _excelRange.EntireColumn.AutoFit(); }
static void Main(string[] args) { string file = AppDomain.CurrentDomain.BaseDirectory + "SetArrayToExcel.xlsx"; Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbook wb = excelApp.Workbooks.Open(file, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing); object[,] testTable = new object[2, 2] { { "6-Feb-10", 0.1 }, { "26-Mar-10", 1.2 } }; Excel.Worksheet ws = wb.ActiveSheet as Excel.Worksheet; Excel.Range rng = ws.get_Range("rngSetValue", Type.Missing); //rng.Value2 = testTable; rng.set_Value(Type.Missing, testTable); }
private void butexcel_Click(object sender, EventArgs e) { try { DataTable tb = null; string ss = ""; tb = (DataTable)this.headerUnitView1.DataSource; SystemCfg cfg2 = new SystemCfg(2); string hospitalName = cfg2.Config; ss = hospitalName + "药品费用统计"; // 创建Excel对象 Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { MessageBox.Show("Excel无法启动"); return; } // 创建Excel工作薄 Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1]; // 列索引,行索引,总列数,总行数 int colIndex = 0; int RowIndex = 0; int colCount = 0; int RowCount = tb.Rows.Count + 1; for (int i = 0; i <= tb.Columns.Count - 1; i++) { colCount = colCount + 1; } //查询条件 string swhere = ""; string tjfs = ""; string tjks = ""; string tjlx = ""; tjfs = "统计方式:" + cmbtjfs.Text; if (cmbtjfs.SelectedIndex != 1) { if (rdkd.Checked) { tjks = "统计科室:" + rdkd.Text; } else { tjks = "统计科室:" + rdgc.Text; } if (rbzy.Checked) { tjlx = "统计类型:" + rbzy.Text; } if (rbcy.Checked) { tjlx = "统计类型:" + rbcy.Text; } if (rbzs.Checked) { tjlx = "统计类型:" + rbzs.Text; } } swhere = tjfs + " " + tjks + " " + tjlx + " " + " 记费日期从:" + dtpBjksj.Value.ToString() + " 到 " + dtpEjksj.Value.ToString(); // 设置标题 Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]); range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = ss; xlApp.ActiveCell.Font.Size = 20; xlApp.ActiveCell.Font.Bold = true; xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; // 设置条件 Excel.Range range1 = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]); range1.MergeCells = true; Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[3, 5], xlApp.Cells[3, colCount]); range2.MergeCells = true; range2.set_Value(Type.Missing, "药品费用"); range2.HorizontalAlignment = Excel.Constants.xlCenter; // 创建缓存数据 object[,] objData = new object[RowCount + 2, colCount + 1]; // 获取列标题 for (int i = 0; i <= tb.Columns.Count - 1; i++) { if (i >= 4) { objData[1, colIndex++] = "药品费用"; } else { objData[1, colIndex++] = tb.Columns[i].Caption; } } for (int i = 0; i <= tb.Columns.Count - 1; i++) { objData[2, i] = tb.Columns[i].Caption; if (i < 4) { Excel.Range range3 = xlSheet.get_Range(xlApp.Cells[3, i + 1], xlApp.Cells[4, i + 1]); range3.MergeCells = true; range3.HorizontalAlignment = Excel.Constants.xlCenter; } } // 获取数据 objData[0, 0] = swhere; string lastName1 = ""; string lastName2 = ""; int rowBegin = 5; int rowEnd = 4; int rowBegin1 = 5; int rowEnd1 = 4; for (int i = 0; i <= tb.Rows.Count - 1; i++) { colIndex = 0; for (int j = 0; j <= tb.Columns.Count - 1; j++) { objData[i + 3, colIndex++] = "" + tb.Rows[i][j].ToString(); if (j == 0) { if (lastName1 != "" && !lastName1.Equals(tb.Rows[i][j].ToString())) { // Fun.DebugView(tb); MergerCell(xlApp, xlSheet, rowBegin, rowEnd, 1); rowBegin = rowEnd + 1; rowEnd = rowEnd + 1; lastName1 = tb.Rows[i][j].ToString(); } else { rowEnd = rowEnd + 1; } } if (j == 1) { if (lastName2 != "" && !lastName2.Equals(tb.Rows[i][j].ToString())) { MergerCell(xlApp, xlSheet, rowBegin1, rowEnd1, 2); rowBegin1 = rowEnd1 + 1; rowEnd1 = rowEnd1 + 1; lastName2 = tb.Rows[i][j].ToString(); } else { rowEnd1 = rowEnd1 + 1; } } } lastName1 = tb.Rows[i][0].ToString(); lastName2 = tb.Rows[i][1].ToString(); Application.DoEvents(); } // 写入Excel range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]); range.Value2 = objData; //合并单元格 // xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 3, colCount]).Borders.LineStyle = 1; //设置报表表格为最适应宽度 xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Select(); xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Columns.AutoFit(); xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 3, colCount]).Font.Size = 9; xlApp.Visible = true; } catch (Exception err) { MessageBox.Show(err.Message); } }
public void setValue(string colName, int row, string text) { Excel.Range range = m_objSheet.get_Range(colName + row.ToString(), miss); range.set_Value(miss, text); }
/// <summary> /// 给单元格赋值 /// </summary> /// <param name="col">列号</param> /// <param name="row">行号</param> /// <param name="text">值</param> public void setValue(int col, int row, string text) { Excel.Range range = m_objSheet.get_Range(this.GetAix(col, row), miss); range.set_Value(miss, text); }
public bool WriteExcelFile(string sReportName, string sSheetName, DateTime time1, DateTime time2, DataTable dt) { string[] date = new string[2]; date[0] = dealdate(time1); date[1] = dealdate(time2); StringBuilder sAddrModel = new StringBuilder(40); StringBuilder sAddrReport = new StringBuilder(40); string sFileModel = ""; string sFileDer = ""; string colA = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; char[] colABC = new char[26] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' }; GetPrivateProfileString("Addr", "AddrModel", "", sAddrModel, sAddrModel.Capacity, FILE_NAME); GetPrivateProfileString("Addr", "AddrReport", "", sAddrReport, sAddrReport.Capacity, FILE_NAME); sFileModel = sAddrModel.ToString() + sReportName.Trim() + ".xls"; if (date[0].CompareTo(date[1]) == 0) { sReportName = sReportName.Trim() + "(" + date[0] + ")"; } else { sReportName = sReportName.Trim() + "(" + date[0] + "-" + date[1] + ")"; } sFileDer = sAddrReport.ToString() + sReportName.Trim() + ".xls"; if (System.IO.File.Exists(sFileModel) == false) { MyLog.Log("3", "模板文件不存在"); return(false); } if (System.IO.File.Exists(sFileDer) == false) { System.IO.File.Copy(sFileModel, sFileDer, true); } else { System.IO.File.Delete(sFileDer); System.IO.File.Copy(sFileModel, sFileDer, true); } //从表中把数据导入SHEET中 #region string FileName; Excel.Workbooks eworkbooks = null; Excel._Workbook eworkbook = null; Excel.Sheets esheets = null; Excel._Worksheet esheet = null; Excel.Range range = null; excelApp.Visible = false; excelApp.UserControl = true; //用Excel应用程序创建Workbooks eworkbooks = excelApp.Workbooks; FileName = sFileDer; eworkbooks.Open(FileName, 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); //用Workbooks创建Workbook eworkbook = eworkbooks.get_Item(1); //用Workbook创建sheets esheets = eworkbook.Worksheets; int iSheetNumber = esheets.Count; int index = 0; for (int i = 1; i <= iSheetNumber; i++) { esheet = (Excel._Worksheet)esheets.get_Item(i); if (esheet.Name.Trim() == sSheetName.Trim()) { index++; break; } } if (index == 0) { esheet = null;//没有找到相应的SHEET页 } else { int _rows = dt.Rows.Count; int _cols = dt.Columns.Count; int j = 4; object[] date_write = new object[4]; date_write[0] = "日期:"; date_write[1] = date[0]; date_write[2] = "至"; date_write[3] = date[1]; range = esheet.get_Range("A" + 2, "D" + 2); range.set_Value(Missing.Value, date); date = null; for (int i = 0; i < _rows; i++) { j = i + 5;//指定从哪一行开始写入数据 object[] currentRow = new object[_cols]; currentRow = dt.Rows[i].ItemArray; range = esheet.get_Range("A" + j, colA.Substring(_cols - 1, 1) + j); range.set_Value(Missing.Value, currentRow); currentRow = null; } } eworkbook.Save(); range = null; eworkbook = null; esheet = null; esheets = null; eworkbooks.Close(); #endregion//excel写数据处理 return(true); }
/// <summary> /// 将数据填充到Excel工作表的单元格中 /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> /// <param name="values"></param> private void AddExcelRows(string startRange, int rowCount, int colCount, object values) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.set_Value(_optionalValue, values); }