Click here to Skip to main content
15,918,041 members
Search within:


Home / Discussions / C#
   

C#

 
FirstPrevNext
GeneralRe: Copy or reference? Pin
Daniel Strigl7-Jul-02 22:26
Daniel Strigl7-Jul-02 22:26 
AnswerRe: Copy or reference? Pin
7-Jul-02 16:01
suss7-Jul-02 16:01 
Generalenabling pictures during processing Pin
Rüpel5-Jul-02 1:02
Rüpel5-Jul-02 1:02 
GeneralRe: enabling pictures during processing Pin
Rüpel5-Jul-02 2:06
Rüpel5-Jul-02 2:06 
GeneralRe: enabling pictures during processing Pin
leppie5-Jul-02 2:08
leppie5-Jul-02 2:08 
GeneralRe: enabling pictures during processing Pin
Rüpel5-Jul-02 2:28
Rüpel5-Jul-02 2:28 
GeneralEXCEL Access Pin
Özgür5-Jul-02 0:22
Özgür5-Jul-02 0:22 
GeneralRe: EXCEL Access Pin
7-Jul-02 16:16
suss7-Jul-02 16:16 
I never write c# code, but In c++ or java, I ever had access Excel, send the c++ codes, hope some help to you!
----------------------------------------------------------
package snsoftx.win;
import snsoft.util.*;
import com.jacob.com.*;
import com.jacob.activeX.*;
import java.util.*;
import java.io.File;

/**
* Company: 南北公司
* @author: Grant
* @email: wang_yi_ming@263.net
* @version 5.0
*
* MSExcelApp类用来操作Ms Excel文件:
* 新建，删除文件, Sheet, Row, Col
* comments:
* 要使用MSExcelApp类, 必须jacob.dll的支持
*
* expamples:
* 1. 只读打开文件,读数据:
* MSExcelApp excelApp = null;
* try
{
excelApp = new MSExcelApp();
if( excelApp.openExcel("c:\\temp\\cc.xls","Sheet1") )
{
int rowCount = excelApp.getRowCount();
int colCount = excelApp.getColumnCount();
System.out.println("rowCount=" + rowCount+",colCount=" + colCount);

// get column info
for (int col = 0; col < colCount; col++)
System.out.println("colName:" + excelApp.getColumnName(col) + "\r\n");

excelApp.prepareFetchData(); // 为了提高查询速度
for (int r = 0; r < rowCount; r++)
{
excelApp.gotoRow( r );
for (int c = 0; c < colCount; c++)
{
Object ob = excelApp.getValue(c);
// or ob = excelApp.getCellValue(r,c);
s += (ob == null) ? "" : ob.toString() + ",";
}
System.out.println(s + "\r\n");
}
excelApp.endFetchData();
excelApp.close();
}
}
catch (Exception ex) { ex.printStackTrace(); }

* 2. 打开文件,修改文件 :
* MSExcelApp excelApp = null;
* try
{
excelApp = new MSExcelApp();
if( excelApp.openExcel("c:\\temp\\cc.xls","学生成绩单",false,false,true) )
{
excelApp.addField("学号",0)
excelApp.addField("姓名",0)
excelApp.addField("语文",1)
excelApp.addField("数学",1)
excelApp.addField("英语",1)

excelApp.insertRow(true);
excelApp.setValue(0,"01");
excelApp.setValue(1,"张三");
excelApp.setValue(2,new Double("85.0") );
excelApp.setValue("数学",new Double("95.0") );
excelApp.setValue(4,new Double("90.5") );

excelApp.insertRow(true);
excelApp.setValue(0,"02");
excelApp.setValue(1,"李四");
excelApp.setValue(2,new Double("80.0") );
excelApp.setValue(3,new Double("90.0") );
excelApp.setValue(4,new Double("85.5") );

excelApp.saveChanges(); // 保存
excelApp.close();
}
}
catch (Exception ex) { ex.printStackTrace(); }
*
*/

