|
Hi AndieDu
I did not read ur requirement fully.
But i designed the similar code and I got the data from the Database and stored the values
in the excel tablewise.
I think It may help you.
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient ;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Excel_12 = Microsoft.Office.Interop.Excel;
namespace WindowsExcelSheet
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel_12.Application oXL;
Excel_12._Workbook oWB;
Excel_12._Worksheet oSheet;
try
{
// Start Excel And get Application Object.
oXL = new Excel_12.Application();
oXL.Visible = true;
// Get a new Workbook
oWB = (Excel_12._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel_12._Worksheet)oWB.ActiveSheet;
// Add Table Headers going Cell by Cell
//oSheet.Cells[1, 1] = "SaleID";
//oSheet.Cells[1, 2] = "Product";
//oSheet.Cells[1, 3] = "SalePrice";
//Format A1:C1 as Bold, Vertical Alingment = Center.
oSheet.get_Range("A1", "C1").VerticalAlignment = Excel_12.XlVAlign.xlVAlignCenter;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Server=192.168.10.113;Database=AmitTestData;User ID=sa;Password=sa;Trusted_Connection=False;";
DataSet ds=new DataSet();
Bussiness objBussiness = new Bussiness();
objBussiness.fncReturnDataTable(conn, "select * from BigScreenProducts", ref ds,"Table0");
objBussiness.fncReturnDataTable(conn, "select * from Employee", ref ds, "Table1");
// sqladp1.Fill(ds,"Table2");
int tblIndex = 1;
int AscciVal = 65; // Ascci Value of Capital 'A'
// Table Count
for (int tblNo = 0; tblNo < ds.Tables.Count; tblNo++)
{
// Creating Headings in the Excel
string[] arrcolumn = new string[ds.Tables[tblNo].Columns.Count];
int ColCnt = ds.Tables[tblNo].Columns.Count;
for (int colname=0; colname < ds.Tables[tblNo].Columns.Count; colname++)
{
arrcolumn[colname] = ds.Tables[tblNo].Columns[colname].ColumnName;
}
char start = (char)AscciVal;
char end = (char)(AscciVal + ColCnt-1);
AscciVal = AscciVal + ColCnt+1; // Value set to the next cell of the Excel sheet
int rowCnt = ds.Tables[tblNo].Rows.Count + 1; // Couting no of rows in the current table
oSheet.Cells[1, tblIndex] = "Table" + tblNo.ToString(); // Filling Table Name in the Table
tblIndex = tblIndex - 1;
tblIndex = tblIndex + ColCnt + 2;
oSheet.get_Range(start + "2", end + "2").Value2 = arrcolumn;
oSheet.get_Range(start + "2", end + "2").Font.Bold = true;
string[,] arr1 = new string[rowCnt - 1, ColCnt];
for (int i = 0; i < ds.Tables[tblNo].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[tblNo].Columns.Count; j++)
{
arr1[i, j] = ds.Tables[tblNo].Rows[i].ItemArray[j].ToString();
}
}
oSheet.get_Range(start + "3", end + rowCnt.ToString()).Value2 = arr1;
}
oXL.Visible = true;
oXL.UserControl = true;
}
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");
}
}
private void button2_Click(object sender, EventArgs e)
{
}
private void btnStop_Click(object sender, EventArgs e)
{
this.Close();
}
// Function for Manupulate a variable no of columns
//private void DisplayQuarterlySales(Excel_12 oWS)
//{
// Excel_12._Workbook oWB;
// Excel_12.Series oSeries;
// Excel_12.Range oRecizeRange;
// Excel_12._Chart oChart;
// String sMsg;
// int iNumQtrs;
// // Display How many Quaters to Display Data for.
// for(
}
}
------------------------------------------
namespace WindowsExcelSheet
{
class Bussiness
{
public Bussiness()
{ }
public void fncReturnDataTable(SqlConnection conn, string strQuertString, ref DataSet ds, string strTabName)
{
SqlCommand cmd=null;
SqlDataAdapter sda=null;
try
{
cmd = new SqlCommand();
cmd.CommandText = strQuertString;
cmd.Connection = conn;
cmd.Connection.Open();
sda = new SqlDataAdapter(cmd);
sda.Fill(ds,strTabName);
}
catch (Exception Exp)
{
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
cmd.Dispose(); sda.Dispose();
}
}
}
}
|
|
|
|
|
thanks very much for your help mate
|
|
|
|
|
Hi All
I worked it out. lol
I can't use the StreamWriter to write the value into spreadsheet and save it, i have to use Excel.Workbook.SaveAs() method to save the spreadsheet.
Thanks for your help anyway.
Andie
|
|
|
|
|
I think no need to write
sw.Writer(ws.Cells[1, 1]);
sw.Writer(ws.Cells[1, 2]);
sw.Writer(ws.Cells[1, 3]);
Only
ws.Cells[1, 1] = "CardNo";
ws.Cells[1, 2] = "PharmacyName";
ws.Cells[1, 3] = "CostPerCard";
will give u the desired result.
I tried it's working. Try in this way.
|
|
|
|
|
thanks mate, i already worked it out myself, and your way does work.
|
|
|
|
|
Hi there. Hope this is in the right place since I *think* it's language specific.
I am currently creating an interop DirectX library and providing methods and structs on the C# side as needed. I'm translating one of the DirectX tutorial samples to test. Things were going swimmingly with all of the methods returning the correct data and setting relevant pointers etc, until I got to this line of code:
result = d3d10Engine.DXGISwapChain.GetBuffer(0, ref gs, out pBackBuffer);
At this point a SystemAccessViolation exception is thrown with message details "Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
The GetBuffer method is declared as:
[PreserveSig]<br />
int GetBuffer(uint Buffer, ref TestConfigurationManager.GuidStruct refiid, [Out] out IntPtr ppSurface);
where TestConfigurationManager.GuidStruct is defined as:
[StructLayout(LayoutKind.Sequential)]<br />
public struct GuidStruct<br />
{<br />
public uint Data1;<br />
public ushort Data2;<br />
public ushort Data3;<br />
public byte b0;<br />
public byte b1;<br />
public byte b2;<br />
public byte b3;<br />
public byte b4;<br />
public byte b5;<br />
public byte b6;<br />
public byte b7;<br />
}
The DirectX API defines the method signature as:
HRESULT GetBuffer(<br />
UINT Buffer,<br />
REFIID riid,<br />
void **ppSurface<br />
);
However, once the C++ preprocessor has done its bit, it actually looks like this:
long GetBuffer(<br />
uint Buffer,<br />
const GUID & riid,<br />
void **ppSurface<br />
);
where GUID is defined as:
typedef struct _GUID {<br />
unsigned long Data1;<br />
unsigned short Data2;<br />
unsigned short Data3;<br />
unsigned char Data4[ 8 ];<br />
} GUID;
Originally I had Data1 as ulong in the GuidStruct and then realised that ulong and unsigned long in C++ have different sizes and that could be causing the access violation by overrunning the stack allocation during marshaling but I've changed it to uint now and I still got the error. I thought it could perhaps be the void pointer parameter but IntPtr should cover that fine as it's just an integer address surely? So the last theory I have is that it has something to do with the fact that the unmanaged method receives a C++ reference and not a pointer. Trawling through the documentation on MSDN regarding marshaling it seems to me that if you're using the ref argument, the marshal copies a pointer onto the unmanaged stack to the memory space of the guid in the CLR. However, because the unmanaged side expects a reference, it doesn't deference the pointer and when it accesses what it thinks is a reference, an access violation results.
Is this the case and is there any way to deal with it, or would I have to resort to C++/CLI?
Thanks in advance.
|
|
|
|
|
OK. I've done some reading and discovered that C++ reference arguments are in actual fact just the memory addresses of the objects they refer to and the compiler inserts the code to dereference them so bang goes my theory. I'm even more stuck now.
|
|
|
|
|
Scratch all that. It is a completely different issue. Looks like the COM objects are not being created or something. Apologies for wasting anybody's time if I have.
|
|
|
|
|
Hi all!! 
Is there anyone who knows how to get the system font script types (like font dialog box) and load it into a combobox? I am using C# language for the program..
Thanx in advanced..
|
|
|
|
|
|
thanx for replying =)..
but I think maybe I didn't phrase my question well enough. Sorry to let you misunderstood.
I am having problems with the font script types ( western, simplified chinese, etc..). These will change depends on the font type that is being selected. But how can I load them into a combobox?
I had all things except for these now.
Hope that you can help me.
|
|
|
|
|
Hi,
I got a very strange problem. I create an Access database. It is working fine with me in my application which is in C#.
I have Access 2003 and my client too. While with my client the select query gives null data. I changes all the methods use OleDbAddaptor and OleDbDataReader functions.These functions are working fine at my end but with my client A Select Query is not working even the Table is present and data is present but the query is not working properly.
Please if you have any suggestion what should I do for this.
|
|
|
|
|
There may be a number of reasons, it would help if we knew what error message is generated.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
An Unhandled exception occurred in your application. If you click continue the application will ignore this error.....
in exception.Message the value is = Unknown
|
|
|
|
|
Ok that was useful - not!
I've seen something like this when there is a problem in the credentials or the connection (to SQL SERVER at least). Try getting an copy of his MDB back to your site to test and stepping through the code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Put a TRY/CATCH in there and see if that gives more information. Could be something simple or more complex but it might be displayed in the CATCH EXCEPTION.
P/s - it is never good to have a topic of Very Urgent please. The guys don't like that as time and help is offered free of charge.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
|
Hi All,
I am trying to pass the values to managed code( C# ) to unmanaged code( C++ ). The following is the example code
[DllImport("Helper.dll", SetLastError = true)]
[return: MarshalAs(UnmanagedType.Bool)]
private extern static bool Sample(long AccessibleContext,long vm);
<p> </p>
long ac = 10;
long id = 20;
Sample(ac,id);
<p> </p>
EXTERN_C __declspec(dllexport) BOOL Sample( long ac ,long id)
{
long sa;
sa = ac + id;
return true;
}
I am not always getting the value of the second parameter (ie "id") in C++ code, it comes as 0 but i am getting the first parameter value.
How can i resolve this problem?
Thanks in advance.
|
|
|
|
|
Hi. I think it's cos you are using longs in your C# code. longs in C# are 8 bytes instead of the 4 in C++. Try change them to ints in the C# side. Let me know if that works as I'm interested to find out.
|
|
|
|
|
Thanks Klempie..
After changing the long to int, it is working fine.
Thanks a lot.
|
|
|
|
|
No probs. I learnt something too.
|
|
|
|
|
In C++, we have pointer and address. How would we handle them when C++ code is converted to C#?
|
|
|
|
|
Hi There,
You do that by using the unsafe keyword, pointers are not safe type in c#, and as you know c# uses managed code which uses garbage collector, but in un-managed code the garbage collector cant interfear, pointers in c# requires writing unmanaged code, therefore you have to use the unsafe keyword.
Sincerely Samer Abu Rabie
Imagination is more important than knowledge !
|
|
|
|
|
I have a main thread with a mainform . I have a method that runs in a background thread that creates some new forms (of different type than mainform). The problem is that if i don't run the method in the mainthread then everything works fine. If i run this method in a background thread , and i make a mainthread.Invoke to create the form , then the form doesn't show up , also if i have checked that it's creation happens in the main thread.
How can i do?
How to create a form in a background thread ??
|
|
|
|
|
This works for me:
private void Form1_Load( object sender, EventArgs e )
{
new Thread( o => Invoke( ( Action ) ( () => new Form().Show() ) ) ).Start();
}
Nick
----------------------------------
Be excellent to each other
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
