private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Sheet 1 content";
xlWorkBook.SaveAs("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls");
}
static void Main(string[] args)
{
System.IO.StreamReader txtfile = new System.IO.StreamReader("D:\\Learning\\TestDLL\\Dllpath.txt");
int counter = txtfile.ReadLine().Count();
Console.WriteLine(counter);
Console.ReadLine();
Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
if (xlapp== null)
{
Console.WriteLine("Excel is not installed");
return;
}
Excel.Workbook xlworkbook;
Excel.Worksheet xlworksheet;
object misvalue = System.Reflection.Missing.Value;
xlworkbook = xlapp.Workbooks.Add(misvalue);
String filepath = @"D:\Learning\TestDLL\pdfshell.dll";
FileVersionInfo myFileVersionInfo = FileVersionInfo.GetVersionInfo(filepath);
xlworksheet=xlworkbook.Worksheets.get_Item(1);
xlworksheet.Name = "DLLProperties";
xlworksheet.Cells[1, 1] ="NameofFile" ;
xlworksheet.Cells[1, 2] = "ProductVersion";
xlworksheet.Cells.Font.Size = 25;
xlworkbook.SaveAs("D:\\Learning\\demoexcel3.xls", Excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue);
xlworkbook.Close(true,misvalue,misvalue);
xlapp.Quit();
}
public void SpremanjeFaktura()
{
string OtvorenaFaktura = Application.StartupPath + "\\CMR\\CMR.xlsx";
//unos podataka
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
sheets = theWorkbook.Worksheets;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
excelApp.Workbooks.Open(OtvorenaFaktura);
var BrojOtpremnice = (worksheet.Cells[1, 10] as Microsoft.Office.Interop.Excel.Range).Value;
int NoviBrojOtpremnice = Convert.ToInt16(BrojOtpremnice) + 1;
excelApp.Cells[1, 10] = NoviBrojOtpremnice;
excelApp.ActiveWorkbook.Save();
theWorkbook.Close(0);
excelApp.Quit();
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
Process.Start(OtvorenaFaktura);
}
static void DrawCombinedLineChart(IEnumerable<Worksheet> source, Worksheet dest, string SeriesName, double maximum, double minimum)
{
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dest.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 800, 400);
Excel.Chart chartPage = myChart.Chart;
myChart.Select();
chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.MaximumScale = maximum;
//xAxis.LogBase = 10;
Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yAxis.MaximumScale = 1;
foreach (var sheet in source)
{
Excel.Series series1 = seriesCollection.NewSeries();
series1.Name = sheet.Name;
series1.XValues = sheet.UsedRange.get_Range("A:A");
series1.Values = sheet.UsedRange.get_Range("B:B");
series1.Smooth = true;
series1.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
}
}
private void drawGraph()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook
while (thread1.IsAlive)
{
//son satır bulunuyor excel dosyasındaki
Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlWorkSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz
// alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
string[] HeatingData = ReceiveData.Split(':');
string[] HeatingData2 = HeatingData[1].Split('D');
var result = HeatingData2[0];
double heating = Convert.ToDouble(result);
theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");
Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor!
this.Invoke((MethodInvoker)delegate
{
chart1.Series["Series1"].Points.AddY(result);
// excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
});
}
}
catch
{
// MessageBox.Show("Dosya bulunamadı");
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Zaman";
xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";
xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
}
}
/// <summary>
/// 表导出Excel
/// </summary>
/// <param name="modeFilePath">模板路径</param>
/// <param name="saveFilePath">保存文件路径</param>
/// <param name="tableName">导出数据名称</param>
public void ListToExcel(string modeFilePath, string saveFilePath, string tableName)
{
//需要添加 Microsoft.Office.Interop.Excel引用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
Console.Write("服务器上缺少Excel组件，需要安装Office软件。");
}
app.Visible = false;
app.UserControl = true;
Excel.Workbooks workbooks = app.Workbooks;
//加载模板
Excel._Workbook workbook = workbooks.Add(modeFilePath);
Excel.Sheets sheets = workbook.Sheets;
try
{
switch (tableName)
{
case "SingleInspection":
//SettlementObservationWriteData(workbook, sheets);
SingleInspectionWriteData(workbook, sheets,saveFilePath);
break;
case "LoftingScore":
LoftingScoreWriteData(workbook, sheets);
break;
//.........
}
//保存生成的Excel文件。
//Missing 在System.Reflection命名空间下。
workbook.SaveAs(saveFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//按顺序释放资源。
eb.NAR(sheets);
eb.NAR(workbook);
eb.NAR(workbooks);
app.Quit();
eb.NAR(app);
eb.KillProcess("EXCEL");//杀死excel进程
switch(tableName)
{
case "SingleInspection":
ef.Save_Excel(saveFilePath, "施工测量放样报验单");
break;
}
ef.Get_Excel(14, @"D:\123.xls");
}
catch (Exception ex)
{
MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
eb.KillProcess("EXCEL");//杀死excel进程
}
}
public void export(DataTable dt)
{
xlApp = new Excel.Application();
xlApp.Visible = true;
try
{
xlApp.Workbooks.Add(Type.Missing);
xlApp.Interactive = false;
xlApp.EnableEvents = false;
xlSheet = (Excel.Worksheet)xlApp.Sheets[1];
xlSheet.Name = "Итог";
int collInd = 0;
int rowInd = 0;
string data = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
data = dt.Columns[i].ColumnName.ToString();
xlSheet.Cells[1, i + 1] = data;
xlSheetRange = xlSheet.get_Range("A1:Z1", Type.Missing);
xlSheetRange.WrapText = true;
xlSheetRange.Font.Bold = true;
}
for (rowInd = 0; rowInd < dt.Rows.Count; rowInd++)
{
for (collInd = 0; collInd < dt.Columns.Count; collInd++)
{
data = dt.Rows[rowInd].ItemArray[collInd].ToString();
xlSheet.Cells[rowInd + 2, collInd + 1] = data;
}
}
xlSheetRange = xlSheet.UsedRange;
xlSheetRange.Columns.AutoFit();
xlSheetRange.Rows.AutoFit();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
xlApp.Visible = true;
xlApp.Interactive = true;
xlApp.ScreenUpdating = true;
xlApp.UserControl = true;
}
}
public void Read(string filename)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename);
// Get worksheet names
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Worksheets)
Debug.WriteLine(sh.Name);
// Get values from sheets SH1 and SH3 (in my file)
//object val1 = wb.Sheets["SH1"].Cells[1, "A"].Value2;
//object val3 = wb.Sheets["SH3"].Cells[1, "A"].Value2;
//Debug.WriteLine("{0} / {1}", val1, val3);
wb.Close();
excel.Quit();
}
public static void Excel_FromDataTable(DataTable dt)
{
// Create an Excel object and add workbook...
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???
// Add column headings...
int iCol = 0;
foreach (DataColumn c in dt.Columns)
{
iCol++;
excel.Cells[1, iCol] = c.ColumnName;
}
// for each row of data...
int iRow = 0;
foreach (DataRow r in dt.Rows)
{
iRow++;
// add each row's cell data...
iCol = 0;
foreach (DataColumn c in dt.Columns)
{
iCol++;
excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
}
}
// Global missing reference for objects we are not defining...
object missing = System.Reflection.Missing.Value;
// If wanting to Save the workbook...
workbook.SaveAs("G:/IssueLogger/IssueLog.xls",
Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);//Change_Me
// If wanting to make Excel visible and activate the worksheet...
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
((Excel._Worksheet)worksheet).Activate();
// If wanting excel to shutdown...
((Excel._Application)excel).Quit();
}
//Matrix R
private void button10_Click(object sender, EventArgs e)
{
string projectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
string path = projectPath + @"/HistEqDataSet/Data-Latih";
//string path = projectPath + @"/ImageDataSet/Data-Latih";
string path2 = projectPath + @"/pcaProcess/MatrixR.xlsx";
Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook wb = ExcelApp.Workbooks.Open(path2);
Excel.Worksheet sh = (Excel.Worksheet)wb.Sheets["Sheet1"];
DirectoryInfo di = new DirectoryInfo(path);
FileInfo[] Images = di.GetFiles("*.bmp");
FileInfo[] sortedImages = Images.OrderBy(f => f.CreationTime).ToArray();
int i = 1;
int img = 0;
for (int j = 1; j <=2178; j++)
{
Image image = Image.FromFile(path + "/" + sortedImages[img].Name);
Bitmap b = new Bitmap(image);
for (int y = 0; y < 48; y++)
{
for (int x = 0; x < 48; x++)
{
Color c = b.GetPixel(x, y);
int rgb = c.R;
sh.Cells[i, j].Value = rgb;
i++;
}
}
img = img + 1;
i = 1;
}
wb.Save();
wb.Close();
MessageBox.Show("Matrix R Selesai", "Done!", MessageBoxButtons.OK);
}
/// <summary>
/// Deze methode gaat de gegevens op een bepaalde plaats in een excel document ophalen
/// </summary>
/// <param name="fileName">De file waaruit de gegevens gehaald moeten worden</param>
private static void ReadExcelFileSAX(string fileNaam)
{
Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(fileNaam, 0, true);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
gewichtLinksVooraan = ((Excel.Range)xlws.Cells[12, 4]).Value2.ToString();
gewichtRechtsVooraan = ((Excel.Range)xlws.Cells[13, 4]).Value.ToString();
gewichtAchterLinks = ((Excel.Range)xlws.Cells[15, 4]).Value.ToString();
gewichtAchterRechts = ((Excel.Range)xlws.Cells[16, 4]).Value.ToString();
gewichtAchterMidden = ((Excel.Range)xlws.Cells[17, 4]).Value.ToString();
}
protected void ExportExcel(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;
}
System.Globalization.CultureInfo CurrentCI =
System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo("th-TH");
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;
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;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}
/// <summary>
/// "Export to Excel" button, uses textbox to left to determine fileName
/// </summary>
private void btnCreateExcelFile_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//Content Type
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 30;
Microsoft.Office.Interop.Excel.Workbook wb = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet wsContentType = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
// storing header part in Excel
for (int i = 1; i < grdReport.Columns.Count + 1; i++)
{
wsContentType.Cells[1, i] = grdReport.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < grdReport.Rows.Count - 1; i++)
{
for (int j = 0; j < grdReport.Columns.Count; j++)
{
wsContentType.Cells[i + 2, j + 1] = grdReport.Rows[i].Cells[j].Value.ToString();
}
}
if (saveContentType.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
ExcelApp.ActiveWorkbook.SaveCopyAs(saveContentType.FileName);
ExcelApp.ActiveWorkbook.Saved = true;
}
ExcelApp.Quit();
}
catch (Exception exp)
{
LogError(exp);
MessageBox.Show("Failed to create Excel file");
}
}
public static String openFile()
{
string filePath = string.Empty;
string fileExt = string.Empty;
OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
//cbbSheetName.Items.Clear();
//txtSheet.Clear();
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
//txtfilename.Text = file.FileName;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(file.FileName);
foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
{
//cbbSheetName.Items.Add(wSheet.Name);
}
}
return(file.FileName);
}
public ExcelWriter(string filename, ReportType type)
{
Report = type;
oApplication = new Microsoft.Office.Interop.Excel.Application();
oWorkbook = null;
oWorksheet = null;
string outputPath = Filename = System.Configuration.ConfigurationManager.AppSettings["OutputLocation"];
if (type == ReportType.MonthyTally)
{
Filename = Path.Combine(outputPath, "MonthlyTally");
}
if (!Directory.Exists(Filename))
Directory.CreateDirectory(Filename);
Filename = Path.Combine(Filename, filename);
if (File.Exists(Filename))
File.Delete(Filename);
logger = Logger.CreateLogger();
}
private void Button4_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
//Книга.
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelApp.Columns.ColumnWidth = 20;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
ExcelApp.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
//Вызываем нашу созданную эксельку.
ExcelApp.Visible = true;
ExcelApp.UserControl = true;
}
private void BtnConvertirScotiabank_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbook libros;
Microsoft.Office.Interop.Excel.Worksheet hoja1, hoja2;
libros = app.Workbooks.Add();
libros.Sheets.Add();
TablaScotiabank();
hoja1 = (Microsoft.Office.Interop.Excel.Worksheet)libros.Worksheets.get_Item(1);
hoja1.Name = "Exported from gridview";
for (int i = 0; i < GritConvert.Rows.Count - 1; i++)
{
for (int j = 0; j < GritConvert.Columns.Count; j++)
{
hoja1.Cells[i + 1, j + 1] = GritConvert.Rows[i].Cells[j].Value.ToString();
}
}
DatoSapiensScotiabank();
hoja2 = (Microsoft.Office.Interop.Excel.Worksheet)libros.Worksheets.get_Item(2);
hoja2.Name = "Exported from gridview2";
for (int i = 0; i < GritConvert.Rows.Count - 1; i++)
{
for (int j = 0; j < GritConvert.Columns.Count; j++)
{
hoja2.Cells[i + 1, j + 1] = GritConvert.Rows[i].Cells[j].Value.ToString();
}
}
libros.SaveAs(@"C:\Users\arni_\OneDrive\Documentos\trabajo para hacer star global\prueba\ComparaScotiabank.xlsx", 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 void ExportExcel(DevExpress.XtraGrid.Views.Grid.GridView gridView3)
{
string filename = "";
using (SaveFileDialog sfd = new SaveFileDialog()
{
Filter = "Excel Workbook|*.xlsx"
})
{
if (sfd.ShowDialog() == DialogResult.OK)
{
filename = sfd.FileName;
}
}
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < gridView3.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = gridView3.Columns[i].Caption;
}
for (int i = 0; i < gridView3.RowCount - 1; i++)
{
for (int j = 0; j < gridView3.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = gridView3.Columns[j].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(filename);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Ghi file thành công tại thư mục chạy ứng dụng");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void ExportToExcel(List <TableData> list)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int i = 0; i < list.Count; i++)
{
int row = int.Parse(list[i].Line);
List <string> values = list[i].Values;
int index = 0;
foreach (string x in values)
{
index++;
xlWorkSheet.Cells[row + 1, index] = values[index - 1];
}
}
//xlWorkSheet.Cells[1, 1] = list[0].Values[0];
//xlWorkSheet.Cells[1, 2] = list[0].Values[1];
//xlWorkSheet.Cells[1, 3] = list[0].Values[2];
xlWorkBook.SaveAs("c:\\temp\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
MessageBox.Show("Excel file created , you can find the file csharp-Excel.xls");
}
public void Inputexcel(Microsoft.Office.Interop.Excel.Application excelApp1)
{
try
{
string ZFCEPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "System\\报价单.xls");
//需要换 成日期的导出表
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(ZFCEPath, Type.Missing, true, Type.Missing,
"htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelApp.Visible = true;
excelApp.ScreenUpdating = true;
Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets[1];
Microsoft.Office.Interop.Excel.Range rng;
// rng = WS.get_Range(WS.Cells[2, 1], WS.Cells[WS.UsedRange.Rows.Count, 45]);
rng = WS.Range[WS.Cells[1, 1], WS.Cells[WS.UsedRange.Rows.Count, 16]];
int rowCount = WS.UsedRange.Rows.Count - 1;
object[,] o = new object[1, 1];
o = (object[, ])rng.Value2;
Microsoft.Office.Interop.Excel.AllowEditRanges ranges = WS.Protection.AllowEditRanges;
ranges.Add("Information", WS.Range["B2:E6"], Type.Missing);
WS.Protect("123", true);
clsCommHelp.CloseExcel(excelApp, analyWK);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
protected void ExportExcel(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;
}
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;
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;
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count > 0)
{
try
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
app.Columns.AutoFit();
app.Visible = true;
MessageBox.Show("Exported");
}
catch { }
}
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[1, 3] = "Измерения";
int i;
for (int j = 2; j < Names2.Count; j++)
{
ExcelWorkSheet.Cells[2, j] = Names2.ElementAt(j);
}
for (i = 3; i < AllIzmer2.Count + 3; i++)
{
for (int j = 2; j < Names2.Count; j++)
{
ExcelWorkSheet.Cells[i, j] = AllIzmer2.ElementAt(i - 3).Subordinate[j];
}
}
int k = i++;
for (int j = 2; j < Names1.Count; j++)
{
ExcelWorkSheet.Cells[k, j] = Names1.ElementAt(j);
}
k++;
for (i = k; i < AllIzmer1.Count + k; i++)
{
for (int j = 2; j < Names1.Count; j++)
{
ExcelWorkSheet.Cells[i, j] = AllIzmer1.ElementAt(i - k).Subordinate[j];
}
}
ExcelApp.Visible = true;
}
public System.Data.DataTable ConvertToDataTable(string path)
{
System.Data.DataTable dt = null;
try
{
object rowIndex = 14;
dt = new System.Data.DataTable();
DataRow row;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int temp = 1;
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, temp]).Value2 != null)
{
dt.Columns.Add(Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, temp]).Value2));
temp++;
}
rowIndex = Convert.ToInt32(rowIndex) + 1;
int columnCount = temp;
temp = 1;
while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, temp]).Value2 != null)
{
row = dt.NewRow();
for (int i = 1; i < columnCount; i++)
{
row[i - 1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, i]).Value2);
}
dt.Rows.Add(row);
rowIndex = Convert.ToInt32(rowIndex) + 1;
temp = 1;
}
app.Workbooks.Close();
}
catch (Exception ex)
{
//lblError.Text = ex.Message;
}
return(dt);
}
private void btExport_Click(object sender, EventArgs e)
{
if (gridwiew.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application xcelApp = new Microsoft.Office.Interop.Excel.Application();
xcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < gridwiew.Columns.Count + 1; i++)
{
xcelApp.Cells[1, i] = gridwiew.Columns[i - 1].HeaderText;
}
for (int i = 0; i < gridwiew.Rows.Count; i++)
{
for (int j = 0; j < gridwiew.Columns.Count; j++)
{
xcelApp.Cells[i + 2, j + 1] = gridwiew.Rows[i].Cells[j].Value.ToString();
}
}
xcelApp.Columns.AutoFit();
xcelApp.Visible = true;
}
}
/// <summary>
/// Создает Excel таблицу, указывает шапку и др настройки.
/// </summary>
private void CreateExcelTableSettings()
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "№";
xlWorkSheet.Cells[1, 2] = "НАИМЕНОВАНИЕ";
xlWorkSheet.Cells[1, 3] = "ДЕЙСТВИЯ";
xlWorkSheet.Cells[1, 4] = "РЕЗУЛЬТАТ";
xlWorkSheet.Cells[2, 1] = "1";
xlWorkSheet.Cells[2, 2] = "2";
xlWorkSheet.Cells[2, 3] = "3";
xlWorkSheet.Cells[2, 4] = "4";
xlWorkSheet.Cells[1, 1].Font.Bold = true;
xlWorkSheet.Cells[1, 2].Font.Bold = true;
xlWorkSheet.Cells[1, 3].Font.Bold = true;
xlWorkSheet.Cells[1, 4].Font.Bold = true;
xlWorkSheet.Cells[2, 1].Font.Bold = true;
xlWorkSheet.Cells[2, 2].Font.Bold = true;
xlWorkSheet.Cells[2, 3].Font.Bold = true;
xlWorkSheet.Cells[2, 4].Font.Bold = true;
xlWorkSheet.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[1, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[2, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[2, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[2, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells[2, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
(xlWorkSheet.Columns["A", Type.Missing]).ColumnWidth = 5;
(xlWorkSheet.Columns["B", Type.Missing]).ColumnWidth = 25;
(xlWorkSheet.Columns["C", Type.Missing]).ColumnWidth = 60;
(xlWorkSheet.Columns["D", Type.Missing]).ColumnWidth = 60;
row = 3;
}
private void button6_Click(object sender, EventArgs e)
{
Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
if (xlapp == null)
{
MessageBox.Show("Microsft Excel Not Found", "Error", MessageBoxButtons.OKCancel);
}
Excel.Workbook workbok;
Excel.Worksheet workshet;
object misValue = System.Reflection.Missing.Value;
workbok = xlapp.Workbooks.Add(misValue);
workshet = (Excel.Worksheet)workbok.Worksheets.get_Item(1);
// workshet = (Excel.Worksheets)workbok.Worksheets.get_Item(1);
workshet.Cells[1, 1] = "Sno";
workshet.Cells[1, 2] = "Faculty Code";
workshet.Cells[1, 3] = "Student ID";
workshet.Cells[1, 4] = "Internal";
workshet.Cells[1, 5] = "Report Writing";
workshet.Cells[1, 6] = "Technical Content";
workshet.Cells[1, 7] = "Presentation";
workshet.Cells[1, 8] = "Total";
// WebClient we = new WebClient();
Accf[] rst = JsonConvert.DeserializeObject <Accf[]>(res_strings_fact);
for (int i = 0; i < rst.Length; i++)
{
workshet.Cells[i + 2, 1] = rst[i].ID;
workshet.Cells[i + 2, 2] = rst[i].Faculty_Code;
workshet.Cells[i + 2, 3] = rst[i].Student_id;
workshet.Cells[i + 2, 4] = rst[i]._internal;
workshet.Cells[i + 2, 5] = rst[i].Report_Writting;
workshet.Cells[i + 2, 6] = rst[i].Technical_Content;
workshet.Cells[i + 2, 7] = rst[i].Presentaion;
workshet.Cells[i + 2, 8] = rst[i].Total;
}
workbok.SaveAs("d:\\According Faculty.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workbok.Close(true, misValue, misValue);
xlapp.Quit();
}
private void btnExport_Click(object sender, EventArgs e) //자료 내보내기
{
//저장할 디렉토리를 물어보고, 거기에 저장
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Excel Files(*.xls)|*.xls";
dlg.Title = "엑셀 파일로 내보내기";
if (dlg.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlworkbook = xlApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlworkbook.Worksheets.get_Item(1);
//타이틀
for (int k = 0; k < dgvMember.Columns.Count; k++)
{
xlWorkSheet.Cells[1, k + 1] = dgvMember.Columns[k].Name;
//((Excel.Range)xlSheet.Cells[2, i + 1]).Interior.Color = Excel.XlRgbColor.rgbGhostWhite;
}
for (int i = 0; i < dgvMember.Rows.Count; i++)
{
for (int k = 0; k < dgvMember.Columns.Count; k++)
{
xlWorkSheet.Cells[i + 2, k + 1] = dgvMember[k, i].Value.ToString();
}
}
xlworkbook.SaveAs(dlg.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
xlworkbook.Close(true);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlworkbook);
releaseObject(xlApp);
MessageBox.Show("Excel Export가 완료되었습니다.");
}
}
public void cargaFormatoTableta(string Path)
{
System.IO.FileInfo fi = new System.IO.FileInfo(Path);
if (fi.Exists)
{
fi.Delete();
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "CCT";
xlWorkSheet.Cells[1, 2] = "N° de serie";
xlWorkBook.SaveAs(Path);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
//string filePath;
public string gettext(string filePath)
{
// Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str = "";
int rCnt = 0;
int cCnt = 0;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
int totalSheets = (int)xlWorkBook.Sheets.Count;
for (int scnt = 1; scnt <= totalSheets; scnt++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(scnt);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str += (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2 + " ";
}
}
releaseObject(xlWorkSheet);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseObject(xlWorkBook);
releaseObject(xlApp);
//MessageBox.Show(str);
return(str);
}
void createfile()
{
Excel.Application xxlApp = new Microsoft.Office.Interop.Excel.Application();
if (xxlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xxlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
position = 0;
WriteArray(users + 1, 8, xlWorkSheet);
xxlApp.Visible = true;
xlWorkSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
}
public void Convert(String metricsFolder)
{
metrics_files = Directory.GetFiles(metricsFolder);
foreach (String file in metrics_files)
{
string[] nameArray = file.Split('\\'); //get an array of all elements split at \\
name = nameArray[nameArray.Length - 1]; //return the last element of the array which shoudl always be the file name
string path = metricsFolder + "\\backups\\";
//check to make sure backup directory exists, otherwise create it
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
var app = new Microsoft.Office.Interop.Excel.Application();
var wb = app.Workbooks.Open(file);
wb.SaveAs(Filename: file + "x", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close();
app.Quit();
path += name;//add the file name to the backup path so the move can be completed
File.Move(file, path);
}
}
private void button1_Click(object sender, EventArgs e)
{
//dataGridView2.Visible = false;
dataGridView2.DataSource = null;
dataGridView2.ColumnCount = 9;
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(@"d:\csharp.net-informations2.xls");
Excel.Worksheet worksheet = workbook.ActiveSheet;
int rcount = worksheet.UsedRange.Rows.Count;
int i = 0;
for (; i + 1 < rcount; i++)
{
dataGridView2.Columns[0].HeaderText = worksheet.Cells[1, 1].Value;
dataGridView2.Columns[1].HeaderText = worksheet.Cells[1, 2].Value;
dataGridView2.Columns[2].HeaderText = worksheet.Cells[1, 3].Value;
dataGridView2.Columns[3].HeaderText = worksheet.Cells[1, 4].Value;
dataGridView2.Columns[4].HeaderText = worksheet.Cells[1, 5].Value;
dataGridView2.Columns[5].HeaderText = worksheet.Cells[1, 6].Value;
dataGridView2.Columns[6].HeaderText = worksheet.Cells[1, 7].Value;
dataGridView2.Columns[7].HeaderText = worksheet.Cells[1, 8].Value;
dataGridView2.Columns[8].HeaderText = worksheet.Cells[1, 9].Value;
// dataGridView2.Rows[0].Cells["Column1"].Value = worksheet.Cells[1, 1].Value;
//ataGridView1.Rows[i].Cells["Column2"].Value = worksheet.Cells[i + 2, 2].Value;
dataGridView2.Rows.Add(worksheet.Cells[i + 2, 1].Value, worksheet.Cells[i + 2, 2].Value,
worksheet.Cells[i + 2, 3].Value, worksheet.Cells[i + 2, 4].Value, worksheet.Cells[i + 2, 5].Value,
worksheet.Cells[i + 2, 6].Value, worksheet.Cells[i + 2, 7].Value, worksheet.Cells[i + 2, 8].Value,
worksheet.Cells[i + 2, 9].Value);
}
workbook.Close();
app.Quit();
}
public TO_EXEL(string lo, DataGridView datalov, string spb, DataGridView dataspbv, string spblo
, DataGridView dataspblov, string svod, DataGridView svodv)
{
DataGridView datalo = datalov;
DataGridView dataspb = dataspbv;
DataGridView dataspblo = dataspblov;
DataGridView datasvod = svodv;
Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorklo;
Excel.Worksheet ExcelWorkspb;
Excel.Worksheet ExcelWorkspblo;
Excel.Worksheet ExcelWorksvod;
ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
ExcelWorkBook.Sheets.Add();
ExcelWorkBook.Sheets.Add();
ExcelWorklo = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
//ap = dataGridView1.DataSource
// ExcelWorklo = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorklo.Name = lo;
export(ExcelWorklo, datalo);
ExcelWorkspb = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(2);
ExcelWorkspb.Name = spb;
export(ExcelWorkspb, dataspb);
ExcelWorkspblo = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(3);
ExcelWorkspblo.Name = spblo;
export(ExcelWorkspblo, dataspblo);
// ExcelWorksvod = (Excel.Worksheet)ExcelWorkBook.Worksheets.
ExcelWorksvod = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(4);
ExcelWorksvod.Name = svod;
export(ExcelWorksvod, datasvod);
ExcelApp.Visible = true;
}
public static void ExportToExcel(string strFileName, string strSheetName)
{
// Run the garbage collector
GC.Collect();
// Delete the file if it already exists
if (System.IO.File.Exists(strFileName))
{
System.IO.File.SetAttributes(strFileName, FileAttributes.Normal);
System.IO.File.Delete(strFileName);
}
// Open an instance of excel. Create a new workbook.
// A workbook by default has three sheets, so if you just want a single one, delete sheet 2 and 3
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook xlWB = (Excel._Workbook)xlApp.Workbooks.Add(Missing.Value);
Excel._Worksheet xlSheet = (Excel._Worksheet)xlWB.Sheets[1];
((Excel._Worksheet)xlWB.Sheets[2]).Delete();
((Excel._Worksheet)xlWB.Sheets[2]).Delete();
xlSheet.Name = strSheetName;
// Write a value into A1
xlSheet.Cells[1, 1] = "Some value";
// Tell Excel to save your spreadsheet
xlWB.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlApp.Quit();
// Release the COM object, set the Excel variables to Null, and tell the Garbage Collector to do its thing
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlSheet = null;
xlWB = null;
xlApp = null;
GC.Collect();
}
public void cargaInstitucionMasivo(string Path)
{
System.IO.FileInfo fi = new System.IO.FileInfo(Path);
if (fi.Exists)
{
fi.Delete();
}
else
{
fi.Create();
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 2;
xlWorkSheet.Cells[1, 1] = "CCT";
xlWorkSheet.Cells[1, 2] = "Nombre";
xlWorkSheet.Cells[1, 3] = "Grado [S para Superior, M para Medio Superior]";
xlWorkSheet.Cells[1, 4] = "Estado";
xlWorkSheet.Cells[1, 5] = "Municipio";
xlWorkSheet.Cells[1, 6] = "Colonia";
xlWorkSheet.Cells[1, 7] = "Calle";
xlWorkSheet.Cells[1, 8] = "Numero Exterior";
xlWorkSheet.Cells[1, 9] = "C.P.";
xlWorkSheet.Cells[1, 10] = "Telefono";
xlWorkSheet.Cells[1, 11] = "Correo";
xlWorkSheet.Cells[1, 12] = "Oficinas (Si o No)";
xlWorkSheet.Cells[1, 13] = "Dependencia";
xlWorkBook.SaveAs(Path);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
public void cargaUsuariosMasivo(string Path)
{
System.IO.FileInfo fi = new System.IO.FileInfo(Path);
if (fi.Exists)
{
fi.Delete();
}
else
{
fi.Create();
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 2;
xlWorkSheet.Cells[1, 1] = "CURP";
xlWorkSheet.Cells[1, 2] = "RFC ó CCT donde pertenece";
xlWorkSheet.Cells[1, 3] = "C.P.";
xlWorkSheet.Cells[1, 4] = "Estado Recidencia";
xlWorkSheet.Cells[1, 5] = "Municipio";
xlWorkSheet.Cells[1, 6] = "Colonia";
xlWorkSheet.Cells[1, 7] = "Calle";
xlWorkSheet.Cells[1, 8] = "Numero Interior";
xlWorkSheet.Cells[1, 9] = "Numero Exterior";
xlWorkSheet.Cells[1, 10] = "Telefono";
xlWorkSheet.Cells[1, 11] = "Telefono Celular";
xlWorkSheet.Cells[1, 12] = "Correo";
xlWorkBook.SaveAs(Path);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
private void gomb_excel_Click(object sender, RoutedEventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Az excel nincs rendesen telepítve!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Számítógép neve:";
xlWorkSheet.Cells[1, 2] = a1;
xlWorkSheet.Cells[2, 1] = "Felhasználó neve:";
xlWorkSheet.Cells[2, 2] = a2;
xlWorkSheet.Cells[3, 1] = "Verzió:";
xlWorkSheet.Cells[3, 2] = a3;
xlWorkSheet.Cells[4, 1] = "Operációs rendszer platformja:";
xlWorkSheet.Cells[4, 2] = a5;
xlWorkSheet.Cells[5, 1] = "Processzor szálak száma:";
xlWorkSheet.Cells[5, 2] = a6;
xlWorkSheet.Cells[6, 1] = "Program mappája:";
xlWorkSheet.Cells[6, 2] = a7;
xlWorkSheet.Cells[7, 1] = "Rendszer mappája:";
xlWorkSheet.Cells[7, 2] = a8;
xlWorkSheet.Cells[8, 1] = "Domain név:";
xlWorkSheet.Cells[8, 2] = a9;
xlWorkBook.SaveAs("D:\\Suli\\Komplex_programozas\\HardWare_SoftWare_Monitor\\HardWare_SoftWare_Monitor\\data.xlsx", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Az adatok sikeresen elmentve!");
}
private void Convert_Dataset_to_Excel()
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = true;
Workbook xlWorkbook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
DataTableCollection collection = ds.Tables;
for (int i = collection.Count; i > 0; i--)
{
Sheets xlSheets = null;
Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelApp.Worksheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table = collection[i - 1];
xlWorksheet.Name = table.TableName;
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
}
/// <summary>
/// Save the Current working workbook
/// CLose the Current working workbook
/// Quit the Excel Interop process
///
/// After calling this function, you should NOT use this Builder ANYMORE
/// </summary>
public void CloseWorkbook()
{
mExcelApp.ScreenUpdating = true;
mExcelApp.Visible = false;
mExcelApp.DisplayAlerts = false;
mBook.Save();
mBook.Close(Type.Missing, Type.Missing, Type.Missing);
mSheet = null;
mBook = null;
if (mExcelApp != null)
{
mExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcelApp);
mExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void SaveXLS_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
//Книга.
ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
//Таблица.
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
//Значения [y - строка,x - столбец]
for (int i = 0; i <= ArrData.rowNumber; i++)
{
ObjWorkSheet.Cells[i + 1, 1] = ArrData.arrNum[i];
ObjWorkSheet.Cells[i + 1, 2] = ArrData.arrValue[i];
}
string fileName = String.Empty;
SaveFileDialog saveFileXLS = new SaveFileDialog();
saveFileXLS.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveFileXLS.FilterIndex = 2;
saveFileXLS.RestoreDirectory = true;
if (saveFileXLS.ShowDialog() == DialogResult.OK)
{
fileName = saveFileXLS.FileName;
}
else
{
return;
}
ObjWorkBook.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);
ObjWorkBook.Close(false, Type.Missing, Type.Missing);
ObjExcel.Quit();
saveFileXLS.Dispose();
}
private void toolStripButton23_Click(object sender, EventArgs e)
{
//Экспорт в Excel
Microsoft.Office.Interop.Excel.Application exApp = new Microsoft.Office.Interop.Excel.Application();
exApp.Visible = true;
exApp.Workbooks.Add();
Worksheet workSheet = (Worksheet)exApp.ActiveSheet;
workSheet.Cells[1, 1] = "Ведомость о питании по классам";
workSheet.Cells[3, 1] = "Месяц";
workSheet.Cells[3, 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet.Cells[3, 2] = "Год";
workSheet.Cells[3, 2].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet.Cells[3, 3] = "Название класса";
workSheet.Cells[3, 3].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet.Cells[3, 4] = "Название типа питания";
workSheet.Cells[3, 4].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet.Cells[3, 5] = "Количество";
workSheet.Cells[3, 5].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
workSheet.Cells[3, 6] = "Цена питания";
workSheet.Cells[3, 6].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
int rowExcel = 4;
for (int i = 0; i < dataGridView17.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView17.Columns.Count; j++)
{
workSheet.Cells[i + rowExcel, j + 1] = dataGridView17.Rows[i].Cells[j].Value.ToString();
workSheet.Cells[i + rowExcel, j + 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
}
workSheet.SaveAs("Ведомость о питании по классам.xlsx");
MessageBox.Show("Экспорт данных завершен...", "Школьное питание", MessageBoxButtons.OK, MessageBoxIcon.Information);
exApp.Quit();
}
public void actualizaraexcel(DataGridView dataGridView1)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Open(txtArchivo.Text);
int IndiceColumna = 0;
foreach (DataGridViewColumn col in dataGridView1.Columns) //Columnas
{
IndiceColumna++;
excel.Cells[1, IndiceColumna] = col.Name;
}
int indiceFila = 0;
foreach (DataGridViewRow row in dataGridView1.Rows) //Filas
{
indiceFila++;
IndiceColumna = 0;
foreach (DataGridViewColumn col in dataGridView1.Columns)
{
IndiceColumna++;
excel.Cells[indiceFila + 1, IndiceColumna] = row.Cells[col.Name].Value;
}
}
//excel.GetSaveAsFilename(txtArchivo.Text);
//excel.Visible = true;
//excel.Workbooks.Add();
excel.Visible = true;
/*excel.Range["A1"].Value = "ID";
* excel.Range["B1"].Value = "USUARIO";
* excel.Range["C1"].Value = "DIRECCION";
* excel.Range["D1"].Value = "TELEFONO";
* excel.Range["E1"].Value = "EDAD";
* excel.Range["A2"].Select();*/
MessageBox.Show("Hoja de Datos Actualizada");
}
private void массив_excel(object[,] массив, int строк, int столбцов)
{
Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
string curDir = System.IO.Directory.GetCurrentDirectory();
object шаблон = curDir + @"\платежи_улица.xlt";
if (!System.IO.File.Exists(шаблон.ToString()))
{
MessageBox.Show("Нет файла " + шаблон.ToString());
return;
}
Microsoft.Office.Interop.Excel.Workbook o = oExcel.Workbooks.Add(Template: шаблон);
Excel.Worksheet eList = (Excel.Worksheet)o.Worksheets[1];
// Excel.ListObject tab1 = eList.ListObjects[1];
oExcel.Application.Visible = true;
eList.Cells[1, 2] = this.Text + " " + DateTime.Today.ToShortDateString();
eList.Cells[3, 1].Select();
eList.Cells[3, 1].Copy();
Excel.Range rg = eList.get_Range("A3", Type.Missing);
rg = rg.get_Resize(строк - 1, столбцов);
rg.PasteSpecial(Excel.XlPasteType.xlPasteFormats);
rg.NumberFormat = "@";
rg.HorizontalAlignment = Excel.Constants.xlRight;
rg.VerticalAlignment = Excel.Constants.xlTop;
// eList.Cells[3, 1].Paste();
// o.ActiveSheet.Paste();
rg.Value2 = массив;
eList.Cells[3, 1].Select();
oExcel.Application.Visible = true;
}
void saveData()
{
ChooseFolder();
string sifre = sifreTxtBox.Text;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
worksheet = workbook.Sheets["Sayfa1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Records";
for (int i = 0; i < sayac; i++)
{
worksheet.Cells[1, i + 1] = kolonlarDizi[i];
}
worksheet.Cells[1, sayac + 1] = "SicilNo (Ondalıksız)";
worksheet.Cells[1, sayac + 2] = "Tarih (Metin)";
//string encPass = encryptionName(sifreTxtBox.Text);
String dosyaAdı;
dosyaAdı = projeAdi.Text + "-" + projeSahibi.Text;
var saveFileDialoge = new SaveFileDialog();
saveFileDialoge.FileName = dosyaAdı;
saveFileDialoge.DefaultExt = ".xlsx";
string lastPath = path.Text;
workbook.SaveAs(lastPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
path.Text = path.Text + dosyaAdı;
app.Quit();
Application.Exit();
}
public static Application RegisterAndGetApp()
{
Application excel = null;
// get excel, throw exception if it is not
var officeType = Type.GetTypeFromProgID("Excel.Application");
if (officeType == null)
throw new Exception("Excel is not installed.");
try
{
excel = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException e)
{
// 0x800401E3 - the excel process simply was not running, we continue if we
// encounter this exception
if ( !e.ToString().Contains("0x800401E3") )
{
throw new Exception("Error setting up communication with Excel. Try closing any open Excel instances.");
}
}
if (excel == null) excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
throw new Exception("Excel could not be opened.");
}
dynSettings.Controller.DynamoModel.CleaningUp += DynamoModelOnCleaningUp;
excel.Visible = ShowOnStartup;
return excel;
}
private void ExportDataSetToExcel(DataTable dt,DataTable dt2)
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
Microsoft.Office.Interop.Excel.Worksheet workSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excel.Sheets.Add();
// I created Application and Worksheet objects before try/catch,
// so that i can close them in finnaly block.
// It's IMPORTANT to release these COM objects!!
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
workSheet.Cells[1, "A"]="origen";
workSheet.Cells[1, "B"]="column1";
workSheet.Cells[1, "C"]="referencia1";
workSheet.Cells[1, "D"]="referencia2";
workSheet.Cells[1, "E"]="referencia3";
workSheet.Cells[1, "F"]="presupuesto";
workSheet.Cells[1, "G"]="codcapi";
workSheet.Cells[1, "H"]="capitulo";
workSheet.Cells[1, "I"]="codunit";
workSheet.Cells[1, "J"]="unitario";
workSheet.Cells[1, "K"]="undunita";
workSheet.Cells[1, "L"]="cantxppto";
workSheet.Cells[1, "M"]="codinsu";
workSheet.Cells[1, "N"]="insutipo";
workSheet.Cells[1, "O"]="insumo";
workSheet.Cells[1, "P"]="unidinsu";
workSheet.Cells[1, "Q"]="ctrlinven";
workSheet.Cells[1, "R"]="validación";
workSheet.Cells[1, "S"]="precioppto";
workSheet.Cells[1, "T"]="consumounitario";
workSheet.Cells[1, "U"]="consumototal";
workSheet.Cells[1, "V"]="adic";
workSheet.Cells[1, "W"]="precioaut";
workSheet.Cells[1, "X"]="PrecioCompra";
workSheet.Cells[1, "Y"]="PrecioEntrado";
workSheet.Cells[1, "Z"]="Ped";
workSheet.Cells[1, "AA"]="aprob";
workSheet.Cells[1, "AB"]="comp";
workSheet.Cells[1, "AC"]="ent";
workSheet.Cells[1, "AD"]="sali";
workSheet.Cells[1, "AE"]="traslado";
workSheet.Cells[1, "AF"]="xcomp";
workSheet.Cells[1, "AG"]="xent";
workSheet.Cells[1, "AH"]="saldoxunit";
workSheet.Cells[1, "AI"]="saldoxppto";
workSheet.Cells[1, "AJ"]="vlrEnt";
workSheet.Cells[1, "AK"]="vlrEnttraslado";
workSheet.Cells[1, "AL"]="VlrCompradoxent";
workSheet.Cells[1, "AM"]="vlrxcomp";
workSheet.Cells[1, "AN"]="VlrTraslado";
workSheet.Cells[1, "AO"]="vlrproy";
workSheet.Cells[1, "AP"]="Vlrini";
workSheet.Cells[1, "AQ"]="vlrejec";
workSheet2.Cells[1, "A"] = "referencia1";
// ------------------------------------------------
// Populate sheet with some real data from "cars" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
workSheet.Cells[row, "A"] = dt.Rows[i].Field<string>("origen");
workSheet.Cells[row, "B"] = dt.Rows[i].Field<DateTime>("column1");
workSheet.Cells[row, "C"] = dt.Rows[i].Field<string>("referencia1");
workSheet.Cells[row, "D"] = dt.Rows[i].Field<string>("referencia2");
workSheet.Cells[row, "E"] = dt.Rows[i].Field<string>("referencia3");
/* workSheet.Cells[row, "F"] = dt.Rows[i].Field<string>("presupuesto");
workSheet.Cells[row, "G"] = dt.Rows[i].Field<string>("codcapi");
workSheet.Cells[row, "H"] = dt.Rows[i].Field<string>("capitulo");
workSheet.Cells[row, "I"] = dt.Rows[i].Field<string>("codunit");
workSheet.Cells[row, "J"] = dt.Rows[i].Field<string>("unitario");
workSheet.Cells[row, "K"] = dt.Rows[i].Field<string>("undunita");
workSheet.Cells[row, "L"] = dt.Rows[i].Field<decimal>("cantxppto");
workSheet.Cells[row, "M"] = dt.Rows[i].Field<string>("codinsu");
workSheet.Cells[row, "N"] = dt.Rows[i].Field<string>("insutipo");
// workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto");
workSheet.Cells[row, "P"] = dt.Rows[i].Field<string>("insumo");
workSheet.Cells[row, "Q"] = dt.Rows[i].Field<string>("unidinsu");
workSheet.Cells[row, "R"] = dt.Rows[i].Field<string>("ctrlinven");
workSheet.Cells[row, "S"] = dt.Rows[i].Field<string>("validación");
workSheet.Cells[row, "T"] = dt.Rows[i].Field<decimal>("precioppto");
workSheet.Cells[row, "U"] = dt.Rows[i].Field<decimal>("consumounitario");
workSheet.Cells[row, "V"] = dt.Rows[i].Field<decimal>("consumototal");
workSheet.Cells[row, "W"] = dt.Rows[i].Field<decimal>("precioaut");
workSheet.Cells[row, "X"] = dt.Rows[i].Field<decimal>("PrecioCompra");
workSheet.Cells[row, "Y"] = dt.Rows[i].Field<decimal>("PrecioEntrado");
workSheet.Cells[row, "Z"] = dt.Rows[i].Field<decimal>("Ped");
workSheet.Cells[row, "AA"] = dt.Rows[i].Field<decimal>("aprob");
workSheet.Cells[row, "AB"] = dt.Rows[i].Field<decimal>("comp");
workSheet.Cells[row, "AC"] = dt.Rows[i].Field<decimal>("traslado");
workSheet.Cells[row, "AD"] = dt.Rows[i].Field<decimal>("sali");
workSheet.Cells[row, "AE"] = dt.Rows[i].Field<decimal>("xcomp");
workSheet.Cells[row, "AF"] = dt.Rows[i].Field<decimal>("xent");
workSheet.Cells[row, "AG"] = dt.Rows[i].Field<decimal>("saldoxunit");
workSheet.Cells[row, "AH"] = dt.Rows[i].Field<decimal>("saldoxppto");
workSheet.Cells[row, "AI"] = dt.Rows[i].Field<decimal>("vlrEnt");
workSheet.Cells[row, "AJ"] = dt.Rows[i].Field<decimal>("vlrEnttraslado");
workSheet.Cells[row, "AK"] = dt.Rows[i].Field<decimal>("VlrCompradoxent");
workSheet.Cells[row, "AL"] = dt.Rows[i].Field<decimal>("vlrxcomp");
workSheet.Cells[row, "AM"] = dt.Rows[i].Field<decimal>("VlrTraslado");
workSheet.Cells[row, "AN"] = dt.Rows[i].Field<decimal>("vlrproy");
workSheet.Cells[row, "AO"] = dt.Rows[i].Field<decimal>("Vlrini");
//workSheet.Cells[row, "AP"] = dt.Rows[i].Field<decimal>("Fecha");
workSheet.Cells[row, "AQ"] = Convert.ToString(dt.Rows[i].Field<decimal>("vlrejec"));
workSheet.Cells[row].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);*/
//57
//124
row++;
}
int row2=2;
for (int i = 0; i <= dt2.Rows.Count - 1; i++)
{
workSheet2.Cells[row2, "A"] = dt2.Rows[i].Field<string>("referencia1");
workSheet2.Cells[row2, "D"] = dt2.Rows[i].Field<string>("referencia2");
workSheet2.Cells[row2, "E"] = dt2.Rows[i].Field<string>("referencia3");
workSheet2.Cells[row2, "F"] = dt2.Rows[i].Field<string>("presupuesto");
workSheet2.Cells[row2, "G"] = dt2.Rows[i].Field<string>("codcapi");
/* workSheet2.Cells[row2, "H"] = dt2.Rows[i].Field<string>("capitulo");
workSheet2.Cells[row2, "I"] = dt2.Rows[i].Field<string>("codunit");
workSheet2.Cells[row2, "K"] = dt2.Rows[i].Field<string>("undunita");
workSheet2.Cells[row2, "L"] = dt2.Rows[i].Field<decimal>("cantxppto");
workSheet2.Cells[row2, "M"] = dt2.Rows[i].Field<string>("codinsu");
workSheet2.Cells[row2, "N"] = dt2.Rows[i].Field<string>("insutipo");
// workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto");
workSheet2.Cells[row2, "P"] = dt2.Rows[i].Field<string>("insumo");
workSheet2.Cells[row2, "Q"] = dt2.Rows[i].Field<string>("unidinsu");
workSheet2.Cells[row2, "R"] = dt2.Rows[i].Field<string>("ctrlinven");
workSheet2.Cells[row2, "S"] = dt2.Rows[i].Field<string>("validación");
workSheet2.Cells[row2, "T"] = dt2.Rows[i].Field<decimal>("precioppto");
workSheet2.Cells[row2, "U"] = dt2.Rows[i].Field<decimal>("consumounitario");
workSheet2.Cells[row2, "V"] = dt2.Rows[i].Field<decimal>("consumototal");
workSheet2.Cells[row2, "W"] = dt2.Rows[i].Field<decimal>("precioaut");
workSheet2.Cells[row2, "X"] = dt2.Rows[i].Field<decimal>("PrecioCompra");
workSheet2.Cells[row2, "Y"] = dt2.Rows[i].Field<decimal>("PrecioEntrado");
workSheet2.Cells[row2, "Z"] = dt2.Rows[i].Field<decimal>("Ped");
workSheet2.Cells[row2, "AA"] = dt2.Rows[i].Field<decimal>("aprob");
workSheet2.Cells[row2, "AB"] = dt2.Rows[i].Field<decimal>("comp");
workSheet2.Cells[row2, "AC"] = dt2.Rows[i].Field<decimal>("traslado");
workSheet2.Cells[row2, "AD"] = dt2.Rows[i].Field<decimal>("sali");
workSheet2.Cells[row2, "AE"] = dt2.Rows[i].Field<decimal>("xcomp");
workSheet2.Cells[row2, "AF"] = dt2.Rows[i].Field<decimal>("xent");
workSheet2.Cells[row2, "AG"] = dt2.Rows[i].Field<decimal>("saldoxunit");
workSheet2.Cells[row2, "AH"] = dt2.Rows[i].Field<decimal>("saldoxppto");
workSheet2.Cells[row2, "AI"] = dt2.Rows[i].Field<decimal>("vlrEnt");
workSheet2.Cells[row2, "AJ"] = dt2.Rows[i].Field<decimal>("vlrEnttraslado");
workSheet2.Cells[row2, "AK"] = dt2.Rows[i].Field<decimal>("VlrCompradoxent");
workSheet2.Cells[row2, "AL"] = dt2.Rows[i].Field<decimal>("vlrxcomp");
workSheet2.Cells[row2, "AM"] = dt2.Rows[i].Field<decimal>("VlrTraslado");
workSheet2.Cells[row2, "AN"] = dt2.Rows[i].Field<decimal>("vlrproy");
workSheet2.Cells[row2, "AO"] = dt2.Rows[i].Field<decimal>("Vlrini");
workSheet2.Cells[row2, "AP"] = dt2.Rows[i].Field<int>("IdFecha");
workSheet2.Cells[row2, "AQ"] = Convert.ToString(dt2.Rows[i].Field<decimal>("vlrejec"));*/
//57
//124
row2++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet2.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xls", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
workSheet2.SaveAs(fileName);
// Display SUCCESS message
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Quit Excel application
excel.Quit();
// Release COM objects (very important!)
if (excel != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
if (workSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
private void btnToExcel_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (this.dgvShowData.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = true;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
//寫入欄位名稱
for (int i = 0; i < dgvShowData.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvShowData.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvShowData.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvShowData.Columns.Count; j++)
{
if (dgvShowData[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvShowData[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvShowData[j, i].Value.ToString();
}
}
}
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit(); //自動調整列高
excel.Cells.EntireColumn.AutoFit();
excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
excel.get_Range("A1").Font.Color = Color.White;
excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
excel.get_Range("B1").Font.Color = Color.White;
excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
excel.get_Range("C1").Font.Color = Color.White;
excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
excel.get_Range("D1").Font.Color = Color.White;
excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
excel.get_Range("E1").Font.Color = Color.White;
excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
excel.get_Range("F1").Font.Color = Color.White;
excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
excel.get_Range("G1").Font.Color = Color.White;
excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
excel.get_Range("H1").Font.Color = Color.White;
excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
excel.get_Range("I1").Font.Color = Color.White;
excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
excel.get_Range("J1").Font.Color = Color.White;
excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
excel.get_Range("K1").Font.Color = Color.White;
excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
excel.get_Range("L1").Font.Color = Color.White;
excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
excel.get_Range("M1").Font.Color = Color.White;
excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
excel.get_Range("N1").Font.Color = Color.White;
excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
excel.get_Range("O1").Font.Color = Color.White;
excel.get_Range("O1").Interior.Color = Color.DodgerBlue;
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
MessageBox.Show("已成功輸出至Excel！", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
}
public static void Save(DataGridView dgv, string Savepath)
{
Microsoft.Office.Interop.Excel.Application APP = new Microsoft.Office.Interop.Excel.Application();
APP.Application.Workbooks.Add(Type.Missing);
APP.Columns.ColumnWidth = 20;
for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
APP.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
APP.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value;
}
}
FileInfo file = new FileInfo(Savepath);
if (file.Exists)
{
file.Delete();
}
OpenV = true;
APP.ActiveWorkbook.SaveAs(Savepath.ToString());
APP.ActiveWorkbook.Saved = true;
APP.Quit();
}
public void SpremanjeFaktura()
{
textBoxPDV.Text = textBoxPDV.Text.Replace("%", "");
textBoxRabat.Text = textBoxRabat.Text.Replace("%", "");
string putanjaDoNoveFakture;
string put = System.IO.File.ReadAllText(Application.StartupPath + "\\Fakture\\PutFakture.txt");
if (textBoxRabat.Text.Length != 0)
putanjaDoNoveFakture = Application.StartupPath + "\\Fakture\\NOVA_RABAT_SLO.xlsx";
else putanjaDoNoveFakture = Application.StartupPath + "\\Fakture\\NOVA_SLO.xlsx";
Excel.Application excelApp = new Excel.Application();
if (radioPlaceno.Checked == true || radioNePlaceno.Checked == true)
{
Microsoft.Office.Interop.Excel.Application ExcelObj = null;
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(putanjaDoNoveFakture, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
int rowIndex, colIndex;
excelApp.Workbooks.Open(putanjaDoNoveFakture);
rowIndex = 17; colIndex = 8;
excelApp.Cells[rowIndex, colIndex] = textBoxDVO.Text;
rowIndex = 18; colIndex = 8;
excelApp.Cells[rowIndex, colIndex] = textBoxValuta.Text;
rowIndex = 21; colIndex = 7;
excelApp.Cells[rowIndex, colIndex] = textBoxBrojFakture.Text;
rowIndex = 22; colIndex = 7;
excelApp.Cells[rowIndex, colIndex] = textBoxRegBroj.Text;
rowIndex = 30; colIndex = 5;
excelApp.Cells[rowIndex, colIndex] = textBoxKolicina.Text;
rowIndex = 30; colIndex = 6;
excelApp.Cells[rowIndex, colIndex] = textBoxCijena.Text;
rowIndex = 27; colIndex = 1;
if (textBoxRabat.Text.Length != 0) rowIndex = 29;
excelApp.Cells[rowIndex, colIndex] = textBoxRelacija.Text;
rowIndex = 30; colIndex = 3;
excelApp.Cells[rowIndex, colIndex] = textBoxJM.Text;
rowIndex = 17; colIndex = 1;
excelApp.Cells[rowIndex, colIndex] = textBoxImeFirme.Text;
rowIndex = 18; colIndex = 1;
excelApp.Cells[rowIndex, colIndex] = textBoxUlica.Text;
rowIndex = 19; colIndex = 1;
excelApp.Cells[rowIndex, colIndex] = textBoxMjesto.Text;
rowIndex = 31; colIndex = 2;
if (textBoxRabat.Text.Length != 0)
{
rowIndex = 31;
colIndex = 3;
}
excelApp.Cells[rowIndex, colIndex] = textBoxPozicija.Text;
//ako je s rabatom
if (textBoxRabat.Text.Length != 0)
{
rowIndex = 30; colIndex = 9;
double rabat = Convert.ToDouble(textBoxRabat.Text);
excelApp.Cells[rowIndex, colIndex] = rabat / 100;
rowIndex = 30; colIndex = 10;
double PDVa = Convert.ToDouble(textBoxPDV.Text);
excelApp.Cells[rowIndex, colIndex] = PDVa / 100;
}
else
{
rowIndex = 30; colIndex = 9;
double PDV = Convert.ToDouble(textBoxPDV.Text);
excelApp.Cells[rowIndex, colIndex] = PDV / 100;
}
rowIndex = 34; colIndex = 6;
string euro = textBoxEur.Text;
if (euro.Contains('.'))
{
textBoxEur.Text = euro.Replace('.', ',');
}
double UkupnaCijenaBezPDV = Convert.ToDouble(textBoxKolicina.Text) * Convert.ToDouble(textBoxCijena.Text);
double UkupnaCijenaPDV = UkupnaCijenaBezPDV * (Convert.ToDouble(textBoxPDV.Text) / 100);
double KonačnaCijena = UkupnaCijenaBezPDV + UkupnaCijenaPDV;
double eur = KonačnaCijena / Convert.ToDouble(textBoxEur.Text);
if (textBoxRabat.Text.Length != 0) excelApp.Cells[39, 6] = eur;
else
excelApp.Cells[rowIndex, colIndex] = eur;
var datum = Convert.ToDateTime(textBoxDVO.Text);
string datumPrikaz = datum.ToShortDateString();
int mjesec = datum.Month;
int godina = datum.Year;
string dira = put + textBoxImeFirme.Text + "\\" + textBoxRelacija.Text + "\\" + mjesec + "-" + godina + "\\PLAČENO\\";
string dirNea = put + textBoxImeFirme.Text + "\\" + textBoxRelacija.Text + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\";
string dir = dira.Replace("\\\\", "\\");
string dirNe = dirNea.Replace("\\\\", "\\");
spremanje = dir + textBoxBrFakture.Text + " " + textBoxDVO.Text + ".xlsx";
spremanjeNe = dirNe + textBoxBrFakture.Text + " " + textBoxDVO.Text + ".xlsx";
System.IO.Directory.CreateDirectory(dirNe);
System.IO.Directory.CreateDirectory(dir);
if (radioPlaceno.Checked)
{
excelApp.ActiveWorkbook.SaveCopyAs(spremanje);
string ime = Path.GetFileNameWithoutExtension(spremanje);
Marshal.ReleaseComObject(ExcelObj);
Marshal.ReleaseComObject(theWorkbook);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(worksheet);
try
{
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
System.Diagnostics.Process.Start(spremanje);
}
if (radioNePlaceno.Checked)
{
excelApp.ActiveWorkbook.SaveCopyAs(spremanjeNe);
string imeNe = Path.GetFileNameWithoutExtension(spremanjeNe);
Marshal.ReleaseComObject(ExcelObj);
Marshal.ReleaseComObject(theWorkbook);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(worksheet);
try
{
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
System.Diagnostics.Process.Start(spremanjeNe);
}
File.AppendAllText(Application.StartupPath + "\\Fakture\\data", System.Environment.NewLine + BrFakture);
}
else
{
MessageBox.Show("Niste označili da li je plačeno ili ne!", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void exportarexcel(DataGridView tabla)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int IndiceColumna = 0;
foreach ( DataGridViewColumn col in tabla.Columns)//columnas
{
IndiceColumna++;
excel.Cells[1, IndiceColumna] = col.Name;
}
int IndiceFila = 0;
foreach (DataGridViewRow row in tabla.Rows)//filas
{
IndiceFila++;
IndiceColumna = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
IndiceColumna++;
excel.Cells[IndiceFila + 1, IndiceColumna] = row.Cells[col.Name].Value;
}
}
excel.Visible = true;
}
public void Template(DataTable dt)
{
string serverpath = this.MapPath(".");
string date = System.DateTime.Now.ToString("ddMMyyyy_hhmmssm");
string newfile = @"" + serverpath + "\\ExcelReports\\BreakEven_Report_" + date + ".xls";
string filename = "BreakEven_Report_" + date + ".xls";
String ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"].ToString();
string newpath = ExcelPath + "BreakEven_Report_" + date + ".xls";
//string newpath = "\\Reports\\ExcelReports\\BreakEven_Report_" + date + ".xls";
lnkexcel.HRef = newpath;
if (File.Exists(newfile) == false)
{
File.Copy(@"" + serverpath + "\\ExcelTemplates\\BreakEven_Report_Template.xls", newfile);
Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
return;
xlWorkbook = xlApp.Workbooks.Open(newfile, 0, false, 5, "", "",
false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
xlNewSheet = (Excel.Worksheet)xlSheets[2];
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
xlNewSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
xlNewSheet = (Excel.Worksheet)xlSheets[1];
hfBevnval.Value = string.IsNullOrEmpty(hfBevnval.Value) ? "0.0" : hfBevnval.Value;
xlNewSheet.Cells[14, 13] = hfBevnval.Value + "%";
//Excel.Worksheet excelc = null;
//excelc=(Excel.Worksheet)xlSheets[2];
//excelc.Cells.Value2("M14", "M14");
//xcel.Range oRange;
// Resize the columns
//oRange = xlNewSheet.get_Range(xlNewSheet.Cells[1, 1],
// xlNewSheet.Cells[rowCount, dt.Columns.Count]);
//oRange.EntireColumn.AutoFit();
objcf.HideDBData_XLSheets(xlSheets, "2");
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
if (proc.MainWindowTitle.ToString() == "")
{
//proc.Kill();
}
}
//Response.Redirect(newfilepath);
string fName = newfile;
//DownLoadFileFromServer(newpath);
}
finally
{
xlApp = null;
}
System.Web.UI.ScriptManager.RegisterStartupScript(this, this.GetType(), "", "anc();", true);
}
}
private void btnToExcel_Click(object sender, EventArgs e)
{
Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (dgvDataShow.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//設定滙出後的存檔路徑(儲存在桌面)
string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) +
@"\即將到期用戶名單" + ".xls";
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = false;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
PGB pgb = new PGB();
pgb.progressBar1.Value = 0;
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = dgvDataShow.Rows.Count - 1;
pgb.progressBar1.Step = 1;
pgb.Show();
//寫入欄位名稱
for (int i = 0; i < dgvDataShow.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvDataShow.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvDataShow.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
Application.DoEvents();
for (int j = 0; j < dgvDataShow.Columns.Count; j++)
{
if (dgvDataShow[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvDataShow[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvDataShow[j, i].Value.ToString();
}
}
}
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit();
//自動調整列高
excel.Cells.EntireColumn.AutoFit();
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//將檔案儲存到SaveFile指定的位置
excel.ActiveWorkbook.SaveCopyAs(SaveFilePath);
if (excel.Application.Version == "11.0")//Office 2007 up
{
excel.ActiveWorkbook.SaveAs(SaveFilePath);
}
else
{
//Office 2003 Up，FileFormat: Excel.XlFileFormat.xlExcel8=>指定Excel 2003 xls格式
excel.ActiveWorkbook.SaveAs(SaveFilePath, FileFormat: Excel.XlFileFormat.xlExcel8);
}
pgb.Dispose();
MessageBox.Show("已成功滙出Excel檔！" + Environment.NewLine + "檔案儲存在您電腦的桌面，檔名：即將到期用戶名單" +
".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//關閉工作簿和結束Excel程式
excel.Workbooks.Close();
excel.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
private void initCom()
{
System.Diagnostics.Process[] pTemp = System.Diagnostics.Process.GetProcesses();
int count = 0;
foreach (System.Diagnostics.Process pTempProcess in pTemp)
{
if ((pTempProcess.ProcessName.ToLower() == ("excel").ToLower()) || (pTempProcess.ProcessName.ToLower()) == "excel.exe" || (pTempProcess.ProcessName.ToLower()) == "winexcel.exe" || (pTempProcess.ProcessName.ToLower()) == "winexcel")
{
count++;
}
}
// 保持有两个WordApp
for (int i = 0; i < (2 - count); i++)
{
excelapp = new Excel.ApplicationClass();
}
}
//生成Excel
private bool ExportToExcel(DataTable dt)
{
bool result = false;
this.UseWaitCursor = true;
if (dt.Rows.Count > 65536)
{
MessageBox.Show("數據記錄太多(最多不能超過65536條)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return result;
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
labMsg.Text = "Excel正在匯出中…………";
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机器未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(true);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "過膠機生產日報表";
//worksheet.QueryTables.Add
#region worksheet的格式
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印
worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印
worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效
worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁
worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中
worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102);
worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126);
worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126);
xlApp.Visible = false;
#endregion
#region 列名
#region 循環填充列名，很慢，已經REMARK
//for (i = 1; i <= dt.Columns.Count - 1; i++)
//{
// if (i == 11 || i == 12 || i == 13)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3);
// }
// else if (i == 14 || i == 15 || i == 16 || i == 17)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8);
// }
// else if (i >= 23 && i <= 31)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim();
// }
// else
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName;
// }
//}
#endregion
int RowIndex = 2;
MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0);
RowIndex = RowIndex + 1;
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9);
#endregion
#region 填充數據 向Excel中逐行逐列写入表格中的数据
RowIndex = RowIndex + 1; //數據開始的行號
int ExcelCurrectRow = RowIndex; //當前行
int dtRowNO;
int realRowNO = 0;//dt實際行
int dtKeyRowNO = RowIndex;//NO:4
int megreCount = 0;
int dtMetrialRowNO = 23;//物料編號所在列
int tempRow;
int tempCol;
#region 較慢的填充數據方法 已經REMARK
//for (int row = 0; row < dt.Rows.Count; row++)
//{
// //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString());
// for (int col = 1; col < dt.Columns.Count; col++)
// {
// if (row > 0) //班次內工程ID相等 col22之後不合併
// {
// if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22)
// {
// CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true);
// }
// else
// {
// CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
// xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
// if (col < 22) currectRow = RowIndex;
// }
// }
// else
// {
// CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
// xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
// }
// }
// RowIndex++;
// //if (RowIndex == 15) goto range; // for 測試
//}
#endregion
DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" });
for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++)
{
DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' ");
megreCount = currectKeyRow.Length;
for (tempRow = 0; tempRow < megreCount; tempRow++)
{
if (tempRow == 0)
{
for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true);
}
realRowNO += megreCount;
}
for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false);
}
dtKeyRowNO++;
}
//dtKeyRowNO++;
//if (realRowNO > dt.Rows.Count) break;
/*
* DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" });
List<Thread> threads = new List<Thread>();
for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++)
{
*/
}
#endregion
#region 設置Sheet邊框
//range:
Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]);
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
#endregion
Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]);
DataFormat.Font.Name = "楷体";
DataFormat.Font.Size = 10;
Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]);
IntFormat.NumberFormatLocal = "0.00";
#region savecode
Object missing = Missing.Value;
SaveFileDialog saveForm = new SaveFileDialog();
saveForm.Filter = "Execl files (*.xls)|*.xls";
saveForm.FilterIndex = 0;
saveForm.FileName = "過膠機生產日報表";
if (saveForm.ShowDialog() == DialogResult.OK)
{
string fileName = saveForm.FileName;
workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
#endregion
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
labMsg.Text = "";
this.UseWaitCursor = false;
xlApp.Quit();
IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
System.GC.Collect();
}
labMsg.Text = "";
return result;
}
ref LV_ITEM lParam );// pointer to struct of LV_ITEM
#endregion
/// <summary>
/// 方法，导出DataTable中的数据到Excel文件 , 多个不同的数据结构显示在一个Excel页面中。
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name="dgv">DataTable</param>
/// <param name="strOutFileName">设定导出的文件名称</param>
/// <param name="strsHeadName">导出的列的的HeadName, 如果为null,则导出全部列</param>
/// <returns></returns>
public static bool SaveDtToExcel(ArrayList aryParam)
{
if (aryParam == null || aryParam.Count == 0)
return false;
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
string fileNameString = ""; //Excel的文件名称。
int n = 0; //标明当前是第几个数据结构。
int nLine = 1; //每个数据结构的开始的行数
int nJG = 5; //每个数据结构之间 间隔的行数
foreach (EXCELPARAM param in aryParam)
{
DataTable dt = param.dt;
string strOutFileName = param.strOutFile;
string[] strsHeadName = param.strsHeadName;
string[] strsFldName = param.strsFldName;
int[] nsLineNo = null;
if (dt.Rows.Count <= 0)
{
MessageBox.Show("没有可以导出的数据！", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
//处理第一个数据结构。 选择文件名称。
if (n == 0)
{
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.FileName = strOutFileName;
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
return false;
//返回文件路径
fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
return false;
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
}
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
}
//定义表格内数据的行数和列数
int rowscount = dt.Rows.Count;
int colscount = dt.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
#endregion
#region
//取得要导出的列的HeadName.
//如果没有指定特定的列，则默认转换 可见的 并且 有HeadName 的列。（控件列不用导出，控件列一般没有HeadName）
if (strsHeadName == null)
{
MessageBox.Show("没有指定字段名称！", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
//取得所有要执行的行，（可能用户并不是要导出所有行，而是通过checkbox选择要导出的行）
//如果没有指定特定的行，则默认转出全部的行
if (nsLineNo == null)
{
nsLineNo = new int[dt.Rows.Count];
for (int row = 0; row <= dt.Rows.Count - 1; row++)
{
nsLineNo[row] = row;
}
}
#endregion
try
{
//向Excel中写入表格的表头
Excel.Range rag = objsheet.get_Range(objsheet.Cells[nLine, 1], objsheet.Cells[nLine, strsHeadName.Length]);
rag.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
int displayColumnsCount = 1;
foreach (string strHeadName in strsHeadName)
{
objExcel.Cells[nLine, displayColumnsCount] = strHeadName;
displayColumnsCount++;
}
//定义每列的数据格式。
int nRow1 = nLine + 1, nRow2 = nRow1 + nsLineNo.Length;
int nColSel = 1;
foreach (string strFldName in strsFldName)
{
rag = objsheet.get_Range(objsheet.Cells[nRow1, nColSel], objsheet.Cells[nRow2, nColSel]);
switch (dt.Columns[strFldName].DataType.ToString())
{
case "System.String":
rag.NumberFormatLocal = "@";
break;
case "System.DateTime":
rag.NumberFormatLocal = "yyyy-MM-dd HH:mm";
break;
//可以根据自己的需要扩展。
default:
//rag.NumberFormatLocal = "G/通用格式";
break;
}
nColSel++;
}
//向Excel中逐行逐列写入表格中的数据
int nCol, row;
int nExcelCol, nExcelRow = nLine + 1; //Execl的行列都从1开始起算。第一行是列名，数据从第二行开始写入。
for (int i = 0; i <= nsLineNo.Length - 1; i++)
{
row = nsLineNo[i];
nExcelCol = 1; //Execl的行列都从1开始起算。第一行是列名，数据从第二行开始写入。
foreach (string strFldName in strsFldName)
{
try
{
objExcel.Cells[nExcelRow, nExcelCol++] = dt.Rows[row][strFldName].ToString().Trim();
}
catch (Exception)
{
}
}
nExcelRow++;
}
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
//
n++;
nLine += nsLineNo.Length + nJG;
}
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return true;
}
/// <summary>
/// Initialize File
/// </summary>
private void CreateExcel()
{
app = new Excel.Application();
workbook = app.Workbooks.Open(ExcelFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheets = workbook.Sheets;
//get first sheet of file excel
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worksheets.get_Item(1);
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
bool detailed = radioButtonDetailledMode.Checked;
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.get_Range("a1", "f1").Merge(false);
Excel.Range chartRange = xlWorkSheet.get_Range("a1", "f1");
chartRange.FormulaR1C1 = string.Format("{0} assets information, media account '{1}'", radioButtonAllAssets.Checked ? "All" : "Selected", _context.Credentials.ClientId);
chartRange.VerticalAlignment = 3;
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
chartRange.Font.Size = 20;
xlWorkSheet.get_Range("a2", "f2").Merge(false);
Excel.Range chartRange2 = xlWorkSheet.get_Range("a2", "f2");
chartRange2.FormulaR1C1 = string.Format("Exported with Azure Media Services Explorer v{0} on {1}. Dates are {2}.",
Assembly.GetExecutingAssembly().GetName().Version.ToString(),
checkBoxLocalTime.Checked ? DateTime.Now.ToString() : DateTime.UtcNow.ToString(),
checkBoxLocalTime.Checked ? "local" : "UTC based"
);
chartRange2.VerticalAlignment = 3;
chartRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
chartRange2.Font.Size = 12;
int row = 4;
int index = 1;
xlWorkSheet.Cells[row, index++] = "Asset Name";
xlWorkSheet.Cells[row, index++] = "Id";
xlWorkSheet.Cells[row, index++] = "Last Modified";
xlWorkSheet.Cells[row, index++] = "Type";
xlWorkSheet.Cells[row, index++] = "Size";
int backindex = index;
_context.StreamingEndpoints.ToList().ForEach(se =>
xlWorkSheet.Cells[row, index++] = "Streaming URL"
);
index = backindex + _context.StreamingEndpoints.Count();
xlWorkSheet.Cells[row, index++] = "Expiration time";
if (detailed)
{
xlWorkSheet.Cells[row, index++] = "Alternate Id";
xlWorkSheet.Cells[row, index++] = "Storage Account";
xlWorkSheet.Cells[row, index++] = "Streaming Locators Count";
xlWorkSheet.Cells[row, index++] = "Streaming Min Expiration time";
xlWorkSheet.Cells[row, index++] = "Streaming Max Expiration time";
xlWorkSheet.Cells[row, index++] = "SAS Locators Count";
xlWorkSheet.Cells[row, index++] = "SAS Min Expiration time";
xlWorkSheet.Cells[row, index++] = "SAS Max Expiration time";
xlWorkSheet.Cells[row, index++] = "Dynamic encryption";
xlWorkSheet.Cells[row, index++] = "Asset filters count";
}
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a4");
formatRange.EntireRow.Font.Bold = true;
formatRange.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
if (radioButtonAllAssets.Checked)
{
int skipSize = 0;
int batchSize = 1000;
int currentBatch = 0;
int total = _context.Assets.Count();
int index2 = 1;
while (true)
{
IQueryable _assetsCollectionQuery = _context.Assets.Skip(skipSize).Take(batchSize);
foreach (IAsset asset in _assetsCollectionQuery)
{
row++;
currentBatch++;
ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked);
backgroundWorker1.ReportProgress(100 * index2 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example.
//if cancellation is pending, cancel work.
if (backgroundWorker1.CancellationPending)
{
xlApp.DisplayAlerts = false;
xlWorkBook.Close();
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
e.Cancel = true;
return;
}
index2++;
}
if (currentBatch == batchSize)
{
skipSize += batchSize;
currentBatch = 0;
}
else
{
break;
}
}
}
else // Selected or visible asets
{
IEnumerable<IAsset> myassets;
if (radioButtonSelectedAssets.Checked)
{
myassets = _selassets;
}
else
{
myassets = _visibleassets;
}
int total = myassets.Count();
int index3 = 1;
foreach (IAsset asset in myassets)
{
row++;
ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked);
backgroundWorker1.ReportProgress(100 * index3 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example.
//if cancellation is pending, cancel work.
if (backgroundWorker1.CancellationPending)
{
xlApp.DisplayAlerts = false;
xlWorkBook.Close();
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
e.Cancel = true;
return;
}
index3++;
}
}
// Set the range to fill.
var aRange = xlWorkSheet.get_Range("A4", "Z100");
aRange.EntireColumn.AutoFit();
try
{
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch
{
MessageBox.Show("Error when saving the Excel file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (checkBoxOpenFileAfterExport.Checked) System.Diagnostics.Process.Start(filename);
}
public void createPortfolioXlFile(string folderPath)
{
if (!System.IO.Directory.Exists(folderPath))
{
throw new DirectoryNotFoundException();
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
object misValue = System.Reflection.Missing.Value;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int count = 1;
foreach (string colName in columnNames)
{
xlWorkSheet.Cells[1, count++] = colName;
}
Excel.Range row1 = xlWorkSheet.UsedRange;
row1.Columns.AutoFit();
try
{
xlWorkBook.SaveAs(path);
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
}
finally
{
Utility.releaseObject(xlWorkSheet);
Utility.releaseObject(xlWorkBook);
Utility.releaseObject(xlApp);
}
}
private void boutonExporter_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Fichier Excel (*.xlsx)|*.xlsx";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Exporter le fichier Excel vers...";
saveFileDialog.ShowDialog();
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";
for(int i=1;i<dataGridView1.Columns.Count+1;i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
}
for (int i=0; i < dataGridView1.Rows.Count ; i++)
{
for(int j=0;j<dataGridView1.Columns.Count;j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[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();
}