public class MSExcelApp // extends AbstractDataSet
{
public ActiveXComponent excelApp;
private Dispatch workbooks;

private Dispatch workbook; // private Dispatch sheets;
private Dispatch sheet;

String m_szFileName;
String m_szSheetName;
boolean m_bNewFile;
boolean m_bReadOnly;
boolean m_bValidate;
boolean bPreparedFectch;

int rowCount, colCount;
int curRow;
Vector fldNames = new Vector();
boolean bCreateFile = false;
boolean visible;

public MSExcelApp()
{
this( false );
}
public MSExcelApp(boolean visible)
{
this.visible = visible;
}

/**
* Open for readOnly
*/
public boolean openExcel(String szFileName,String szSheetName)
{
return openExcel( szFileName,szSheetName, false,false,false);
}
public boolean openExcel(String szFileName,String szSheetName,boolean bReadOnly)
{
return openExcel(szFileName, szSheetName, bReadOnly,false,false);
}
public boolean openExcel(String szFileName,String szSheetName,
boolean bReadOnly, boolean bNewFile, boolean bNewSheet)
{
if( excelApp == null )
{
excelApp = new ActiveXComponent("Excel.Application");
if( visible )
excelApp.setProperty("Visible", new Variant(true));
}
workbooks = Dispatch.get(excelApp,"Workbooks").toDispatch();
bPreparedFectch = false;
curRow = -1;
rowCount = colCount = 0;

if( szFileName.indexOf('/') >= 0 )
szFileName = szFileName.replace('/','\\');

m_szFileName = szFileName;
m_bReadOnly = bReadOnly;
m_bNewFile = bNewFile;

try
{
// if( bNewFile
if( !bNewFile && isFileExist(szFileName) )
{
workbook = Dispatch.call(workbooks,"Open",szFileName).toDispatch();
m_bValidate = true;
}
else
{
workbook = Dispatch.call(workbooks,"Add").toDispatch();
bCreateFile = true;
m_bValidate = true;
}

if( bNewSheet && !isSheetExist(szSheetName) )
m_bValidate = newSheet( szSheetName );
else
{
m_bValidate = activeSheet( szSheetName );
if( m_bValidate )
{
innerGetRowColCount();

//PrepareFetchData();
for(int i=0; i<colcount; i++)
="" fldnames.addelement(="" innergetcolumnname(i)="" );
="" endfetchdata();
="" }
="" catch(exception="" ex)
="" {
="" m_bvalidate="false;
" ex.printstacktrace();
="" return="" m_bvalidate;
="" }

="" public="" boolean="" isfileexist(="" string="" szfilename="" )
="" try
="" file="" file(szfilename);
="" file.isfile();
="" ex)="" {}
="" false;
="" }


="" safearray="" sa;
="" int="" nametoindex(string="" szfldname)
="" for(int="" i="0;" i<fldnames.size();="" if(="" fldnames.elementat(i).tostring().equalsignorecase(="" szfldname="" )="" i;
="" -1;
="" addfield(string="" szcolname,="" ncoltype)
="" setcellvalue(-1,="" colcount,="" szcolname)="" szcolname="" set="" column="" attribute
="" *
="" selection.numberformatlocal="@"
="" *="" szformat="@" ;
="" if="" (="" ncoltype="=" com.borland.dx.dataset.variant.int="" ||="" com.borland.dx.dataset.variant.short
="" com.borland.dx.dataset.variant.byte="" com.borland.dx.dataset.variant.long="" else="" com.borland.dx.dataset.variant.bigdecimal="" com.borland.dx.dataset.variant.float="" com.borland.dx.dataset.variant.double="" com.borland.dx.dataset.variant.date="" com.borland.dx.dataset.variant.timestamp="" ;

="" colname="getCellColName(colCount);
//Message.out.println(colName+","+szColName+","+szFormat);
" object="" a1="Dispatch.invoke(sheet," "columns",="" dispatch.get,
="" new="" object[]="" {colname+":"+colname},
="" int[1]).todispatch();
="" end
="" dispatch.put(="" a1,="" "numberformatlocal",="" szformat);

="" colcount++;
="" true;
="" else
="" getcolumnname(int="" col)
="" col="">=0 && col < fldNames.size() )
return fldNames.elementAt(col).toString();
return "";
}
public int getColumnType(int col) { return 0; }

protected String getCellColName(int col)
{
col++;
int count = 0;
String s = "";
while( col > 0 )
{
int m = (col-1)%26;
col = (col-1)/26;
s = (char)('A'+m) + s;
}
return s;
}

protected String getCellName(int row, int col)
{
return getCellColName(col)+(row+2);
}

// zsb 11-29 cancel
/* static public void closeExcelApp()
{
if( excelApp != null )
{
Dispatch workbook = Dispatch.get(excelApp,"ActiveWorkbook").toDispatch();
Dispatch.call(workbook,"Close", new Variant(false) );
excelApp.invoke("Quit", new Variant[] {});
excelApp.release();
excelApp = null;
}
}
*/
public void close()
{
fldNames.removeAllElements();
if( excelApp != null && workbook != null )
{
endFetchData();
Dispatch.call(workbook,"Close", new Variant(false) );
workbook = null;
excelApp.invoke("Quit", new Variant[] {});
excelApp.release();
excelApp = null;
}
}

public String getFileName()
{
return m_szFileName;
}
boolean isReadOnly() { return m_bReadOnly; };


public void prepareFetchData()
{
if( !bPreparedFectch )
{
String cell1 = getCellName(-1,0);
String cell2 = getCellName(rowCount,colCount);

Dispatch range = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] {cell1+":"+cell2},
new int[1]).toDispatch();
Variant ret = Dispatch.get(range,"Value");
sa = ret.toSafeArray();
bPreparedFectch = true;
}
}
public void endFetchData()
{
if( bPreparedFectch )
{
if( sa != null )
{
sa.release();
sa = null;
}
bPreparedFectch = false;
}
}
boolean activeSheet(String szSheetName)
{
// Dispatch workbook = Dispatch.get(excelApp,"ActiveWorkbook").toDispatch();
Dispatch sheets = Dispatch.get(workbook,"Sheets").toDispatch();

int count = Dispatch.get(sheets,"Count").toInt();
for(int i=0; i<count; i++)
="" {
="" sheet="Dispatch.call(sheets," "item",="" new="" variant(i+1)).todispatch();

="" if(="" szsheetname.equalsignorecase(="" dispatch.get(sheet,"name").tostring()="" )="" )
="" dispatch.call(sheet,"activate");
="" return="" true;
="" }
="" false;
="" }

