csharp: Export DataSet into Excel and import all the Excel sheets to DataSet，csharpdataset
/// <summary>
/// Export DataSet into Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form3_Load(object sender, EventArgs e)
{
//Create an Emplyee DataTable
DataTable employeeTable = new DataTable("Employee");
employeeTable.Columns.Add("Employee ID");
employeeTable.Columns.Add("Employee Name");
employeeTable.Rows.Add("1", "塗聚文");
employeeTable.Rows.Add("2", "geovindu");
employeeTable.Rows.Add("3", "李蘢怡");
employeeTable.Rows.Add("4", "ноппчц");
employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц");
//Create a Department Table
DataTable departmentTable = new DataTable("Department");
departmentTable.Columns.Add("Department ID");
departmentTable.Columns.Add("Department Name");
departmentTable.Rows.Add("1", "IT");
departmentTable.Rows.Add("2", "HR");
departmentTable.Rows.Add("3", "Finance");
//Create a DataSet with the existing DataTables
DataSet ds = new DataSet("Organization");
ds.Tables.Add(employeeTable);
ds.Tables.Add(departmentTable);
ExportDataSetToExcel(ds);
}
/// <summary>
/// This method takes DataSet as input paramenter and it exports the same to excel
/// </summary>
/// <param name="ds"></param>
private void ExportDataSetToExcel(DataSet ds)
{
//Creae an Excel application instance
//EXCEL組件接口
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.Application excelApp = new Excel.Application();
excelApp.Application.Workbooks.Add(true);
string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");
//Create an Excel workbook instance and open it from the predefined location
//Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(FilePath);
Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks;
Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss);
foreach (DataTable table in ds.Tables)
{
//Add a new worksheet to workbook with the Datatable name
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
excelWorkBook.Close(false, miss, miss);
//excelWorkBook.Save();
books.Close();
excelApp.Quit();
}
/// <summary>
/// EXCEL表的所有工作表導入到DataSet
/// 塗聚文 Microsoft.ACE.OLEDB.12.0
/// Geovin Du
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
static DataSet ImportExcelParse(string fileName)
{
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
DataSet data = new DataSet();
foreach (var sheetName in GetExcelSheetNames(connectionString))
{
using (OleDbConnection con = new OleDbConnection(connectionString))
{
var dataTable = new DataTable();
string query = string.Format("SELECT * FROM [{0}]", sheetName);
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(dataTable);
data.Tables.Add(dataTable);
}
}
return data;
}
/// <summary>
/// 讀取所有工作表名
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
static string[] GetExcelSheetNames(string connectionString)
{
OleDbConnection con = null;
DataTable dt = null;
con = new OleDbConnection(connectionString);
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheetNames = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheetNames[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheetNames;
}
/// <summary>
/// 添加圖片
/// 塗聚文
/// </summary>
/// <param name="dt"></param>
protected void ExportExcelImg(System.Data.DataTable dt)
{
if (dt == null || dt.Rows.Count == 0) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
xlApp.Application.Workbooks.Add(true);
string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
try
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
catch
{
worksheet.Cells[r + 2, i + 1] =
dt.Rows[r][i].ToString().Replace("=", "");
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
string strimg =Application.StartupPath+@"/IMG_6851.JPG";
worksheet.Shapes.AddPicture(strimg, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);
//在添加的圖片上加文字
worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "塗聚文寫上", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);
xlApp.Visible = true;
workbook.SaveAs(FilePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
workbook.Close(false, miss, miss);
//excelWorkBook.Save();
workbooks.Close();
xlApp.Quit();
}
