public Form1()
{
InitializeComponent();
listedItems = new List <string>();
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Add(Type.Missing);
worksheet = null;
saveFileDialog = new SaveFileDialog();
db = new DataContext(getConnection());
}
public bool export(string excelFile, Int16[] sizes)
{
if (columnSizes != null)
{
this.columnSizes = sizes;
}
else
{
for (int i = 0; i < 20; i++)
{
this.columnSizes[i] = 30;
}
}
Microsoft.Office.Interop.Excel._Application objExcelApp = null;
try
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelApp.Visible = false;
Microsoft.Office.Interop.Excel._Workbook workbook = objExcelApp.Workbooks.Add(1);
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
if (worksheet == null)
{
textBox1.AppendText("Worksheet could not be created. Check that your office installation and project references are correct.");
objExcelApp.Quit();
return(false);
}
worksheet.Range["A1"].Activate();
// resize the column
for (int i = 0; i < this.columnList.Count; i++)
{
worksheet.Range[this.columnList[i] + "1"].ColumnWidth = this.columnSizes[i];
}
worksheet.Paste();
// Save the excel file
workbook.SaveAs(excelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault);
objExcelApp.Workbooks.Close();
objExcelApp.Quit();
}
catch (Exception ex)
{
textBox1.AppendText(ex.Message);
if (objExcelApp != null)
{
objExcelApp.Quit();
}
return(false);
}
return(true);
}
public async void Export(DataGridView dataGridView)
{
try
{
_worksheet = _workbook.ActiveSheet;
_worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
await CreateIndecies(dataGridView, ref cellRowIndex, ref cellColumnIndex);
//Getting the location and file name of the excel to save from user.
var saveDialog =
new SaveFileDialog
{
Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*",
FilterIndex = 2
};
if (saveDialog.ShowDialog() == DialogResult.OK)
{
_workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
_excel.Quit();
_workbook = null;
_excel = null;
}
}
private void BBuacar_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.Cancel)
{
return;
}
TArchivo.Text = openFileDialog1.FileName;
BProgreso.Texto = "Leyendo información del archivo";
//extraigo las hojas que contiene el archivo
string fileName = TArchivo.Text;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlLibro = xlApp.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlHojas = xlLibro.Sheets;
Hojas = new List <Microsoft.Office.Interop.Excel._Worksheet>();
foreach (Microsoft.Office.Interop.Excel._Worksheet h in xlHojas)
{
hojas.Items.Add(h.Name);
Hojas.Add(h);
}
//cierro el archivo de exel
}
/// <summary>
/// 导出不一致数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsbtnDiffExport_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = null;
Microsoft.Office.Interop.Excel._Workbook workbook = null;
try
{
// creating Excel Application
app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "豆瓣图书数据";
// storing header part in Excel
for (int i = 1; i < dgvDiff.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgvDiff.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgvDiff.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvDiff.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgvDiff.Rows[i].Cells[j].Value.ToString();
}
}
string fileName = string.Empty;
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel File |*.xlsx";
sfd.InitialDirectory = Environment.CurrentDirectory;
sfd.RestoreDirectory = true;
if (DialogResult.OK == sfd.ShowDialog())
{
fileName = sfd.FileName;
}
}
if (fileName != string.Empty)
{
// save the application
workbook.SaveAs(fileName
, Type.Missing
, Type.Missing
, Type.Missing
, Type.Missing
, Type.Missing
, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive
, Type.Missing
, Type.Missing
, Type.Missing
, Type.Missing);
workbook.Close(true, fileName, false);
}
// Exit from the application
}
catch (Exception exp)
{
}
finally
{
if (app != null)
{
try
{
workbook.Close(false, Environment.CurrentDirectory + "\\" + "123.xlsx", false);
app.Quit();
workbook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);
app = null;
}
catch
{
}
}
}
}
//datatable导出到xls
public static void Export2Xls(DataTable data, string filename, bool exportHeader = true)
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
Microsoft.Office.Interop.Excel._Application xlsApp = null;
Microsoft.Office.Interop.Excel._Workbook xlsBook = null;
Microsoft.Office.Interop.Excel._Worksheet xstSheet = null;
try
{
xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlsBook = xlsApp.Workbooks.Add();
xstSheet = (Microsoft.Office.Interop.Excel._Worksheet)xlsBook.Worksheets[1];
var buffer = new StringBuilder();
if (exportHeader)
{
//Excel中列与列之间按照Tab隔开
foreach (DataColumn col in data.Columns)
{
buffer.Append(col.ColumnName + "\t");
}
buffer.AppendLine();
}
foreach (DataRow row in data.Rows)
{
foreach (DataColumn col in data.Columns)
{
buffer.Append(row[col].ToString() + "\t");
}
buffer.AppendLine();
}
System.Windows.Forms.Clipboard.SetDataObject("");
// 放入剪切板
System.Windows.Forms.Clipboard.SetDataObject(buffer.ToString());
var range = (Microsoft.Office.Interop.Excel.Range)xstSheet.Cells[1, 1];
range.Select();
xstSheet.Paste();
//清空剪切板
System.Windows.Forms.Clipboard.SetDataObject("");
xlsBook.SaveAs(filename);
}
finally
{
if (xlsBook != null)
{
xlsBook.Close();
}
if (xlsApp != null)
{
xlsApp.Quit();
}
// finally里清空Com对象
Marshal.ReleaseComObject(xlsApp);
Marshal.ReleaseComObject(xlsBook);
Marshal.ReleaseComObject(xstSheet);
xstSheet = null;
xlsBook = null;
xlsApp = null;
}
}
private void ExportToExcel(DataTable dt)
{
if (m_app != null)
{
m_app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_app);
}
// Книга Excel.
m_workBook = null;
// Страница Excel.
Microsoft.Office.Interop.Excel.Worksheet m_workSheet = null;
m_app = null;
string name = "";
if (dt.TableName == "statistic")
{
name = "Статистика на ";
}
else if (dt.TableName == "child")
{
name = "Список детей на ";
}
saveFileDialog1.FileName = name + DateTime.Now.Day + "." +
DateTime.Now.Month + "." +
DateTime.Now.Year + ".xlsx";// по умолчанию сохраняет в корень диска С:
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
// Создание приложения Excel.
m_app = new Microsoft.Office.Interop.Excel.Application();
// Приложение "невидимо".
m_app.Visible = false;
// Приложение управляется пользователем.
m_app.UserControl = true;
// Добавление книги Excel.
m_workBook = m_app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Связь со страницей Excel.
m_workSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_app.ActiveSheet;
// Заполняем шапку
int k = 1;
for (int j = 0; j < dt.Columns.Count; j++)
{
m_workSheet.Cells[1, j + k] = dt.Columns[j].ColumnName;
((Microsoft.Office.Interop.Excel.Range)m_workSheet.Cells[1, j + k]).Font.Bold = true;
((Microsoft.Office.Interop.Excel.Range)m_workSheet.Cells[1, j + k]).Borders.Color = Color.Black;
}
k = 1;
// Пишем строку
for (int l = 0; l < dt.Rows.Count; l++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
DataRow dr = dt.Rows[l];
((Microsoft.Office.Interop.Excel.Range)m_workSheet.Cells[l + 2, j + k]).Borders.Color = Color.Black;
m_workSheet.Cells[l + 2, j + k] = dr[j];
}
}
// Сохранение файла Excel.
m_workSheet.Columns.AutoFit();
m_workBook.SaveAs(saveFileDialog1.FileName);
}
finally
{
m_app.Visible = true;
m_app.Interactive = true;
m_app.ScreenUpdating = true;
m_app.UserControl = true;
GC.Collect();
dt.Clear();
}
}
}
