- using System;
- using System.Collections;
- using Excel=Microsoft.Office.Interop.Excel;
- namespace WindowsApplication1
- {
- ///
- /// 对Excel进行操作的类。
- ///
- public class JointExcel
- {
- #region 私有成员
- private Excel.ApplicationClass m_objExcel;//Excel应用程序对象
- private Excel.Workbooks m_objBooks;//Excel的Books对象
- private Excel.Workbook m_objBook;//当前Book对象
- private Excel.Worksheet m_objSheet;//当前Sheet对象
- private Excel.Range m_Range;//当前Range对象
- private System.Reflection.Missing miss =
- System.Reflection.Missing.Value;//空数据变量
- private Excel.Font m_Font;//当前单元格的字体属性对象
- private Excel.Borders m_Borders;//当前单元格或者区域的边框属性对象
- //单元格的四条边框对象
- private Excel.Border m_BorderTop;
- private Excel.Border m_BorderBottom;
- private Excel.Border m_BorderLeft;
- private Excel.Border m_BorderRight;
- private Excel.Range m_cellRange;//单元格Range对象，用来取得对象的Rows和Columns属性对象
- //单元格列号数组
- private string[] m_colString = new string[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"};
- #endregion
- ///
- /// 本类使用在web application中时，请在Web.Config中添加
- ///
- ///
- public JointExcel()
- {
- m_objExcel = new Excel.ApplicationClass();
- m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
- m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
- m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
- }
- ~JointExcel()
- {
- //释放所有Com对象
- if (m_cellRange != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_cellRange);
- if (m_BorderTop != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderTop);
- if (m_BorderBottom != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderBottom);
- if (m_BorderLeft != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderLeft);
- if (m_BorderRight != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_BorderRight);
- if (m_Borders != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Borders);
- if (m_Font != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Font);
- if (m_Range != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Range);
- if (m_objSheet != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
- if (m_objBook != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
- if (m_objBooks != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
- if (m_objExcel != null)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
- GC.Collect();
- }
- #region 选定单元格
- private string GetCell(int ColNum, int RowNum)
- {
- string temp = "A";
- int row = RowNum+1;
- if (ColNum < 0 || ColNum > 255)
- {
- throw new Exception("行号错误");
- }
- int i0, i1 = 0;
- i0 = Math.DivRem(ColNum, 25, out i1);
- if (i0 == 0 && i1 == 0)
- {
- return "A" + row.ToString();
- }
- if (i0 == 0 && i1 > 0)
- {
- return m_colString[i1] + row.ToString();
- }
- else
- {
- //return temp + m_colString[i0] + row.ToString();
- return m_colString[i0] + m_colString[i1] + row.ToString();
- }
- }
- ///
- /// 选定相应单元格
- ///
- /// int 列号
- /// int 行号
- public void SetRange(int ColNum, int RowNum)
- {
- m_Range = m_objSheet.get_Range((object)GetCell(ColNum, RowNum), miss);
- m_Font = m_Range.Font;
- m_Borders = m_Range.Borders;
- m_BorderTop = m_Borders[Excel.XlBordersIndex.xlEdgeTop];
- m_BorderBottom = m_Borders[Excel.XlBordersIndex.xlEdgeBottom];
- m_BorderLeft = m_Borders[Excel.XlBordersIndex.xlEdgeLeft];
- m_BorderRight = m_Borders[Excel.XlBordersIndex.xlEdgeRight];
- m_cellRange = m_Range;
- }
- ///
- /// 选择相应的区域
- ///
- /// 起始单元格列号
- /// 起始单元格行号
- /// 结束单元格列号
- /// 结束单元格行号
- public void SetRange(int startColNum, int startRowNum, int endColNum, int
- endRowNum)
- {
- m_Range =
- m_objSheet.get_Range((object)GetCell(startColNum, startRowNum), (object)GetCell(endColNum, endRowNum));
- m_Font = m_Range.Font;
- m_Borders = m_Range.Borders;
- m_BorderTop = m_Borders[Excel.XlBordersIndex.xlEdgeTop];
- m_BorderBottom = m_Borders[Excel.XlBordersIndex.xlEdgeBottom];
- m_BorderLeft = m_Borders[Excel.XlBordersIndex.xlEdgeLeft];
- m_BorderRight = m_Borders[Excel.XlBordersIndex.xlEdgeRight];
- m_cellRange = m_Range;
- }
- #endregion
- //开始具体的Excel操作
- #region 给单元格附值
- ///
- /// 给选定单元格附值
- ///
- /// 值
- public void SetCellValue(string value)
- {
- if (m_Range == null) throw new System.Exception("没有设定单元格或者区域");
- m_Range.Value2 = value;
- }
- ///
- /// 给选定单元格附值
- ///
- /// 列号
- /// 行号
- /// 值
- public void SetCellValue(int row, int col, string value)
- {
- SetRange(col, row);
- m_Range.Value2 = value;
- m_Range.Font.Name = "Arial";
- m_Range.Font.Size = 9;
- }
- ///
- /// 合并选定区域后给其附值
- ///
- /// 起始行号
- /// 起始列号
- /// 结束行号
- /// 结束列号
- /// 值
- public void SetCellValue(int startRow, int startCol, int endRow, int
- endCol, string value)
- {
- Merge(startRow, startCol, endRow, endCol);
- m_Range.Value2 = value;
- m_Range.Font.Size = 9;
- m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
- }
- #endregion
- public void SetCellbolk(int row, int col)
- {
- SetRange(col, row);
- m_Range.Font.Bold = true;
- }
- #region 设定单元格对齐方式
- ///
- /// 设定单元格中文字的对齐方式
- ///
- /// 对齐方式
- //public void SetHorizontal(JointEmun.ExcelAlign ea)
- //{
- // if (m_Range == null) throw new System.Exception("没有设定单元格或者区域");
- // switch (ea.ToString())
- // {
- // case "Left":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
- // break;
- // case "Right":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
- // break;
- // case "center":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
- // break;
- // default:
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
- // break;
- // }
- //}
- ///
- /// 设定单元格中文字的对齐方式
- ///
- /// 单元格行号
- /// 单元格列号
- /// 对齐方式
- // public void SetHorizontal(int rowIndex, int columnIndex, JointEmun.ExcelAlign
- //ea)
- // {
- // SetRange(columnIndex, rowIndex);
- // switch (ea.ToString())
- // {
- // case "Left":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
- // break;
- // case "Right":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
- // break;
- // case "center":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
- // break;
- // default:
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
- // break;
- // }
- // }
- ///
- /// 设定选定区域的对齐方式
- ///
- /// 起始行号
- /// 起始列号
- /// 结束行号
- /// 结束列号
- /// 对齐方式
- // public void SetHorizontal(int startRowIndex, int startColumnIndex, int
- //endRowIndex, int endColumnIndex, JointEmun.ExcelAlign ea)
- // {
- // SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
- // switch (ea.ToString())
- // {
- // case "Left":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
- // break;
- // case "Right":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
- // break;
- // case "center":
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
- // break;
- // default:
- // m_Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
- // break;
- // }
- // }
- #endregion
- #region 设置行高和列宽
- ///
- /// 设置列宽
- ///
- /// 列宽度
- public void SetColumnWidth(float columnWidth)
- {
- m_Range.ColumnWidth = columnWidth;
- }
- ///
- /// 设置列宽
- ///
- /// 列号
- /// 列宽度
- public void SetColumnWidth(int columnIndex, float columnWidth)
- {
- SetRange(columnIndex, 0);
- m_Range.ColumnWidth = columnWidth;
- }
- ///
- /// 设置行高
- ///
- /// 行宽度
- public void SetRowHeigh(float rowHeigh)
- {
- m_Range.RowHeight = rowHeigh;
- }
- ///
- /// 设置行高
- ///
- /// 行号
- /// 行宽度
- public void SetRowHeigh(int rowIndex, float rowHeigh)
- {
- SetRange(0, rowIndex);
- m_Range.RowHeight = rowHeigh;
- }
- #endregion
- #region 合并单元格
- ///
- /// 将选定区域中的单元格合并
- ///
- public void Merge()
- {
- m_Range.Merge(null);
- }
- ///
- /// 将选定区域中的单元格合并
- ///
- /// 起始行号
- /// 起始列号
- /// 结束行号
- /// 结束列号
- public void Merge(int startRowIndex, int startColumnIndex, int endRowIndex,
- int endColumnIndex)
- {
- SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
- m_Range.Merge(null);
- }
- #endregion
- #region 设置字体名称、大小
- ///
- /// 设置区域内的字体
- ///
- /// 起始行号
- /// 起始列号
- /// 结束行号
- /// 结束列号
- /// 字体名称
- public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex,
- int endColumnIndex, string fontName)
- {
- SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
- m_Font.Name = fontName;
- }
- ///
- /// 设置区域内的字号（文字大小）
- ///
- /// 起始行号
- /// 起始列号
- /// 结束行号
- /// 结束列号
- /// 字号
- public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex,
- int endColumnIndex, int fontSize)
- {
- SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
- m_Font.Size = fontSize;
- }
- ///
- /// 设置区域内的字体以及字号
- ///
- /// 起始行号
- /// 起始列号
- /// 结束行号
- /// 结束列号
- /// 字体名称
- /// 字号
- public void SetFont(int startRowIndex, int startColumnIndex, int endRowIndex,
- int endColumnIndex, string fontName, int fontSize)
- {
- SetRange(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex);
- m_Font.Name = fontName;
- m_Font.Size = fontSize;
- }
- ///
- /// 设置单元格的字体和字号
- ///
- /// 行号
- /// 列号
- /// 字体
- /// 字号
- public void SetFont(int rowIndex, int columnIndex, string fontName, int
- fontSize)
- {
- SetRange(columnIndex, rowIndex);
- m_Font.Name = fontName;
- m_Font.Size = fontSize;
- }
- ///
- /// 设置单元格的字体
- ///
- /// 行号
- /// 列号
- /// 字体
- public void SetFont(int rowIndex, int columnIndex, string fontName)
- {
- SetRange(columnIndex, rowIndex);
- m_Font.Name = fontName;
- }
- ///
- /// 设置单元格的字号
- ///
- /// 行号
- /// 列号
- /// 字号
- public void SetFont(int rowIndex, int columnIndex, int fontSize)
- {
- SetRange(columnIndex, rowIndex);
- m_Font.Size = fontSize;
- }
- ///
- /// 设定字体
- ///
- /// 字体
- public void SetFont(string fontName)
- {
- m_Font.Name = fontName;
- }
- ///
- #endregion
- public void setcolor(int rowSum, int colSum, int endrowSum, int endcolIndex,int color)
- {
- m_objSheet.get_Range(m_objExcel.Cells[rowSum, colSum], m_objExcel.Cells[endrowSum, endcolIndex]).Select();
- m_objSheet.get_Range(m_objExcel.Cells[rowSum, colSum], m_objExcel.Cells[endrowSum, endcolIndex]).Interior.ColorIndex = color;//设置为浅黄色，共
- }
- //畫邊框
- public void setline(int row, int col)
- {
- //xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
- //xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
- //xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
- //xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
- //xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
- SetRange(col, row);
- m_Range.Borders.LineStyle = 1;
- }
- public void setline(int srow, int scol, int erow, int ecol, int linetype)
- {
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders.LineStyle = linetype;
- }
- public void setlinebold(int srow, int scol, int erow, int ecol, int linetype)
- {
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders.LineStyle = linetype;
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
- }
- public void setline_left(int srow, int scol, int erow, int ecol, int linetype)
- {
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = linetype;
- }
- public void setline_right(int srow, int scol, int erow, int ecol, int linetype)
- {
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = linetype;
- }
- public void setline_top(int srow, int scol, int erow, int ecol, int linetype)
- {
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = linetype;
- }
- public void setline_btoon(int srow, int scol, int erow, int ecol, int linetype)
- {
- m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = linetype;
- }
- //賦值2
- public void SetCellValue2(int srow, int scol, int erow, int ecol,string value)
- {
- m_Range=m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]);
- m_Range.Value2 = value;
- m_Range.Font.Name = "Arial";
- m_Range.Font.Size = 9;
- }
- public void SetCellbolk2(int srow, int scol, int erow, int ecol)
- {
- m_Range = m_objSheet.get_Range(m_objExcel.Cells[srow, scol], m_objExcel.Cells[erow, ecol]);
- m_Range.Font.Bold = true;
- }
- public void save()
- {
- m_objBook.SaveAs(@"E:/Demo.xls", miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
- m_objBook.Close(miss, miss, miss);
- m_objExcel.Quit();
- GC.Collect();
- }
- }
- }
642
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