="" boolean="" issheetexist(string="" szsheetname)
="" dispatch="" workbook="Dispatch.get(excelApp,"ActiveWorkbook").toDispatch();
" sheets="Dispatch.get(workbook,"Sheets").toDispatch();

" int="" count="Dispatch.get(sheets,"Count").toInt();
" for(int="" i="0;" i<count;="" deletesheet(string="" variant(i+1)).todispatch();
="" try
="" dispatch.call(sheet,"delete");
="" catch(exception="" ex)="" {="" break;="" newsheet(string="" message.out.println("new="" sheet"+szsheetname);
="" "add").todispatch();
="" dispatch.put(sheet,"name",szsheetname);
="" ex)
="" protected="" void="" finalize()="" throws="" throwable="" close();="" public="" getrowcount()
="" rowcount;
="" getcolumncount()
="" colcount;
="" getrow()
="" currow;
="" gotorow(int="" row)
="" row="">=0 && row < rowCount )
curRow = row;
}

public Object getValue(int row,int columnIndex)
{
return getCellValue(row, columnIndex);
}

public Object getValue(int columnIndex)
{
return getCellValue(curRow, columnIndex);
}

public void insertRow(boolean after)
{
if( m_bReadOnly || !m_bValidate )
return;

try
{
if( !after )
curRow = rowCount++;
else
{
String cell1 = getCellName(curRow,0);
String cell2 = getCellName(curRow,colCount-1);

Dispatch range = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] {cell1+":"+cell2},
new int[1]).toDispatch();
// value = Dispatch.get(a1, "Value");
//Dispatch range = Dispatch.call(excelApp,"GetRange", new Variant(cell1),new Variant(cell2) ).toDispatch();
boolean bRet = Dispatch.call(range,"Insert", new Variant( (long)-4121 )).toBoolean();
if( bRet )
rowCount++;
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}

