C#操作Excelのクラス
using System;
using Microsoft.Office.Core;
using Excel;
namespace XingFuStudio.ExcelEdit
{
class ExcelEdit
{
string myFileName;
Excel.Application myExcel;
Excel.Workbook myWorkBook;
/// <summary>
/// ， Excel
/// </summary>
public ExcelEdit()
{
//
// ：http://XingFuStar.cnblogs.com
}
/// <summary>
/// Excel
/// </summary>
public void CreateExcel()
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Application.Workbooks.Add(true);
}
/// <summary>
/// Excel
/// </summary>
public void ShowExcel()
{
myExcel.Visible = true;
}
/// <summary>
/// Excel
/// </summary>
/// <param name="data"> </param>
/// <param name="startRow">Excel </param>
/// <param name="startColumn">Excel </param>
public void WriteData(string[,] data, int startRow, int startColumn)
{
int rowNumber = data.GetLength(0);
int columnNumber = data.GetLength(1);
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < columnNumber; j++)
{
// Excel ， “'” ， ， ， 。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j];
}
}
}
/// <summary>
/// Excel
/// </summary>
/// <param name="data"> </param>
/// <param name="starRow"> </param>
/// <param name="startColumn"> </param>
public void WriteData(string data, int row, int column)
{
myExcel.Cells[row, column] = data;
}
/// <summary>
/// Excel
/// </summary>
/// <param name="data"> </param>
/// <param name="startRow">Excel </param>
/// <param name="startColumn">Excel </param>
public void WriteData(System.Data.DataTable data, int startRow, int startColumn)
{
for (int i = 0; i <= data.Rows.Count - 1; i++)
{
for (int j = 0; j <= data.Columns.Count - 1; j++)
{
// Excel ， “'” ， ， ， 。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data.Rows[i][j].ToString();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="row"> </param>
/// <param name="column"> </param>
/// <returns> </returns>
public string ReadData(int row, int column)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
return range.Text.ToString();
}
/// <summary>
/// Excel
/// </summary>
/// <param name="pictureName"> </param>
public void InsertPictures(string pictureName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
// ，
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
}
/// <summary>
/// Excel
/// </summary>
/// <param name="pictureName"> </param>
/// <param name="left"> </param>
/// <param name="top"> </param>
/// <param name="width"> </param>
/// <param name="heigth"> </param>
public void InsertPictures(string pictureName, int left, int top, int width, int heigth)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, top, left, heigth, width);
}
/// <summary>
///
/// </summary>
/// <param name="sheetNum"> ， ， 1 </param>
/// <param name="newSheetName"> </param>
public void ReNameSheet(int sheetNum, string newSheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Name = newSheetName;
}
/// <summary>
///
/// </summary>
/// <param name="oldSheetName"> </param>
/// <param name="newSheetName"> </param>
public void ReNameSheet(string oldSheetName, string newSheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[oldSheetName];
worksheet.Name = newSheetName;
}
/// <summary>
///
/// </summary>
/// <param name="sheetName"> </param>
public void CreateWorkSheet(string sheetName)
{
Excel.Worksheet newWorksheet = (Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = sheetName;
}
/// <summary>
///
/// </summary>
/// <param name="sheetName"> </param>
public void ActivateSheet(string sheetName)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
}
/// <summary>
///
/// </summary>
/// <param name="sheetNum"> </param>
public void ActivateSheet(int sheetNum)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Activate();
}
/// <summary>
///
/// </summary>
/// <param name="SheetName"> </param>
public void DeleteSheet(int sheetNum)
{
((Excel.Worksheet)myWorkBook.Worksheets[sheetNum]).Delete();
}
/// <summary>
///
/// </summary>
/// <param name="SheetName"> </param>
public void DeleteSheet(string sheetName)
{
((Excel.Worksheet)myWorkBook.Worksheets[sheetName]).Delete();
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.MergeCells = true;
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="hAlign"> </param>
/// <param name="vAlign"> </param>
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, ExcelHAlign hAlign, ExcelVAlign vAlign)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn)
{
CellsDrawFrame(startRow, startColumn, endRow, endColumn,
true, true, true, true, true, true, false, false,
LineStyle. , BorderWeight. , ColorIndex. );
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="isDrawTop"> </param>
/// <param name="isDrawBottom"> </param>
/// <param name="isDrawLeft"> </param>
/// <param name="isDrawRight"> </param>
/// <param name="isDrawHInside"> </param>
/// <param name="isDrawVInside"> </param>
/// <param name="isDrawDown"> </param>
/// <param name="isDrawUp"> </param>
/// <param name="lineStyle"> </param>
/// <param name="borderWeight"> </param>
/// <param name="color"> </param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn,
bool isDrawTop, bool isDrawBottom, bool isDrawLeft, bool isDrawRight,
bool isDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, bool isDrawDiagonalUp,
LineStyle lineStyle, BorderWeight borderWeight, ColorIndex color)
{
//
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
//
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle. ;
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle. ;
//
if (isDrawTop)
{
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeTop].ColorIndex = color;
}
if (isDrawBottom)
{
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeBottom].ColorIndex = color;
}
if (isDrawLeft)
{
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeLeft].ColorIndex = color;
}
if (isDrawRight)
{
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeRight].ColorIndex = color;
}
if (isDrawVInside)
{
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideVertical].ColorIndex = color;
}
if (isDrawHInside)
{
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = color;
}
if (isDrawDiagonalDown)
{
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalDown].ColorIndex = color;
}
if (isDrawDiagonalUp)
{
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalUp].ColorIndex = color;
}
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="color"> </param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = Pattern.Solid;
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="color"> </param>
/// <param name="pattern"> </param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="endRow"> </param>
/// <param name="height"> </param>
public void SetRowHeight(int startRow, int endRow, int height)
{
//
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
/// <summary>
///
/// </summary>
/// <param name="columnNum"> </param>
public void RowAutoFit(int rowNum)
{
//
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
///
/// </summary>
/// <param name="startColumn"> ( )</param>
/// <param name="endColumn"> ( )</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width)
{
//
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
///
/// </summary>
/// <param name="startColumn"> </param>
/// <param name="endColumn"> </param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width)
{
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
///
/// </summary>
/// <param name="columnNum"> </param>
public void ColumnAutoFit(string column)
{
//
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
///
/// </summary>
/// <param name="columnNum"> </param>
public void ColumnAutoFit(int columnNum)
{
string strcolumnNum = GetColumnName(columnNum);
//
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="color"> </param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.ColorIndex = color;
}
/// <summary>
/// ( , , )
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="isBold"> </param>
/// <param name="isItalic"> </param>
/// <param name="underline"> </param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}
/// <summary>
///
/// </summary>
/// <param name="startRow"> </param>
/// <param name="startColumn"> </param>
/// <param name="endRow"> </param>
/// <param name="endColumn"> </param>
/// <param name="fontName"> </param>
/// <param name="fontSize"> </param>
public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn,string fontName, int fontSize)
{
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}
/// <summary>
/// Excel
/// </summary>
/// <param name="fileName">Excel </param>
public void Open(string fileName)
{
myExcel = new Excel.Application();
myWorkBook = myExcel.Workbooks.Add(fileName);
myFileName = fileName;
}
/// <summary>
/// Excel
/// </summary>
/// <returns> True</returns>
public bool Save()
{
if (myFileName == "")
{
return false;
}
else
{
try
{
myWorkBook.Save();
return true;
}
catch (Exception ex)
{
return false;
}
}
}
/// <summary>
/// Excel
/// </summary>
/// <param name="fileName"> </param>
/// <returns> True</returns>
public bool SaveAs(string fileName)
{
try
{
myWorkBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return true;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// Excel
/// </summary>
/// <param name="isSave"> </param>
public void Close(bool isSave)
{
myWorkBook.Close(isSave, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// Excel
/// </summary>
/// <param name="isSave"> </param>
/// <param name="fileName"> </param>
public void Close(bool isSave,string fileName)
{
myWorkBook.Close(isSave, fileName, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
#region
private string GetColumnName(int number)
{
int h, l;
h = number / 26;
l = number % 26;
if (l == 0)
{
h -= 1;
l = 26;
}
string s = GetLetter(h) + GetLetter(l);
return s;
}
private string GetLetter(int number)
{
switch (number)
{
case 1:
return "A";
case 2:
return "B";
case 3:
return "C";
case 4:
return "D";
case 5:
return "E";
case 6:
return "F";
case 7:
return "G";
case 8:
return "H";
case 9:
return "I";
case 10:
return "J";
case 11:
return "K";
case 12:
return "L";
case 13:
return "M";
case 14:
return "N";
case 15:
return "O";
case 16:
return "P";
case 17:
return "Q";
case 18:
return "R";
case 19:
return "S";
case 20:
return "T";
case 21:
return "U";
case 22:
return "V";
case 23:
return "W";
case 24:
return "X";
case 25:
return "Y";
case 26:
return "Z";
default:
return "";
}
}
#endregion
}
/// <summary>
///
/// </summary>
public enum ExcelHAlign
{
= 1,
,
,
,
,
,
,
}
/// <summary>
///
/// </summary>
public enum ExcelVAlign
{
= 1,
,
,
,
}
/// <summary>
///
/// </summary>
public enum BorderWeight
{
= 1,
= 2,
= -4138,
= 4
}
/// <summary>
///
/// </summary>
public enum LineStyle
{
= 1,
= -4115,
= 4,
= 5,
= -4118,
= -4119,
= -4142,
= 13
}
/// <summary>
///
/// </summary>
public enum UnderlineStyle
{
= -4142,
= - 4119,
= 5,
= 2,
= 4
}
/// <summary>
///
/// </summary>
public enum Pattern
{
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}
/// <summary>
/// , Excel
/// </summary>
public enum ColorIndex
{
= -4142,
= -4105,
= 1,
= 53,
= 52,
= 51,
= 49,
= 11,
= 55,
80 = 56,
= 9,
= 46,
= 12,
= 10,
= 14,
= 5,
= 47,
50 = 16,
= 3,
= 45,
= 43,
= 50,
= 42,
= 41,
= 13,
40 = 48,
= 7,
= 44,
= 6,
= 4,
= 8,
= 33,
= 54,
25 = 15,
= 38,
= 40,
= 36,
= 35,
= 34,
= 37,
= 39,
= 2
}
}
回転:http://xingfustar.cnblogs.com