private void button3_Click(object sender, EventArgs e)
{
if (!Directory.Exists("Excel_Kayitlari"))
{
Directory.CreateDirectory("Excel_Kayitlari");
}
saveFileDialog.OverwritePrompt = false;
saveFileDialog.Title = "Excel Dosyaları";
saveFileDialog.DefaultExt = "xlsx";
saveFileDialog.Filter = "xlsx Dosyaları (*.xlsx)|*.xlsx|Tüm Dosyalar(*.*)|*.*";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
app.Visible = true;
worksheet = workbook.Sheets["Sayfa1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Excel Dışa Aktarım";
for (int i = 1; i < selectedIndexses.Count + 1; i++)
{
worksheet.Cells[1, i] = listedItems[i - 1];
}
for (int i = 0; i < dgw.Rows.Count; i++)
{
for (int j = 0; j < listedItems.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgw.Rows[i].Cells[listedItems[j]].Value.ToString();
}
}
workbook.SaveAs(saveFileDialog.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);
app.Quit();
}
}
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 LeerLinea()
{
bool nullrow = true;
row++;
FilaXLS obj = new FilaXLS();
//creo el comando para subir el campo a la base de datos
string s = "";
string s1 = "insert into " + TTablaDestino.Text + "(";
int i, n;
n = ListaCamposDestino2.Items.Count;
bool primero = true;
s = s + ") values(";
primero = true;
bool hayDatos = false;
//recorre todos los campos
for (int col = 0; col < ListaCamposOrigen2.Items.Count; col++)
{
Objetos.CParametro obj2 = (Objetos.CParametro)ListaCamposDestino2.Items[col];
if (primero == true)
{
primero = false;
}
else
{
s1 = s1 + ",";
s = s + ",";
}
//Objetos.CParametro obj2 = (Objetos.CParametro)ListaCamposDestino2.Items[col];
s1 = s1 + obj2.nombre;
//lectura del registro de excel
CColumna cc = (CColumna)ListaCamposOrigen2.Items[col];
string cell;
char cy = ' ';
char cx = ((char)(65 + col)).ToString()[0];
if (cx > 'Z')
{
cy = (char)(64 + (int)col / (int)26);
cx = (char)((int)cx - (int)'Z' + 64);
}
cell = cy.ToString().Trim() + cx.ToString();
cell += row;
try
{
cc.Columna = cell;
if (Hoja.get_Range(cell, cell).Text != null)
{
cell = (string)Hoja.get_Range(cell, cell).Text;
}
else
{
cell = "";
}
}
catch (System.Exception)
{
break;
}
if (cell != "")
{
try
{
cc.Nombre = cell;
obj.v[col] = cell;
nullrow = false;
}
catch (Exception)
{
obj.v[col - 1] = "";
}
}
//termino de agregar los datos del campo de la consulta
if (obj.v[col] == null)
{
obj.v[col] = "";
}
if (obj.v[col].Length > obj2.Logitud && obj2.Logitud >= 0)
{
s = s + "\'" + obj.v[col].Substring(0, obj2.Logitud).Replace('\'', ' ') + "\'";
}
else
{
s = s + "\'" + obj.v[col].Replace('\'', ' ') + "\'";
}
if (obj.v[col] != "")
{
hayDatos = true;
}
}
if (!nullrow)
{
//aqui tengo el registro de exel
s = s1 + s + ")";
if (hayDatos == false)
{
//timer1.Enabled = false;
status = 2;
row = Comandos.Count;
BProgreso.Progreso = 0;
BProgreso.ValorMaximo = row;
xlLibro.Close(false, Missing.Value, Missing.Value);
xlApp.Quit();
return;
}
else
{
try
{
BProgreso.Texto = Comandos.Count + " Registros leídos";//row.ToString() + " Registros leídos";
Comandos.Add(s);
}
catch (System.Exception ex)
{
Errores = Errores + "\n" + ex;;
}
}
}
else
{
status = 2;
row = Comandos.Count;
BProgreso.Progreso = 0;
if (row > 0)
{
BProgreso.ValorMaximo = row;
}
xlLibro.Close(false, Missing.Value, Missing.Value);
xlApp.Quit();
}
nullrow = false;
}
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();
}
}
}