public void postRow()
{
if( m_bReadOnly || !m_bValidate || excelApp == null )
return ;

try
{
// Dispatch workbook = Dispatch.get(excelApp,"ActiveWorkbook").toDispatch();

if( bCreateFile )
Dispatch.call(workbook,"SaveAs",m_szFileName);
else
Dispatch.call(workbook,"Save");
}
catch(Exception ex)
{
ex.printStackTrace();
}
}

public boolean isRowChanged()
{
return false;
}
public boolean isNewRow()
{
return false;
}

public boolean deleteIndexRow(int row)
{
if( m_bReadOnly || !m_bValidate || row >= rowCount || row < 0 )
return false;

boolean bRet = false;
try
{
// Dispatch range = Dispatch.call(excelApp,"GetRange", GetCellName(row,0), GetCellName(row,colCount-1) ).toDispatch();
Dispatch range = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] { getCellName(row,0), getCellName(row,colCount-1) },
new int[1]).toDispatch();

bRet = Dispatch.call(range, "Delete", new Variant( (long)-4162 )).toBoolean();
}
catch(Exception ex) { }
if( bRet )
{
rowCount--;
if( curRow >= rowCount )
curRow = rowCount-1;
}
return bRet;
}

public void deleteRow()
{
deleteIndexRow(curRow);
}
public void saveChanges()
{
postRow();
}
/*
public boolean isChanged()
{
return false;
}

public boolean find(int findType,String filter)
{
return false;
}
public boolean find(String filter)
{
return false;
}
public boolean findNext(String filter)
{
return false;
}
public boolean seek(String comparision,Object keys[])
{
return false;
}
public void setCurrentIndex(String indexName)
{
}
*/
public boolean gotoFirst()
{
curRow = 0;
return rowCount > 0;
}
public boolean gotoLast()
{
curRow = rowCount-1;
return rowCount > 0;
}
public boolean gotoNext()
{
if( curRow < rowCount-1 )
{
curRow++;
return true;
}
return false;
}
public boolean gotoPrev()
{
if( curRow > 0 )
{
curRow--;
return true;
}
return false;
}
public void edit()
{
}
public void update()
{
postRow();
}

void innerGetRowColCount()
{
rowCount = colCount = 0;
if( !m_bValidate ) return;

Dispatch range = Dispatch.get(excelApp, "ActiveCell").toDispatch();
range = Dispatch.call(range, "SpecialCells", new Variant(11)).toDispatch();
Dispatch.call(range,"Select");

rowCount = Dispatch.get(range, "Row").toInt() - 1;
colCount = Dispatch.get(range, "Column").toInt();
}

String innerGetColumnName(int col)
{
Object o = getCellValue(-1,col);
return ( o == null ) ? "" : o.toString();
}

public Object getCellValue(int row, int col)
{
Object value = null;

if( m_bValidate && row >= -1 && col >= 0 && row < rowCount && col < colCount )
{
try
{
if( bPreparedFectch && sa != null )
value = sa.getVariant(row+2,col+1);
else
{
String cellName = getCellName(row,col);

Object a1 = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] {cellName},
new int[1]).toDispatch();
value = Dispatch.get(a1, "Value");
}
}
catch(Exception ex){ ex.printStackTrace(); }
}
Variant vTemp =(Variant)value;
if (vTemp.isNull())
value=null;
return value;
}
public boolean setCellValue(int row, int col,Object val)
{
if( !m_bValidate || m_bReadOnly )
return false;

if( row >= -1 && col >= 0 )
{
try
{
String cellName = getCellName(row,col);

Object a1 = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] {cellName},
new int[1]).toDispatch();
String text;
if( val == null )
text = "";
else if( val instanceof java.util.Date )
text = snsoft.util.Utilities.dateToString((java.util.Date)val);
else
text = val.toString();

Dispatch.put( a1, "FormulaR1C1", text); // Dispatch.put( a1, "Value", text);
return true;
}
catch(Exception ex) {}
}
return false;
}

