/// <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();
}
/// <summary>
/// GirdView转换成DataTable
/// 20150813
/// 涂聚文
/// </summary>
/// <param name="dgv"></param>
/// <returns></returns>
public static DataTable GetGirdViewToTableHeaderText(DataGridView dgv)
{
DataTable dt = new DataTable();
try
{
for (int count = 0; count < dgv.Columns.Count; count++)
{
if (dgv.Columns[count].Visible == true)
{
DataColumn dc = new DataColumn(dgv.Columns[count].HeaderText);
dt.Columns.Add(dc);
}
}
for (int count = 0; count < dgv.Rows.Count; count++)
{
DataRow dr = dt.NewRow();
for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
{
if (dgv.Columns[count].Visible == true)
{
//if (dgv[countsub, count].ValueType == typeof(string))
//{
// dr[countsub] = "'" + dgv.Rows[count].Cells[countsub].Value;
//}
//else
//{
dr[countsub] = dgv.Rows[count].Cells[countsub].Value;
//}
}
}
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
ex.Message.ToString();
}
return dt;
}
/// <summary>
/// List 转DataTable
/// 涂聚文
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable ConvertListToDataTable(List<string[]> list)
{
// New table.
DataTable table = new DataTable();
// Get max columns.
int columns = 0;
foreach (var array in list)
{
if (array.Length > columns)
{
columns = array.Length;
}
}
// Add columns.
for (int i = 0; i < columns; i++)
{
table.Columns.Add();
}
// Add rows.
foreach (var array in list)
{
table.Rows.Add(array);
}
return table;
}
/// <summary>
/// 涂聚文
/// 2015.08.18
/// </summary>
/// <param name="dataGridView"></param>
/// <returns></returns>
public static DataTable DataGridViewToDataTable(DataGridView dataGridView)
{
DataTable dt = new DataTable();
try
{
foreach (DataGridViewColumn col in dataGridView.Columns)
{
if (col.Visible == true)
{
dt.Columns.Add(col.HeaderText, col.ValueType);
}
}
foreach (DataGridViewRow gridRow in dataGridView.Rows)
{
if (gridRow.IsNewRow)
continue;
int irow = 0;
DataRow dtRow = dt.NewRow();
for (int i1 = 0; i1 < dataGridView.Columns.Count; i1++)
{
if (dataGridView.Columns[i1].Visible == true)
{
dtRow[irow] = (gridRow.Cells[i1].Value == null ? DBNull.Value : gridRow.Cells[i1].Value);
irow++;
}
}
dt.Rows.Add(dtRow);
}
//ds.Tables.Add(dt);
//System.Diagnostics.Debugger.Break();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
return dt;
}
/// <summary>
/// 涂聚文
/// </summary>
/// <param name="dgv"></param>
/// <returns></returns>
public static DataTable GetGirdViewToTableHeaderText(DataGridView dgv)
{
//DataGridViewColumnCollection
DataTable dt = new DataTable();
try
{
//标题
for (int count = 0; count < dgv.Columns.Count; count++)
{
if (dgv.Columns[count].Visible == true)
{
DataColumn dc = new DataColumn(dgv.Columns[count].HeaderText, dgv.Columns[count].ValueType);
dt.Columns.Add(dc);
}
}
for (int count = 0; count < dgv.Rows.Count; count++)
{
int irow = 0;
DataRow dr = dt.NewRow();
//DataRow dr = dt.Rows.Add();
for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
{
if (dgv.Columns[countsub].Visible == true)
{
//if (dgv[countsub, count].ValueType == typeof(string))
//{
// dr[countsub] = "'" + dgv.Rows[count].Cells[countsub].Value;
//}
//elsedt.Rows[countsub][count]
//{
//dr[countsub] = dgv[countsub, count].Value;// dgv.Rows[count].Cells[countsub].Value;
dr[irow] = dgv.Rows[count].Cells[countsub].Value; // dgv[countsub, count].Value;
//}
irow++;
}
}
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
return dt;
}