ExcelTechnology
Export Data in DataSet to Excel
We often use Excel during working for it is convenient for saving and displaying data. Also, Excel is very powerful to calculate data. Actually, in order to store a large amount of data, every company has a small database. When we want to use the data, we should get it from database. Well, how to export data to Excel? This article focuses on converting data in DataSet to Excel.
If we want to export data in DataSet to Excel, it is essential to add Office COM references for we need some methods and properties when exporting Excel objects. For this method, we need to generate XML file firstly and then import to database.
public void DataSetToExcel(DataSet ds,string FileName)
{
try
{
//Define Web Page
//System.Web.UI.Page mypage=new System.Web.UI.Page();
HttpResponse resp;
resp=HttpContext.Current.Response;
resp.ContentEncoding=System.Text.Encoding.GetEncoding(“GB23123);
resp.AppendHeader(“Content-disposition”,”attachment;filename=”+FileName+”.xls”);
resp.ContentType=”application/ms-excel”;
//Define Variable
string colHeaders=null;
string Is_item=null;
//Display Format Definition////////////////
//Define File Stream Operation
//FileStream fs=new FileStream(FileName,FileMode.Create,FileAccess.Write);
//StreamWriter sw = new StreamWriter (fs, System. Text. Encoding. GetEncoding (“GB23123));
StringWriter sfw=new StringWriter();
//Define Table Object and Row Object, and Initialize Value with DataSet
System.Data.DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select();
int i=0;
int cl=dt.Columns.Count;
//Get Column Title of Data Table, and Segment Title with t. Add Enter Symbol after the Last Column Title
for(i=0;i
{
//if(i==(cl-1)) //Last column, add n
// colHeaders+=dt.Columns[i].Caption.ToString();
//else
colHeaders+=dt.Columns[i].Caption.ToString()+”t”;
}
sfw.WriteLine(colHeaders);
//sw.WriteLine(colHeaders);
//Deal with Data Row by Row
foreach(DataRow row in myRow)
{
//Type Data
for(i=0;i
{
//if(i==(cl-1))
// Is_item+=row[i].ToString()+”n”;
//else
Is_item+=row[i].ToString()+”t”;
}
sfw.WriteLine(Is_item);
//sw.WriteLine(Is_item);
Is_item=null;
}
resp.Write(sfw);
//resp.Clear();
resp.End();
}
catch(Exception e)
{
throw e;
}
}
Sometimes, we need to deal with lots of data exportation to Excel or Word. In order to save time and energy, we may use the third party Add-ins. In fact, some Add-in has high quality and can export a large amount of data quickly. As I know, Spire.DataExport specializes in exporting data to various format files. And it published the free component for exporting data recently. The free version remains the basic function to export data of official one. The difference is that the free version can’t satisfy customers with some additional requirements, for example, special data format setting.
How to Export Data in Access to Excel with VB.NET
We always search for the best way to export Access data to Excel for working more efficiently. It is no doubt that there are materials about this problem. And some people are very familiar with the method. However, we need to check if the method is suitable for the practical situation. Also, we will have many troubles when operating according to different requirements.
Microsoft Access and Excel are considered as the basic tools during the process of exporting. However, some developers may not install the two tools. At that time, how do they realize export data to Excel? Now, I want to introduce a way to export Access data to Excel with VB.Net if we don’t have Microsoft.
The following code shows us the detailed process
Private Sub btnMSAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMSAccess.Click
Dim oleDbConnection1 As New System.Data.OleDb.OleDbConnection()
oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=........Databasedemo.mdb
Dim oleDbCommand1 As New System.Data.OleDb.OleDbCommand()
oleDbCommand1.CommandText = "select * from parts"
oleDbCommand1.Connection = oleDbConnection1
Dim accessExport1 As New Spire.DataExport.Access.AccessExport()
accessExport1.DatabaseName = "test.mdb"
accessExport1.DataFormats.CultureName = "zh-CN"
accessExport1.DataFormats.Currency = "c"
accessExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
accessExport1.DataFormats.Float = "g"
accessExport1.DataFormats.[Integer] = "g"
accessExport1.DataFormats.Time = "H:mm"
accessExport1.SQLCommand = oleDbCommand1
accessExport1.TableName = "ExportData"
oleDbConnection1.Open()
accessExport1.SaveToFile()
End Sub
This method can export data in Access to Excel quickly. And this method can deal with a large amount of data. What’s more, if we want to export data to many different Excel Workbooks, this method is very useful for it can complete it with a short time.
Of course, we also can find several kinds of good add-in which specialize in exporting data. As I know, Spire.DataExport is very helpful. It not just exports data in database to Excel, but another file format, such as Word, PDF and so on. Recently, it published a free component version which offers customers to experience if it is suitable for their requirements.
To sum up, we should evaluate the methods for exporting Access data to Excel before we choose.
How to Convert Doc to PDF on Different Platform
Sometimes, we may share the excellent articles or papers online with other by sending them online with PDF format. We use PDF format because PDF can be readable across multiple platforms. No matter which operation system platform we use, PDF can be read.
Therefore, more and more Word documents are converted to PDF to be convenient for reading on different platform. The other reason that we want to convert doc file to PDF is that others can’t be edited PDF documents that our workers can be protected. But the problem is that how should we do to realize the conversion.
It is not very difficult to convert doc to PDF. The common method is to use document converters. Although users should pay for good converters at the beginning, there are several free converters developed recently. Users just need to follow the guides and then wait for the converted PDF documents.
Besides, on different platform, there are different methods to convert doc to PDF.
On MAC
Firstly, open the doc file which we want to convert it to PDF format. Then, select Print in File menu. Next, click the PDF button and select Save as PDF from the print menu. Print a copy of the doc file to PDF. Finally, give the converted PDF file a name and select save it to the specified path. After that, we can find that the doc file has been converted to PDF format.
On Windows
Also, we need to open the doc file which we want to convert. Then click Microsoft Office button, select “Save As” and select “PDF”. As is known, Microsoft Office can’t be converted to PDF directly so that we need to have a tool installed, Adobe Acrobat to save the document as PDF format. Next, save the named PDF to specified path and click Publish. Then, the document is displayed with PDF format.
Convert Word to PDF with C# and Avoid Error Happening during Process
Because Microsoft Word doesn’t provide us with a function to convert Word to PDF directly, so we often need to use software to realize conversion. We can find several good converters online. These converters are mainly offered for common users. While, developers may write code to complete the requirement. Of course, we can find many materials online about how to convert Word to PDF with C#. To certain degree, these materials can help developers. But developers should test practically to make sure if the methods are useful for them.
During process to use C# to convert Word to PDF, we may come across this problem that error happen when using ACRODISTLib.PdfDistillerClass. After converting one document, we can’t release it and the error happens when invoking ACRODISTLib.PdfDistillerClass again. How to solve this problem?
Firstly, we install the needed environment, for example, Microsoft Word. Please not that the printer installed is “MS Publisher Color Printer”, the default printer provided by MS. Then add references, Acrobat Distiller and Word 2003.
The following code is about the detailed method to convert Word to PDF with C#.
using oWord = Microsoft.Office.Interop.Word;
private void WordConvert()
{
oWord.ApplicationClass word = new Microsoft.Office.Interop.Word.ApplicationClass();
Type wordType= word.GetType();
var word = new ActiveXObject("Word.Application");
var doc = word.Documents.Open(docfile);
oWord.Documents docs = word.Documents;
Type docsType = docs.GetType();
object objDocName = @"c:test.doc";
oWord.Document doc = (oWord.Document)docsType.InvokeMember("Open", System.Reflection.BindingFlags.InvokeMethod, null, docs, new Object[] {objDocName, true, true});
//Print it to the specified file by using Type.InvokeMember to invoke
Type docType = doc.GetType();
object printFileName = @"c:test.ps";
docType.InvokeMember("PrintOut",System.Reflection.BindingFlags.InvokeMethod,null,doc,new object[]{false,false,oWord.WdPrintOutRange.wdPrintAllDocument,printFileName});
//new object[]{false,false,oWord.WdPrintOutRange.wdPrintAllDocument,printFileName}
//Quit WORD
wordType.InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod,null, word, null);
object o1= "c:\test.ps";
object o2= "c:\test.pdf";
object o3= "";
//Covert PS to PDF object
try
{
ACRODISTXLib.PdfDistillerClass pdf = new ACRODISTXLib.PdfDistillerClass();
Type pdfType = pdfConvert.GetType();
pdfType.InvokeMember("FileToPDF",System.Reflection.BindingFlags.InvokeMethod,null,pdf,new object[]{o1,o2,o3});
pdf = null;
}
Finally, in order to avoid the errors happening when invoking this method, we need to end acrodist.exe process.
Control Excel Font or Color of One Row by Using C#
Developers often use C# to operate Excel for realizing Excel automation. Generally speaking, there are several parts included in Excel automation, for example, Excel creation, data import and export and so on. In order to generate a complete Excel file, developers often have Excel formatted.
The frequently used formatting in Excel is font styles, which is one part of cell formatting. There are lots of font styles for Excel. When we open an Excel, we can find the Format in menu. After selecting Cell, kinds of formatting tools are displayed. Click the Font tab and we can select the wanted styles for the cells which are needed to be formatted.
Sometimes, we may make specified cells be obvious by using different color and size from other cells. The special cell may be the column title or the most important information. For example, if the data in one cell is the total quantity of one column, the data may be set as red or other bright colors. Although it is easy to set font style, according to users’ requirements, we may come across some difficult questions.
Recently, I found a problem about that how to control one row font or color when exporting Excel by using C#. After trying several methods, the following code may be helpful to solve this problem.
Firstly, we need to set the column format to complete to set Excel Font by using C#.
Excel.Range my range = mysheet.get_Range(mysheet.Cells[1,1], mysheet.Cells[5,1]);
Myrange.NumberFormatLocal = “@”;
The last line code means that the cell format type is text formatting.
Then, set the color of the tenth row as red.
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Select();
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Interior.ColorIndex=3;
mysheet.get_Range((Excel.Range)mysheet.Cells[10,1],(Excel.Range)mysheet.Cells[10,200]).Font.ColorIndex=3;
After running the code, we can find that all the characters in cells of the tenth row become red.