public void setValue(int columnIndex,Object value)
{
setCellValue(curRow, columnIndex, value);
}

public void setValue(String colName,Object value)
{
setCellValue(curRow, nameToIndex(colName), value);
}

public static boolean newExcelSheet(String szFileName, String szSheetName)
{
boolean bFileExist = false;
try
{
File file = new File(szFileName);
bFileExist = file.isFile();
}
catch(Exception ex) { }

MSExcelApp excel = new MSExcelApp();
if( excel.openExcel(szFileName,szSheetName,true,bFileExist,true) )
{
try { excel.update(); return true; } catch(Exception ex) { return false; }
}
else
return false;
}

public static boolean isSheetExist(String szFileName, String szSheetName)
{
MSExcelApp excel = new MSExcelApp();
boolean bRet = excel.openExcel(szFileName,szSheetName,false);
excel.close();
return bRet;
}

public static boolean deleteSheet(String szFileName, String szSheetName)
{
MSExcelApp excel = new MSExcelApp();
if( excel.openExcel(szFileName,szSheetName, true) && excel.deleteSheet(szSheetName) )
{
try { excel.postRow(); return true; } catch(Exception ex) { return false; }
}
else
return false;
}
};
GeneralRemove focus from Form Pin
Bo Norgaard4-Jul-02 20:47
Bo Norgaard4-Jul-02 20:47 
GeneralRe: Remove focus from Form Pin
Bo Norgaard4-Jul-02 23:21
Bo Norgaard4-Jul-02 23:21 
GeneralWrite User in the Event Log Pin
Vanclei4-Jul-02 4:50
Vanclei4-Jul-02 4:50 
Generalunsafe code Pin
Member 169774-Jul-02 2:23
Member 169774-Jul-02 2:23 
GeneralRe: unsafe code Pin
James T. Johnson4-Jul-02 7:07
James T. Johnson4-Jul-02 7:07 
GeneralRe: unsafe code Pin
Nish Nishant4-Jul-02 15:24
sitebuilderNish Nishant4-Jul-02 15:24 
GeneralOk You C# People Pin
Swinefeaster4-Jul-02 1:11
Swinefeaster4-Jul-02 1:11 
GeneralRe: Ok You C# People Pin
leppie4-Jul-02 2:37
leppie4-Jul-02 2:37 
GeneralRe: Ok You C# People Pin
leppie4-Jul-02 10:51
leppie4-Jul-02 10:51 
GeneralRe: Ok You C# People Pin
Swinefeaster4-Jul-02 12:50
Swinefeaster4-Jul-02 12:50 
GeneralRe: Ok You C# People Pin
SHaroz5-Jul-02 6:12
SHaroz5-Jul-02 6:12 
QuestionWindows Explorer like Control ??? Pin
4-Jul-02 0:08
suss4-Jul-02 0:08 
AnswerRe: Windows Explorer like Control ??? Pin
SimonS4-Jul-02 0:42
SimonS4-Jul-02 0:42 
GeneralRe: Windows Explorer like Control ??? Pin
4-Jul-02 4:40
suss4-Jul-02 4:40 
GeneralComboBox SelectedItemChanged event Pin
paulb3-Jul-02 14:11
paulb3-Jul-02 14:11 
GeneralRe: ComboBox SelectedItemChanged event Pin
James T. Johnson3-Jul-02 14:43
James T. Johnson3-Jul-02 14:43 
GeneralRe: ComboBox SelectedItemChanged event Pin
paulb4-Jul-02 13:16
paulb4-Jul-02 13:16 
Last Visit: 31-Dec-99 18:00     Last Update: 16-Jun-24 0:38Refreshᐊ Prev1...15148151491515015151151521515315154151551515615157 Next ᐅ

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise
Privacy
Cookies
Terms of Use
Last Updated 12 Jun 2024
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-06-13:1