Tuesday, March 17, 2009

c# how to read write excel spreedsheet

用.net读写excel文件有很多种方法,这里介绍一种比较简单的:

使用Microsoft.Office.Interop.Excel

下面是一个我项目中的一个读取函数的删减版本,要用上using Excel = Microsoft.Office.Interop.Excel;

除了读取一个Range里的东西,下面的函数演示了怎么枚举所有的行列,这样就可以不用事先知道Range了。

private void LoadExcelFile(string annotationFile)
{
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;

try
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Open(annotationFile, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);


//Get a range of data.
range = objSheet.get_Range("A1", Missing.Value);
range = range.get_Resize(1, 1);
double videoTimeOffset = (double)range.get_Value(Missing.Value);

//Another way of getting data
string output = "";
int rowIndex = 3;
//object colIndex2 = 11;
while (((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2 != null)
{
string tmp = "";
int lapID;
SwimLap.StrokeTypeEnum strokeType;
string lapType; //Warm Up/Down Main

tmp = (((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2.ToString();
if (tmp.Equals(" ") || tmp.Equals("-"))
lapID = 0;
else
lapID = Convert.ToInt32(tmp);

tmp = (((Excel.Range)objSheet.Cells[rowIndex, 2]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 2]).Value2.ToString();
strokeType = SwimLap.GetStrokeTypeEnum(tmp);

lapType = (((Excel.Range)objSheet.Cells[rowIndex, 3]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 3]).Value2.ToString();


SwimLap sl = new SwimLap(videoTimeOffset,lapID,strokeType,lapType,lapTime,lapVideoStartTime,lapVideoEndTime,strokeCount,confidence,isTurn,isVideoFullLap,startType,strokeRate);

ss.AddLap(sl);

rowIndex++;

}

objBooks.Close();
objApp.Quit();

}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}