private void ExcelB_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.get_Range("A1").Value = "Id";
ExcelApp.get_Range("B1").Value = "Login";
ExcelApp.get_Range("C1").Value = "Password";
ExcelApp.get_Range("E1").Value = "Данные за: " + DateTime.Now.ToShortDateString();
for (int i = 1; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Rows[i].Cells[j].GetType().Name == "DataGridViewComboBoxCell")
{
DataGridViewComboBoxCell DataC = new DataGridViewComboBoxCell();
DataC = (DataGridViewComboBoxCell)dataGridView1.Rows[i].Cells[j];
ExcelApp.Cells[i + 1, j + 1] = DataC.EditedFormattedValue;
}
else
{
ExcelApp.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
}
ExcelApp.Visible = true;
ExcelApp.UserControl = true;
}
private void SetExcelFormat(Microsoft.Office.Interop.Excel.Application excel, ref int row, IGrouping <string, Model.Product> item)
{
excel.Cells[row, 1] = item.Key;
excel.get_Range(excel.Cells[row, 1], excel.Cells[row, 9]).Interior.Color = global::Helper.ExcelColor.LightPink;
excel.get_Range(excel.Cells[row, 4], excel.Cells[row, 4]).Formula = string.Format("=SUM(D{0}:C{1})", row + 1, row + item.Count());
row++;
foreach (var pro in item)
{
excel.Cells[row, 1] = pro.DepotName;
excel.Cells[row, 2] = pro.Id;
excel.Cells[row, 3] = pro.ProductName;
excel.Cells[row, 4] = pro.StocksQuantity;
excel.Cells[row, 5] = pro.CnName;
excel.Cells[row, 6] = pro.HandbookId;
excel.Cells[row, 7] = pro.HandbookProductId;
excel.Cells[row, 8] = pro.CustomerInvoiceXOId;
excel.Cells[row, 9] = (pro.NetWeight.Value / 1000).ToString("0.#####");
int col = 10;
foreach (var dic in pro.MaterialDic)
{
excel.Cells[row, col++] = dic.Value;
}
row++;
}
//每组数据之间不留空行
//row++;
}
private void SetExcelFormat(Microsoft.Office.Interop.Excel.Application excel, ref int row, IGrouping <string, Model.Product> item)
{
excel.Cells[row, 1] = item.Key;
excel.get_Range(excel.Cells[row, 1], excel.Cells[row, 1]).Interior.Color = "255";
excel.get_Range(excel.Cells[row, 3], excel.Cells[row, 3]).Formula = string.Format("=SUM(C{0}:C{1})", row + 1, row + item.Count());
row++;
foreach (var pro in item)
{
excel.Cells[row, 1] = pro.Id;
excel.Cells[row, 2] = pro.ProductName;
excel.Cells[row, 3] = pro.StocksQuantity;
excel.Cells[row, 4] = pro.CnName;
excel.Cells[row, 5] = (pro.NetWeight.Value / 1000).ToString("0.#####");
int col = 6;
foreach (var dic in pro.MaterialDic)
{
excel.Cells[row, col++] = dic.Value;
}
row++;
}
//每组数据之间不留空行
//row++;
}
public void ExportarExcel(DataGridView grade, string texto)
{
//exportar dados para o excel
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "Salvar Como Arquivo Excel";
saveFileDialog.FileName = "Relatório";
saveFileDialog.Filter = "Excel 2007|*.xlsx|Excel 2003|*.xls";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Cells[1, 2] = texto;
for (int coluna = 1; coluna < grade.Columns.Count + 1; coluna++)
{
ExcelApp.Cells[3, coluna] = grade.Columns[coluna - 1].HeaderText;
}
for (int linha = 0; linha < grade.Rows.Count; linha++)
{
for (int coluna = 0; coluna < grade.Columns.Count; coluna++)
{
ExcelApp.Cells[linha + 4, coluna + 1] = grade.Rows[linha].Cells[coluna].Value.ToString();
}
}
ExcelApp.Columns.AutoFit();
Microsoft.Office.Interop.Excel.Range celulas;
//Aqui para baixo configuro o texto de layout
celulas = ExcelApp.get_Range("A1", "E1");
celulas.MergeCells = true;
celulas.Font.Italic = true;
celulas.Font.Size = 20F;
celulas = ExcelApp.get_Range("A1", "F1"); //Recupero o conjunto de células de A1 até Z1
celulas.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; //centralizo as células
celulas.Font.Bold = true; //mudo a fonte para negrito
//Aqui para baixo configuro o nome da coluna
celulas = ExcelApp.get_Range("A3", "Z3");
celulas.Font.Size = 12F;
celulas.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; //centralizo as células
celulas.Font.Bold = true; //mudo a fonte para negrito
//celulas.Interior.Color = ColorTranslator.ToWin32(Color.LightGray); //mudo as cores das celulas
//celulas.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //insiro a borda nas celulas
ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog.FileName.ToString());
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Arquivo exportado com sucesso!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("Inconsitência: " + ex.Message, "Possível falha", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void SetExcelFormat(Microsoft.Office.Interop.Excel.Application excel, ref int col, ref int row, IGrouping <string, Book.Model.AssemblySiteInventoryDetail> item)
{
excel.Cells[row, 1] = item.Key;
excel.get_Range(excel.Cells[row, 1], excel.Cells[row, 5 + 1 + this._assemblySiteInventory.Details[0].Product.MaterialDic.Keys.Count]).Interior.Color = "255"; //红色
excel.get_Range(excel.Cells[row, 5], excel.Cells[row, 5]).Formula = string.Format("=SUM(E{0}:E{1})", row + 1, row + item.Count());
col = 7;
foreach (var ec in this._assemblySiteInventory.Details[0].Product.MaterialDic)
{
string excelColumnName = CountExcelColumnName(col);
excel.get_Range(excel.Cells[row, col], excel.Cells[row, col]).Formula = string.Format("=SUM({2}{0}:{2}{1})", row + 1, row + item.Count(), excelColumnName);
col++;
}
row++;
}
private void button12_Click(object sender, EventArgs e)
{
// 엑셀 파일로 저장
Microsoft.Office.Interop.Excel.Application xcelApp =
new Microsoft.Office.Interop.Excel.Application();
xcelApp.Application.Workbooks.Add(Type.Missing);
// 엑셀 서식 지정
string rangStr = "A2:A" + (dataGridView1.Rows.Count + 1);
MessageBox.Show(rangStr);
Microsoft.Office.Interop.Excel.Range range =
xcelApp.get_Range(rangStr, Type.Missing);
range.NumberFormat = "0";
// Head Text 설정
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
xcelApp.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++)
{
xcelApp.Cells[i + 2, j + 1] =
dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
xcelApp.Columns.AutoFit();
xcelApp.Visible = true;
}
static void TestCOM()
{
//<Snippet3>
// In C# 3.0 and earlier versions, you need to supply an argument for
// every parameter. The following call specifies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Workbooks.Add();
excelApp.Visible = true;
var myFormat =
Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//</Snippet3>
//<Snippet13>
// The following code shows the same call to AutoFormat in C# 4.0. Only
// the argument for which you want to provide a specific value is listed.
excelApp.Range["A1", "B4"].AutoFormat(Format: myFormat);
//</Snippet13>
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 30;
excel.Cells[1, 1] = "INFORME DE VENTAS POR ORIGEN DE ORDEN";
excel.Cells[2, 1] = "DESDE " + sFechaInicio + " A " + sFechaFin;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
//excel.Cells[1, iIndiceColumna] = col.Name;
}
int iIndiceFila = 3;
excel.Cells[6, 2].Interior.Color = Color.Aquamarine;
excel.Cells[8, 1].Interior.Color = Color.Yellow;
excel.Cells[8, 2].Interior.Color = Color.Yellow;
excel.Cells[8, 3].Interior.Color = Color.Yellow;
excel.get_Range("B6", "B6").BorderAround();
excel.get_Range("A8", "C8").BorderAround();
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
}
}
excel.Visible = true;
}
/// <summary>
/// 创建一个新的Excel文件,并写入数据集
/// <para>请注意,Excel文件可能创建失败</para>
/// <para>原因:指定Excel文件已存在</para>
/// </summary>
/// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
/// <param name="path">请提供一个Excel文件路径,如果不存在则自动创建</param>
/// <returns></returns>
public static bool CreateExcel(DataSet ds, string path)
{
bool result = true;
Microsoft.Office.Interop.Excel.Application excel =
new Microsoft.Office.Interop.Excel.Application();//create Excel manipulate objects
try
{
excel.SheetsInNewWorkbook = ds.Tables.Count; //获取Sheet档数量
excel.Workbooks.Add();
for (int number = 0; number < ds.Tables.Count; number++) //循环添加Sheet档
{
Microsoft.Office.Interop.Excel.Worksheet sheet =
excel.ActiveWorkbook.Worksheets[number + 1] as
Microsoft.Office.Interop.Excel.Worksheet; //获取sheet;
DataTable dt = ds.Tables[number]; //获取表
sheet.Name = dt.TableName; //设置Sheet档名
int i = 0;
for (; i < dt.Columns.Count; i++) //循环添加列头
{
sheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; //设置列头名
}
Microsoft.Office.Interop.Excel.Range range =
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, i]); //设置编辑区域
range.Font.Bold = true; //字体加粗
range.Font.ColorIndex = 0; //字体颜色
range.Interior.ColorIndex = 15; //背景颜色
range.ColumnWidth = 15; //列宽
range.Borders.LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //边框样式
range.HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //字体居中
for (int y = 0; y < dt.Rows.Count; y++) //循环添加行
{
for (int x = 0; x < dt.Rows[y].Table.Columns.Count; x++) //循环添加列
{
sheet.Cells[y + 2, x + 1] = dt.Rows[y][x]; //设置列值
}
}
}
excel.Visible = false;//设置为预览
//System.Threading.Thread.Sleep(5000);//线程延迟5秒再预览
excel.ActiveWorkbook.SaveAs(path,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);//另存为
}
catch (Exception ex)
{
ex.Message.ToString();
result = false;
}
finally
{
excel.ActiveWorkbook.Close(); //关闭Excel对象
excel.Quit(); //退出
KillEXCELProcess();
}
return(result);
}
/// <summary>
/// DataSet导出到Excel
/// </summary>
public static void DataSetToExcel(DataSet dataSet, String savePath)
{
DataTable dataTable = dataSet.Tables[0]; //默认只导出第一个table
int rowNumber = dataTable.Rows.Count; //不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Application.Workbooks.Add(true);
//这样就只会打开一个sheet，不会出现下面说到的打开两个sheet：一个空白，一个有数据的情况
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.ActiveWorkbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range range;
try
{
//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excelApp.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
Application.DoEvents();
}
// 写入Excel
range = excelApp.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[rowNumber + 1, columnNumber]);
range.NumberFormat = "@"; //设置单元格为文本格式
range.Value2 = objData;
//excelApp.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
workbook.SaveAs(savePath);
MessageBox.Show("导出成功！");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
workbook.Close();
workbook = null;
excelApp.Quit();
KillProcess(excelApp);
}
}
private void button3_Click(object sender, EventArgs e)
{
// Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
// Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range chartRange;
if (kdvcustomer.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Application XcelApp = new Microsoft.Office.Interop.Excel.Application();
XcelApp = new Microsoft.Office.Interop.Excel.Application();
//xlWorkBook = XcelApp.Workbooks.Add (misValue);
// xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item (1);
XcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 1; i < kdvcustomer.Columns.Count + 1; i++)
{
XcelApp.Cells [1, i] = kdvcustomer.Columns [i - 1].HeaderText;
}
for (int i = 0; i < kdvcustomer.Rows.Count; i++)
{
for (int j = 0; j < kdvcustomer.Columns.Count; j++)
{
XcelApp.Cells [i + 2, j + 1] = kdvcustomer.Rows [i].Cells [j].Value.ToString();
}
}
XcelApp.Columns.AutoFit();
chartRange = XcelApp.get_Range("A1", "P1");
chartRange.Font.Bold = true;
XcelApp.Visible = true;
//chartRange = XcelApp.get_Range ("H1");
//chartRange.FormulaR1C1 = "RECEIVED EQUIPMENT LIST";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.CadetBlue);
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Crimson);
chartRange.Font.Size = 15;
}
}
public static void XlsUtil(DataTable table)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = true;
for (int i = 0; i < table.Columns.Count; i++)
{
excel.Cells[1, i + 1] = table.Columns[i].Caption;
}
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
if (table.Columns[j].ColumnName == "正味價格" || table.Columns[j].ColumnName == "納期")
{
string str = table.Rows[i][j].ToString();
excel.Cells[i + 2, j + 1] = str;
}
else
{
string str = "'" + table.Rows[i][j].ToString();
excel.Cells[i + 2, j + 1] = str;
}
}
}
}
Microsoft.Office.Interop.Excel.Range oRange = excel.get_Range("A1", "A1");
oRange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Range iRange = excel.get_Range("A1", "A1");
iRange.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
}
public static void ExportDatagridview(DataGridView dgv)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = true;
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible == true)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
}
if (dgv.Rows.Count > 0)
{
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible != false)
{
string str = "'" + dgv.Rows[i].Cells[j].Value.ToString();
excel.Cells[i + 2, j + 1] = str;
}
}
}
}
Microsoft.Office.Interop.Excel.Range oRange = excel.get_Range("A1", "A1");
oRange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Range iRange = excel.get_Range("A1", "A1");
iRange.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 30;
excel.Cells[1, 1] = "INFORME FLUJO DIARIO DE CLIENTES";
excel.Cells[2, 1] = "DESDE " + sFechaInicio + " A " + sFechaFin;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
if (iIndiceColumna != 0)
{
excel.Columns.ColumnWidth = 15;
}
excel.Cells[4, iIndiceColumna] = col.HeaderText;
excel.Cells[4, iIndiceColumna].Interior.Color = Color.Yellow;
excel.Cells[4, iIndiceColumna].BorderAround();
}
int iIndiceFila = 4;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
excel.Cells[iIndiceFila + 1, iIndiceColumna].BorderAround();
}
}
excel.get_Range("A4", "G4").BorderAround();
excel.Visible = true;
}
// Prints all the unique, recorded, MAC-addresses in the textfield.
private void PrintMacAddresses_Click(object sender, EventArgs e)
{
StringBuilder macAddresses = new StringBuilder();
dataTextBox.Clear();
// Start Excel
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
// Get path to excecutable (Same place as where the Excel Sheet is)
string exeDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
// Open workbook
Microsoft.Office.Interop.Excel._Workbook oWB = oXL.Workbooks.Open(System.IO.Path.Combine(exeDir, "MAC adress list example.xlsx"));
Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
int rowIdx;
int notEmpty;
foreach (string macAddress in uniqueMacAddresses)
{
rowIdx = 1;
notEmpty = 1;
macAddresses.Append(macAddress + "\n");
// Loops through the rows in the Excel-document until you find
// a row that is completely empty.
while (notEmpty > 0)
{
string aCellAddress = "B" + rowIdx.ToString();
Microsoft.Office.Interop.Excel.Range row = oXL.get_Range(aCellAddress, aCellAddress).EntireRow;
notEmpty = (int)oXL.WorksheetFunction.CountA(row);
rowIdx++;
}
// Set the first found completely empty row to a MAC-address value
oSheet.Cells[rowIdx - 1, 2] = macAddress;
}
dataTextBox.Text = macAddresses.ToString();
// Clears all the addresses so that they are removed when we are printing the next time.
uniqueMacAddresses.Clear();
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 20;
excel.Cells[1, 1] = "INFORME DE VENTAS POR PRODUCTOS";
excel.Cells[2, 1] = "DESDE " + sfechaInicio + " A " + sfechaFin;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
if (iIndiceColumna == 1)
{
excel.Cells[1, iIndiceColumna].ColumnWidth = 40;
}
excel.Cells[5, iIndiceColumna] = col.HeaderText;
excel.Cells[5, iIndiceColumna].Interior.Color = Color.Yellow;
}
int iIndiceFila = 5;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
excel.Cells[iIndiceFila + 1, iIndiceColumna].BorderAround();
}
}
excel.get_Range("A5", "C5").BorderAround();
excel.Visible = true;
}
static void SplitAndMail(string FileName)
{
Microsoft.Office.Interop.Excel.Application xlApp;
xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
FileInfo fi = new FileInfo(FileName);
string FullFileName = fi.FullName.ToString();
try
{
xlWorkBook = xlApp.Workbooks.Open(FullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
catch
{
Console.WriteLine("You must choose a file");
return;
}
xlWorkBook = xlApp.Workbooks.Open(FullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Range rng = xlApp.get_Range("A1");
//int index = 0;
string liteFileName = FileName.Replace(".xlsx", "");
foreach (Microsoft.Office.Interop.Excel.Worksheet displayWorksheet in xlApp.Worksheets)
{
string root = fi.Directory.ToString();
string sheetName = displayWorksheet.Name.ToString();
string SaveFileName = root + "\\" + liteFileName + "." + sheetName + ".xlsx";
//Microsoft.Office.Interop.Excel.Application NewxlApp;
//NewxlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook NewWorkbook;
NewWorkbook = xlApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet NewWorkSheet;
NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkbook.Worksheets.Add();
displayWorksheet.Copy(NewWorkSheet);
NewWorkbook.SaveAs(SaveFileName);
NewWorkbook.Save();
NewWorkbook.Close();
}
}
/// <summary>
/// 读取指定单元格数据
/// </summary>
/// <param name="row">行序号</param>
/// <param name="column">列序号</param>
/// <returns>该格的数据</returns>
public string ReadData(string fileName, string sheetName, int row, int column)
{
Microsoft.Office.Interop.Excel.Application myExcel;
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
myExcel = new Microsoft.Office.Interop.Excel.Application();
//myWorkBook = myExcel.Application.Workbooks.Add(true);
myWorkBook = myExcel.Workbooks.Add(fileName);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet.Name = sheetName;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
Microsoft.Office.Interop.Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
string str = range.Text.ToString();
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
return(str);
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 20;
excel.Cells[1, 1] = "PRODUCTOS";
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[3, iIndiceColumna] = col.HeaderText;
excel.Cells[3, iIndiceColumna].Interior.Color = Color.Yellow;
}
int iIndiceFila = 3;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
excel.Cells[iIndiceFila + 1, iIndiceColumna].BorderAround();
}
}
excel.get_Range("A3", "E3").BorderAround();
excel.Visible = true;
}
private void buttonExcel_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.get_Range("A1").Value = "Id";
ExcelApp.get_Range("B1").Value = "login";
ExcelApp.get_Range("C1").Value = "password";
ExcelApp.get_Range("D1").Value = "fullname";
ExcelApp.get_Range("E1").Value = "phone";
ExcelApp.get_Range("F1").Value = "Company";
ExcelApp.get_Range("G1").Value = "Дата создания документа: " + DateTime.Now.ToShortDateString();
for (int i = 1; i < usersDataGridView.Rows.Count; i++)
{
for (int j = 0; j < usersDataGridView.ColumnCount; j++)
{
if (usersDataGridView.Rows[i].Cells[j].GetType().Name == "DataGridViewComboBoxCell")
{
DataGridViewComboBoxCell dgvcbc = new DataGridViewComboBoxCell();
dgvcbc = (DataGridViewComboBoxCell)usersDataGridView.Rows[i].Cells[j];
ExcelApp.Cells[i + 1, j + 1] = dgvcbc.EditedFormattedValue;
}
else
{
ExcelApp.Cells[i + 1, j + 1] = usersDataGridView.Rows[i].Cells[j].Value;
}
}
}
//Вызываем нашу созданную эксельку.
ExcelApp.Visible = true;
ExcelApp.UserControl = true;
}
private void ExportExcel(DataGridView dgv)
{
try
{
if (dgv.Rows.Count >= 1)
{
labelProgMon.Text = "开始导出";
labelProgMon.Update();
string yr_str = comboBoxYear1.Text.Trim();
string mon_str = comboBoxMon1.Text.Trim();
string day_str = comboBoxDay1.Text.Trim();
string fName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.InitialDirectory = "d:";
saveFileDialog.Filter = "EXCEL文件|*.xlsx";
saveFileDialog.FilterIndex = 2;
if (day_str == "")
{
saveFileDialog.FileName = yr_str + "年" + mon_str + "月清运垃圾明细表";
}
else
{
saveFileDialog.FileName = yr_str + "年" + mon_str + "月" + day_str + "日清运垃圾明细表";
}
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
fName = saveFileDialog.FileName;
progressBarMon.Value = 12;
progressBarMon.Update();
this.Refresh();
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;// new Microsoft.Office.Interop.Excel.Workbooks();
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook
Microsoft.Office.Interop.Excel.Worksheet ws;
//MessageBox.Show("355", "process", MessageBoxButtons.OK, MessageBoxIcon.None);
int num_sheet = dgv.Rows.Count / 55;
labelProgMon.Text = "完成 " + progressBarMon.Value.ToString() + "%";
labelProgMon.Update();
//MessageBox.Show(num_sheet.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
for (int cur_sheet = 1; cur_sheet <= num_sheet; cur_sheet++)
{
if (num_sheet != 1)
{
progressBarMon.Value = Convert.ToInt32(12 + cur_sheet * 87 / num_sheet);
progressBarMon.Update();
//label5.Text = "完成 " + progressBar1.Value.ToString() + "%";
//label5.Update();
}
else
{
progressBarMon.Value = 50;
progressBarMon.Update();
//label5.Text = "完成 " + progressBar1.Value.ToString() + "%";
//label5.Update();
}
if (cur_sheet == 1)
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"];
else
ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;
ws.Name = cur_sheet.ToString();
//生成字段名称
excel.Cells[1, 1] = " 日期\n名称";
excel.Cells[1, 2] = "1";
excel.Cells[1, 3] = "2";
excel.Cells[1, 4] = "3";
excel.Cells[1, 5] = "4";
excel.Cells[1, 6] = "5";
excel.Cells[1, 7] = "6";
excel.Cells[1, 8] = "7";
excel.Cells[1, 9] = "8";
excel.Cells[1, 10] = "9";
excel.Cells[1, 11] = "10";
excel.Cells[1, 12] = "11";
excel.Cells[1, 13] = "12";
excel.Cells[1, 14] = "13";
excel.Cells[1, 15] = "14";
excel.Cells[1, 16] = "15";
excel.Cells[1, 17] = "合计";
excel.Cells[1, 18] = "";
excel.Cells[2, 1] = "";
excel.Cells[2, 2] = "箱数";
excel.Cells[2, 3] = "吨数";
excel.Cells[2, 4] = "吨数";
excel.Cells[2, 5] = "吨数";
excel.Cells[2, 6] = "吨数";
excel.Cells[2, 7] = "吨数";
excel.Cells[2, 8] = "吨数";
excel.Cells[2, 9] = "吨数";
excel.Cells[2, 10] = "吨数";
excel.Cells[2, 11] = "吨数";
excel.Cells[2, 12] = "吨数";
excel.Cells[2, 13] = "吨数";
excel.Cells[2, 14] = "吨数";
excel.Cells[2, 15] = "吨数";
excel.Cells[2, 16] = "吨数";
excel.Cells[2, 17] = "吨数";
excel.Cells[2, 18] = "总箱数";
Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[1, 17], excel.Cells[1, 18]);
merge_range.Merge(Type.Missing);
Microsoft.Office.Interop.Excel.Range merge_range2 = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]);
merge_range2.Merge(Type.Missing);
//填充数据
for (int i = (cur_sheet - 1) * 56 + 1; i < (cur_sheet - 1) * 56 + 57; i++)
{
for (int j = 0; j < dgv.ColumnCount - 1; j++)
{
if (dgv[j, i - 1].Value != null)
{
excel.Cells[i - (cur_sheet - 1) * 56 + 2, j + 1] = dgv[j, i - 1].Value;
}
else
excel.Cells[i - (cur_sheet - 1) * 56 + 2, j + 1] = "";
}
}
Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[58, 18]);//现有的
all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range c11_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]);
c11_range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
all_range.EntireColumn.AutoFit(); //自动调整列宽
all_range.EntireRow.AutoFit();
all_range.Borders.LineStyle = 1;
labelProgMon.Text = "完成 " + progressBarMon.Value.ToString() + "%";
labelProgMon.Update();
}//end for每个sheet
progressBarMon.Value = progressBarMon.Maximum;
progressBarMon.Update();
wb.Saved = true;
wb.SaveCopyAs(fName); //保存
excel.Quit(); //关闭进程
labelProgMon.Text = "已完成";
labelProgMon.Update();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
//label5.Text = "完成";
toolStripButtonMonExl.Enabled = false;
}
else
{
progressBarMon.Visible = false;
progressBarMon.Update();
labelProgMon.Text = "";
labelProgMon.Update();
MessageBox.Show("未导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
}
catch (Exception ex)
{
progressBarMon.Visible = false;
progressBarMon.Update();
labelProgMon.Text = "";
labelProgMon.Update();
MessageBox.Show(ex.Message.ToString());
}
//*/
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
try
{
Cursor = Cursors.AppStarting;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 13;
excel.Cells[1, 1] = "EMPRESA: ";
excel.Cells[1, 2] = cmbEmpresa.Text;
excel.Cells[2, 1] = "REPORTE:";
excel.Cells[2, 2] = "CONSULTA DE EXISTENCIAS A UNA FECHA DE CORTE:";
excel.Cells[3, 1] = "LOCALIDAD:";
excel.Cells[3, 2] = cmbOficina.Text;
excel.Cells[3, 3] = "BODEGA:";
excel.Cells[3, 4] = cmbBodega.Text;
excel.Cells[5, 1] = "FECHA CORTE";
excel.Cells[5, 2] = txtFechaCorte.Text;
excel.Cells[6, 1] = "FECHA INICIO";
excel.Cells[6, 2] = "";
excel.Cells[7, 1] = "FECHA FIN";
excel.Cells[7, 2] = "";
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
if (iIndiceColumna == 2)
{
excel.Cells[9, iIndiceColumna].ColumnWidth = 20;
}
excel.Cells[9, iIndiceColumna] = col.HeaderText;
excel.Cells[9, iIndiceColumna].Interior.Color = Color.Gray;
excel.Cells[9, iIndiceColumna].BorderAround();
}
int iIndiceFila = 9;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
}
}
excel.get_Range("A9", "E9").BorderAround();
excel.Visible = true;
Cursor = Cursors.Arrow;
}
catch (Exception ex)
{
catchMensaje.lblMensaje.Text = ex.Message;
catchMensaje.ShowDialog();
}
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 40;
excel.Cells[1, 4] = "INFORME DE INGRESOS ";
excel.Cells[2, 4] = "DESDE " + sFechaInicio + " A " + sFechaFin;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
if (iIndiceColumna == 1)
{
excel.Cells[1, iIndiceColumna].ColumnWidth = 0;
}
if (iIndiceColumna == 2 || iIndiceColumna == 6 || iIndiceColumna == 7 || iIndiceColumna == 12 || iIndiceColumna == 13)
{
excel.Cells[1, iIndiceColumna].ColumnWidth = 8;
}
if (iIndiceColumna == 3 || iIndiceColumna == 5 || iIndiceColumna == 9 || iIndiceColumna == 10 || iIndiceColumna == 11)
{
excel.Cells[1, iIndiceColumna].ColumnWidth = 10;
}
if (iIndiceColumna != 1)
{
excel.Cells[4, iIndiceColumna] = col.HeaderText;
excel.Cells[4, iIndiceColumna].Interior.Color = Color.Yellow;
excel.Cells[4, iIndiceColumna].BorderAround();
}
}
int iIndiceFila = 4;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
}
}
excel.get_Range("A4", "M4").BorderAround();
excel.Visible = true;
}
catch (Exception ex)
{
catchMensaje.lblMensaje.Text = ex.ToString();
catchMensaje.ShowDialog();
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
if (!this.rbtnConsumption.Checked && !this.rbtnOriginalGoods.Checked)
{
MessageBox.Show("Please select consumption or original goods to download first.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.dgvConsumption.RowCount == 0 && this.rbtnConsumption.Checked)
{
MessageBox.Show("The consumption data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop);
this.btnDownload.Focus();
return;
}
if (this.dgvOriginalGoods.RowCount == 0 && this.rbtnOriginalGoods.Checked)
{
MessageBox.Show("The original goods data not exist.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Stop);
this.btnDownload.Focus();
return;
}
DialogResult dlgR = MessageBox.Show("Please select a document format:\n[Yes] Generate Excel as new version;\n[No] Generate Excel as old version;\n[Cancel] Reject to handle.", "Prompt", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information);
if (dlgR == DialogResult.Yes)
{
#region //new version
if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
string[] strFiledName = { "成品备件号" };
SqlLib lib = new SqlLib();
DataTable dt = lib.SelectDistinct(middleTable, strFiledName);
lib.Dispose(0);
for (int i = 0; i < dt.Rows.Count; i++)
{
string strEHB = dt.Rows[i][0].ToString().Trim();
DataRow[] datarow = middleTable.Select("[成品备件号] = '" + strEHB + "' AND ([重复备件号] IS NULL OR [重复备件号] = '')");
if (datarow.Length > 0)
{
worksheet.Name = strEHB.Replace("/", "X");
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 10]).NumberFormatLocal = "@";
for (int j = 0; j < datarow.Length; j++)
{
worksheet.Cells[j + 2, 1] = datarow[j][9].ToString().Trim();
worksheet.Cells[j + 2, 2] = datarow[j][0].ToString().Trim();
worksheet.Cells[j + 2, 3] = datarow[j][3].ToString().Trim();
worksheet.Cells[j + 2, 4] = datarow[j][4].ToString().Trim();
worksheet.Cells[j + 2, 5] = datarow[j][5].ToString().Trim();
worksheet.Cells[j + 2, 6] = "0";
worksheet.Cells[j + 2, 7] = datarow[j][2].ToString().Trim();
worksheet.Cells[j + 2, 8] = datarow[j][7].ToString().Trim();
worksheet.Cells[j + 2, 9] = string.Empty;
}
worksheet.Cells[1, 1] = "手册号";
worksheet.Cells[1, 2] = "成品货物备件号";
worksheet.Cells[1, 3] = "净耗数量";
worksheet.Cells[1, 4] = "数量损耗率";
worksheet.Cells[1, 5] = "重量损耗率";
worksheet.Cells[1, 6] = "总耗重量";
worksheet.Cells[1, 7] = "原料货物备件号";
worksheet.Cells[1, 8] = "废料货物备件号";
worksheet.Cells[1, 9] = "备注";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 9]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Name = "Verdana";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[datarow.Length + 1, 9]).Font.Size = 9;
worksheet.Cells.EntireColumn.AutoFit();
if (i < dt.Rows.Count - 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
}
}
}
dt.Clear();
dt.Dispose();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "OriginalGoods";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).NumberFormatLocal = "@";
int iActualRow = 0;
for (int k = 0; k < this.dgvOriginalGoods.Rows.Count; k++)
{
if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, k].Value.ToString().Trim()))
{
iActualRow++;
worksheet.Cells[iActualRow + 1, 1] = this.dgvOriginalGoods[15, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 2] = this.dgvOriginalGoods[2, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 3] = this.dgvOriginalGoods[16, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 4] = "//";
worksheet.Cells[iActualRow + 1, 5] = this.dgvOriginalGoods[17, k].Value.ToString().Trim();
worksheet.Cells[iActualRow + 1, 6] = "1";
}
}
worksheet.Cells[1, 1] = "货主十位数编码";
worksheet.Cells[1, 2] = "原始货物备件号";
worksheet.Cells[1, 3] = "合并货物备件号";
worksheet.Cells[1, 4] = "规格型号";
worksheet.Cells[1, 5] = "仓库号";
worksheet.Cells[1, 6] = "单位净重";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 6]).Font.Bold = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Name = "Verdana";
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.dgvOriginalGoods.Rows.Count + 1, 6]).Font.Size = 9;
worksheet.Cells.EntireColumn.AutoFit();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
#endregion
}
else if (dlgR == DialogResult.No)
{
#region //old version
if (this.rbtnConsumption.Checked && this.dgvConsumption.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvConsumption.RowCount + 1, this.dgvConsumption.ColumnCount - 2]).NumberFormatLocal = "@";
int iActualRow = 0, iLineNo = 0;
string strBatchFg = String.Empty;
for (int i = 0; i < this.dgvConsumption.RowCount; i++)
{
string strBOM1 = this.dgvConsumption[0, i].Value.ToString().Trim();
if (String.Compare(strBOM1, strBatchFg) == 0)
{
iLineNo++;
}
else
{
iLineNo = 1; strBatchFg = strBOM1;
}
string strBOM2 = this.dgvConsumption[8, i].Value.ToString().Trim(); //Mapping 'BOM In Customs'
decimal dConsump = Convert.ToDecimal(this.dgvConsumption[3, i].Value.ToString().Trim()); //Mapping 'Consumption'
if (String.IsNullOrEmpty(strBOM2) && dConsump > 0.0M)
{
iActualRow++;
for (int j = 0; j < this.dgvConsumption.ColumnCount - 2; j++)
{
if (j == 1)
{
excel.Cells[iActualRow + 1, j + 1] = iLineNo;
}
else
{
excel.Cells[iActualRow + 1, j + 1] = this.dgvConsumption[j, i].Value.ToString().Trim();
}
}
}
}
for (int k = 0; k < this.dgvConsumption.ColumnCount - 2; k++)
{
excel.Cells[1, k + 1] = this.dgvConsumption.Columns[k].HeaderText.ToString().Trim();
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvConsumption.ColumnCount - 2]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Name = "Verdana";
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvConsumption.ColumnCount - 2]).Font.Size = 9;
excel.Cells.EntireColumn.AutoFit();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
if (this.rbtnOriginalGoods.Checked && this.dgvOriginalGoods.RowCount > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[this.dgvOriginalGoods.RowCount + 1, this.dgvOriginalGoods.ColumnCount - 5]).NumberFormatLocal = "@"; //Set the excel cells format as text
int iActualRow = 0;
for (int m = 0; m < this.dgvOriginalGoods.RowCount; m++)
{
if (String.IsNullOrEmpty(this.dgvOriginalGoods[14, m].Value.ToString().Trim())) //The same mapping 'BOM In Customs'
{
iActualRow++;
for (int n = 1; n < this.dgvOriginalGoods.ColumnCount - 4; n++)
{
excel.Cells[iActualRow + 1, n + 1] = this.dgvOriginalGoods[n + 1, m].Value.ToString().Trim();
}
}
}
for (int y = 1; y <= iActualRow; y++)
{
excel.Cells[y + 1, 1] = y;
} //Regenerate the first column value, since it is the serial number
for (int x = 1; x < this.dgvOriginalGoods.ColumnCount - 4; x++)
{
excel.Cells[1, x] = this.dgvOriginalGoods.Columns[x].HeaderText.ToString().Trim();
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, this.dgvOriginalGoods.ColumnCount - 5]).AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Name = "Verdana";
excel.get_Range(excel.Cells[1, 1], excel.Cells[iActualRow + 1, this.dgvOriginalGoods.ColumnCount - 5]).Font.Size = 9;
excel.Cells.EntireColumn.AutoFit();
excel.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
#endregion
}
}
private void bar_ExportExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (this.action != "view" || this._assemblySiteDifference.Details == null || this._assemblySiteDifference.Details.Count < 1)
{
return;
}
try
{
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
ConvertMaterial(); //计算原料净重
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Range r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 7]);
r.MergeCells = true;//合并单元格
excel.Cells.ColumnWidth = 10;
excel.Cells[1, 1] = "组装现场盘点差异(" + this.date_Difference.DateTime.ToString("yyyy-MM-dd") + ")";
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).RowHeight = 25;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 20;
//excel.Cells[1, productShipmentList.Count + 1] = DateTime.Now.ToString("yyyy.MM.dd");
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 7]).HorizontalAlignment = -4108;
excel.Cells[2, 1] = "商品编号";
excel.Cells[2, 2] = "商品名称";
excel.Cells[2, 3] = "客户型号";
excel.Cells[2, 4] = "版本";
excel.Cells[2, 5] = "盘点数量";
excel.Cells[2, 6] = "理论数量";
excel.Cells[2, 7] = "差异";
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 7 + 1 + this._assemblySiteDifference.Details[0].Product.MaterialDic.Keys.Count]).Interior.Color = "12566463";
excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 1]).ColumnWidth = 25;
excel.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).ColumnWidth = 50;
int col = 9;
//原料
foreach (var item in this._assemblySiteDifference.Details[0].Product.MaterialDic)
{
excel.Cells[2, col++] = item.Key;
}
List <Model.AssemblySiteDifferenceDetai> haveThreeCategoryPro = this._assemblySiteDifference.Details.Where(P => P.Product.ProductCategory3 != null).ToList();
List <Model.AssemblySiteDifferenceDetai> haveTwoCategoryPro = this._assemblySiteDifference.Details.Where(P => P.Product.ProductCategory2 != null && P.Product.ProductCategory3 == null).ToList();
List <Model.AssemblySiteDifferenceDetai> haveOneCategoryPro = this._assemblySiteDifference.Details.Where(P => P.Product.ProductCategory2 == null && P.Product.ProductCategory3 == null).ToList();
int row = 3;
foreach (var item in haveThreeCategoryPro.GroupBy(P => P.Product.ProductCategory3.ProductCategoryName))
{
SetExcelFormat(excel, ref col, ref row, item);
foreach (var pro in item)
{
excel.Cells[row, 1] = pro.Product.Id;
excel.Cells[row, 2] = pro.Product.ProductName;
excel.Cells[row, 3] = pro.Product.CustomerProductName;
excel.Cells[row, 4] = pro.Product.ProductVersion;
excel.Cells[row, 5] = pro.ActualQuantity;
excel.Cells[row, 6] = pro.TheoryQuantity;
excel.Cells[row, 7] = pro.DiffQty;
col = 9;
foreach (var dic in pro.Product.MaterialDic)
{
excel.Cells[row, col++] = dic.Value;
}
row++;
}
row++;
}
foreach (var item in haveTwoCategoryPro.GroupBy(P => P.Product.ProductCategory2.ProductCategoryName))
{
SetExcelFormat(excel, ref col, ref row, item);
foreach (var pro in item)
{
excel.Cells[row, 1] = pro.Product.Id;
excel.Cells[row, 2] = pro.Product.ProductName;
excel.Cells[row, 3] = pro.Product.CustomerProductName;
excel.Cells[row, 4] = pro.Product.ProductVersion;
excel.Cells[row, 5] = pro.ActualQuantity;
excel.Cells[row, 6] = pro.TheoryQuantity;
excel.Cells[row, 7] = pro.DiffQty;
col = 9;
foreach (var dic in pro.Product.MaterialDic)
{
excel.Cells[row, col++] = dic.Value;
}
row++;
}
row++;
}
foreach (var item in haveOneCategoryPro.GroupBy(P => P.Product.ProductCategory.ProductCategoryName))
{
SetExcelFormat(excel, ref col, ref row, item);
foreach (var pro in item)
{
excel.Cells[row, 1] = pro.Product.Id;
excel.Cells[row, 2] = pro.Product.ProductName;
excel.Cells[row, 3] = pro.Product.CustomerProductName;
excel.Cells[row, 4] = pro.Product.ProductVersion;
excel.Cells[row, 5] = pro.ActualQuantity;
excel.Cells[row, 6] = pro.TheoryQuantity;
excel.Cells[row, 7] = pro.DiffQty;
col = 9;
foreach (var dic in pro.Product.MaterialDic)
{
excel.Cells[row, col++] = dic.Value;
}
row++;
}
row++;
}
excel.Visible = true;//是否打开该Excel文件
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
}
catch
{
MessageBox.Show("Excel未生成完畢，請勿操作，并重新點擊按鈕生成數據！", "提示！", MessageBoxButtons.OK);
return;
}
}
private void btn2_Click(object sender, System.EventArgs e)
{
Microsoft.Office.Interop.Excel.Range Rng;
fileExcel = @"C:\Users\hp\source\repos\WpfApp9\WpfApp9\obj\Debug\SK_Moschnost.xlsx";
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
// open workbook
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(fileExcel, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];
Microsoft.Office.Interop.Excel.Chart xlChart;
Microsoft.Office.Interop.Excel.Series xlSeries;
xlApp.Visible = true;
xlApp.UserControl = true;
Microsoft.Office.Interop.Excel.Range usedColumn = xlSheet.UsedRange.Columns[2];
System.Array myvalues = (System.Array)usedColumn.Cells.Value2;
string[] strArray = myvalues.OfType <object>().Select(o => o.ToString()).ToArray();
for (int i = 1; i < strArray.Length; i++)
{
data.Add(Convert.ToDouble(strArray[i]));
}
for (double g = gamin; g < gamax; g += 1000)
{
for (double T = 0; T <= tf - t0; T += 0.01)
{
for (double t = t0; t <= tf; t += 0.01)
{
function(t, T, g);
}
}
}
gamma();
minmax();
timemax();
taumin2();
gmin22();
String sMsg;
sMsg = "Minimum of maximum |f(gamma,tau)|: ";
sMsg = String.Concat(sMsg, minmax());
sMsg = String.Concat(sMsg, " Вт, when minimum of gamma: ");
sMsg = String.Concat(sMsg, gmin22());
sMsg = String.Concat(sMsg, ", minimum of tau: ");
sMsg = String.Concat(sMsg, taumin2());
sMsg = String.Concat(sMsg, ", maximum of time: ");
sMsg = String.Concat(sMsg, timemax());
MessageBoxResult mes = MessageBox.Show(sMsg, "Caculate and draw graphic?", MessageBoxButton.YesNo);
if (mes == MessageBoxResult.No)
{
Close();
}
else
{
//Add table headers going cell by cell.
xlSheet.Cells[1, 4] = "Время [t0,tf], сек.";
xlSheet.Cells[1, 5] = "|f(gamma,tau)|";
//AutoFit columns A:B.
Rng = xlSheet.get_Range("A1:G1");
Rng.EntireColumn.AutoFit();
// interval [t0, tf]
Rng = xlApp.get_Range("D2", "D1002");
Rng.Formula = "=A101";
StreamWriter txt = new StreamWriter("testinte2.txt");
for (double t = t0; t < tf; t += 0.01)
{
txt.WriteLine(Math.Abs(gmin22() - data[Convert.ToInt32(t * 100)] - data[Convert.ToInt32((t + taumin2()) * 100)]));
}
txt.Close();
object misvalue = System.Reflection.Missing.Value;
string[] txtname = System.IO.File.ReadAllLines(@"C:\Users\hp\source\repos\WpfApp9\WpfApp9\bin\Debug\testinte2.txt");
try
{
for (int i = 0; i <= txtname.Length; i++)
{
xlSheet.Cells[5][i + 2] = txtname[i];
}
Thread.Sleep(3000);
}
catch (Exception ex)
{
Console.WriteLine("Exception" + ex);
}
// add a chart for the selected data
xlWorkBook = (Microsoft.Office.Interop.Excel.Workbook)xlSheet.Parent;
xlChart = (Microsoft.Office.Interop.Excel.Chart)xlWorkBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// use the ChartWizard to create a new chart from the select data
xlSeries = (Microsoft.Office.Interop.Excel.Series)xlChart.SeriesCollection(1);
xlSeries.XValues = xlSheet.get_Range("E2:E1002");
}
}
//masih belum
public void printLaporanPenjualan(String filename, String periode)
{
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Report";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 6]].Merge();
worKsheeT.Cells[1, 1] = "Laporan Penjualan";
worKsheeT.Cells.Font.Size = 15;
worKsheeT.Cells[2, 1] = "Tanggal";
worKsheeT.Cells[2, 2] = "NoNota";
worKsheeT.Cells[2, 3] = "Nama Barang";
worKsheeT.Cells[2, 4] = "Jumlah";
worKsheeT.Cells[2, 5] = "Harga";
worKsheeT.Cells[2, 6] = "Total";
//worKsheeT.Cells[2, 5] = "Kategori";
Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Koneksi k = new Koneksi();
k.Connect();
int baris = 3;
String q = "SELECT DATE_FORMAT(Tanggal,'%d-%M-%Y') as Tanggal,b.Nama,nd.Jumlah,nd.Harga,(nd.Harga*nd.Jumlah) as Subtotal,nb.NoNota " +
"FROM notajual nb " +
"INNER JOIN notajualdetil nd ON (nb.NoNota=nd.NoNota) " +
"INNER JOIN barang b ON (b.KodeBarang=nd.KodeBarang) " +
"WHERE DATE_FORMAT(Tanggal,'%Y-%M')=?periode";
MySqlCommand c = new MySqlCommand(q, k.KoneksiDB);
c.Parameters.Add(new MySqlParameter("periode", periode));
MySqlDataReader rd = c.ExecuteReader();
int total = 0;
while (rd.Read())
{
worKsheeT.Cells[baris, 1] = rd["Tanggal"];
worKsheeT.Cells[baris, 2] = rd["NoNota"];
worKsheeT.Cells[baris, 3] = rd["Nama"];
worKsheeT.Cells[baris, 4] = rd["Jumlah"];
worKsheeT.Cells[baris, 5] = rd["Harga"];
worKsheeT.Cells[baris, 6] = rd["Subtotal"];
total = total + Int32.Parse(rd["Subtotal"] + "");
baris = baris + 1;
}
worKsheeT.Cells[baris, 6] = total;
Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("E3:F" + baris);
angkaStyles.NumberFormat = "##,#";
Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:F20");
Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:F2");
Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:F" + baris);
rangeStyles2.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles.Value2 = "'Style Test";
//rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles3.BorderAround2();
rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
k.KoneksiDB.Close();
worKbooK.SaveAs(filename);
worKbooK.Close();
excel.Quit();
ProcessStartInfo info = new ProcessStartInfo();
info.Verb = "print";
info.FileName = @filename;
info.CreateNoWindow = true;
info.WindowStyle = ProcessWindowStyle.Hidden;
Process p = new Process();
p.StartInfo = info;
p.Start();
p.WaitForInputIdle();
System.Threading.Thread.Sleep(3000);
//if (false == p.CloseMainWindow())
//p.Kill();
}
catch (Exception ex)
{
String msg = ex.Message;
String abc = "aa";
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
//Función para exportar a excel
private void exportarAExcel(DataGridView dgvCierre)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int iIndiceColumna = 0;
excel.Columns.ColumnWidth = 10;
excel.Cells[1, 1] = "EMPRESA: ";
excel.Cells[1, 2] = "COSA NOSTRA";
excel.Cells[2, 1] = "REPORTE:";
excel.Cells[2, 2] = "KARDEX POR EMPRESA";
excel.Cells[3, 1] = "PROD. INICIAL:";
excel.Cells[3, 2] = dbAyudaArticuloInicial.txtIdentificacion.Text;
excel.Cells[4, 1] = "PROD. FINAL:";
excel.Cells[4, 2] = dbAyudaArticuloFinal.txtIdentificacion.Text;
excel.Cells[5, 1] = "FECHA INICIO";
excel.Cells[5, 2] = txtFechaDesde.Text;
excel.Cells[5, 4] = "FECHA FIN";
excel.Cells[5, 5] = txtFechaHasta.Text;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
if (iIndiceColumna == 2 || iIndiceColumna == 3)
{
excel.Cells[8, iIndiceColumna].ColumnWidth = 15;
}
excel.Cells[8, iIndiceColumna] = col.HeaderText;
excel.Cells[8, iIndiceColumna].Interior.Color = Color.Yellow;
excel.Cells[8, iIndiceColumna].BorderAround();
}
int iIndiceFila = 9;
foreach (DataGridViewRow row in dgvCierre.Rows)
{
iIndiceFila++;
iIndiceColumna = 0;
foreach (DataGridViewColumn col in dgvCierre.Columns)
{
iIndiceColumna++;
excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
}
}
excel.get_Range("A8", "T8").BorderAround();
excel.Visible = true;
}
catch (Exception ex)
{
catchMensaje.lblMensaje.Text = ex.ToString();
catchMensaje.ShowDialog();
}
}
private void backgroundWorkerYearExl_DoWork(object sender, DoWorkEventArgs e)
{
try
{
progressBarYear.Value = 10;
progressBarYear.Update();
labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%";
labelProgYear.Update();
string yr_str = comboBoxYear3.Text.Substring(2, 2);
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;//一个xls文档 new Microsoft.Office.Interop.Excel.Workbooks();
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook
Microsoft.Office.Interop.Excel.Worksheet ws;//excel中的一个sheet
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"];
ws.Name = yr_str + "年总合计";
#region 年总计
Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 11]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[4, 1]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 2], excel.Cells[4, 2]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 3], excel.Cells[4, 3]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 10], excel.Cells[4, 10]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 11], excel.Cells[4, 11]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 4], excel.Cells[3, 6]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 7], excel.Cells[3, 9]);
merge_range.Merge(Type.Missing);
excel.Cells[1, 1] = comboBoxYear3.Text +"年垃圾产量总计表";
Microsoft.Office.Interop.Excel.Range bold_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2,11]);
bold_range.Font.Size = 20;
bold_range.Font.Bold = true;
bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
bold_range.EntireColumn.AutoFit(); //自动调整列宽
bold_range.EntireRow.AutoFit();
excel.Cells[3, 1] = "月份";
excel.Cells[3, 2] = "月产箱数";
excel.Cells[3, 3] = "实际吨数";
excel.Cells[3, 4] = "自运箱数";
excel.Cells[3, 5] = "实际吨数";
excel.Cells[3, 10] = "合计箱数";
excel.Cells[3, 11] = "合计吨数";
excel.Cells[4, 4] = "西清";
excel.Cells[4, 5] = "十队";
excel.Cells[4, 6] = "合计";
excel.Cells[4, 7] = "西清";
excel.Cells[4, 8] = "十队";
excel.Cells[4, 9] = "合计";
//填充数据
for (int x = 0; x < dataGridViewMon.RowCount; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x + 5, y + 1] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x + 5, y + 1] = "";
}
}
Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[17, 11]);//现有的
all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
all_range.EntireColumn.AutoFit(); //自动调整列宽
all_range.EntireRow.AutoFit();
all_range.Borders.LineStyle = 1;
all_range.Font.Size = 12;
#endregion
progressBarYear.Value = 20;
progressBarYear.Update();
labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%";
labelProgYear.Update();
dataGridViewMon.DataSource = crform_ds.Tables["MonOutPut"];
for (int cur_sheet = 1; cur_sheet <= 12; cur_sheet++)
{
progressBarYear.Value = Convert.ToInt32(20 + 6.5*cur_sheet);
progressBarYear.Update();
labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%";
labelProgYear.Update();
ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;
ws.Name = yr_str + "年" + cur_sheet.ToString()+"月";
merge_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 11]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[4, 1]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 2], excel.Cells[4, 2]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 3], excel.Cells[4, 3]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 10], excel.Cells[4, 10]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 11], excel.Cells[4, 11]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 4], excel.Cells[3, 6]);
merge_range.Merge(Type.Missing);
merge_range = excel.get_Range(excel.Cells[3, 7], excel.Cells[3, 9]);
merge_range.Merge(Type.Missing);
excel.Cells[1, 1] = comboBoxYear3.Text + "年"+cur_sheet.ToString()+"月垃圾产量明细表";
bold_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[2, 11]);
bold_range.Font.Size = 20;
bold_range.Font.Bold = true;
bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
bold_range.EntireColumn.AutoFit(); //自动调整列宽
bold_range.EntireRow.AutoFit();
excel.Cells[3, 1] = "日期";
excel.Cells[3, 2] = "日产箱数";
excel.Cells[3, 3] = "实际吨数";
excel.Cells[3, 4] = "自运箱数";
excel.Cells[3, 5] = "实际吨数";
excel.Cells[3, 10] = "合计箱数";
excel.Cells[3, 11] = "合计吨数";
excel.Cells[4, 4] = "西清";
excel.Cells[4, 5] = "十队";
excel.Cells[4, 6] = "合计";
excel.Cells[4, 7] = "西清";
excel.Cells[4, 8] = "十队";
excel.Cells[4, 9] = "合计";
//填充数据
for (int x = (cur_sheet - 1) * 32; x < (cur_sheet - 1) * 32+32; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null && y != 3 )
{
excel.Cells[x - (cur_sheet - 1) * 32 + 5, y + 1] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x - (cur_sheet - 1) * 32 + 5, y + 1] = "";
}
}
all_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[36, 11]);//现有的
all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
all_range.EntireColumn.AutoFit(); //自动调整列宽
all_range.EntireRow.AutoFit();
all_range.Borders.LineStyle = 1;
all_range.Font.Size = 12;
}//end for每个sheet
labelProgYear.Text = "完成 " + progressBarYear.Value.ToString() + "%";
labelProgYear.Update();
progressBarYear.Value = progressBarYear.Maximum;
progressBarYear.Update();
wb.Saved = true;
wb.SaveCopyAs(fName); //保存
excel.Quit(); //关闭进程
labelProgYear.Text = "已完成";
labelProgYear.Update();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
//label5.Text = "完成";
toolStripButton6.Enabled = false;
this.Enabled = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
progressBarYear.Visible = false;
progressBarYear.Update();
labelProgYear.Text = "";
labelProgYear.Update();
this.Enabled = true;
}
this.Enabled = true;
}
/// <summary>根据参数创建excel表</summary>
public void CreateExcel(System.Data.DataTable dt, string pathname, string[] str)
{
int eRowIndex = 2;
int eColIndex = 1;
int cols = dt.Columns.Count;
int rows = dt.Rows.Count;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets["sheet1"];
//xlSheet.PageSetup.CenterHorizontally = false;//页面水平居中//xlSheet.PageSetup.CenterVertically = false;//页面不垂直居中
xlApp.Cells[1, cols] = str[0];
xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).Font.Bold = true;
xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).Font.Size = str[1];
xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).MergeCells = true;
xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[1, cols]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
try
{
//列名的处理
for (int i = 0; i < cols; i++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName;
eColIndex++;
}
//列内容样式
xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, cols]).Font.Bold = true;
xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, cols]).Font.Name = str[3];
xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, cols]).Font.Size = str[2];
//xlApp.get_Range((object)xlApp.Cells[eRowIndex, 1], (object)xlApp.Cells[eRowIndex, 1]).ColumnWidth = 20;//设置列宽
eRowIndex++;
for (int i = 0; i < rows; i++)
{
eColIndex = 1;
for (int j = 0; j < cols; j++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString();
eColIndex++;
}
eRowIndex++;
}
Microsoft.Office.Interop.Excel.Range range1 = (Microsoft.Office.Interop.Excel.Range)xlApp.get_Range((object)xlApp.Cells[1, 1], (object)xlApp.Cells[eRowIndex - 1, cols]);
range1.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range1.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//xlApp.Visible = true;
xlApp.Cells[eRowIndex + 1, 1] = "附加信息：";
xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true;
xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true;
xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).RowHeight = str[4];
xlApp.Cells[eRowIndex + 2, 1] = "确认签名：";
xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true;
xlApp.get_Range((object)xlApp.Cells[eRowIndex + 1, 1], (object)xlApp.Cells[eRowIndex + 1, cols]).MergeCells = true;
//行、列自适应
xlApp.Cells.EntireColumn.AutoFit(); //xlApp.Cells.EntireRow.AutoFit(); //xlApp.DisplayAlerts = true; //xlBook.SaveCopyAs(path);
xlSheet.SaveAs(pathname);
xlApp.Workbooks.Close();
}
catch (Exception e)
{
Response.Write("<script>alert('0000x1：'" + e.Message + ")</script>");
}
finally
{
xlApp.Quit();
GC.Collect(); //杀掉Excel进程。
}
}
private void timerMon5_Tick(object sender, EventArgs e)
{
if (flag_everydayexl == true)
{
this.dateTimePicker6.Enabled = false;
timerMon5.Stop();
dataGridViewMon.DataSource = crform_ds.Tables["Result_t0"];
//MessageBox.Show(dataGridView1[0, 0].Value.ToString());
//MessageBox.Show(dataGridView1[0, 1].Value.ToString());
//MessageBox.Show(dataGridView1[1, 0].Value.ToString());
//MessageBox.Show(dataGridView1[1, 1].Value.ToString());
//ExportExcel(dataGridView1);
try
{
if (dataGridViewMon.Rows.Count >= 1)
{
this.Enabled = false;
//MessageBox.Show("F**K@@");
progressBarDay.Visible = true;
progressBarDay.Value = 0;
progressBarDay.Update();
labelProgDay.Text = "开始导出";
labelProgDay.Update();
string yr_str = comboBoxYear2.Text.Trim();
string mon_str = comboBoxMon2.Text.Trim();
string day_str = comboBoxDay2.Text.Trim();
string fName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.InitialDirectory = "d:";
saveFileDialog.Filter = "EXCEL文件|*.xlsx";
saveFileDialog.FilterIndex = 2;
saveFileDialog.FileName = mon_str + "月" + day_str + "日完成情况";
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
fName = saveFileDialog.FileName;
progressBarDay.Value = 12;
progressBarDay.Update();
this.Refresh();
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;//一个xls文档 new Microsoft.Office.Interop.Excel.Workbooks();
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook
Microsoft.Office.Interop.Excel.Worksheet ws;//excel中的一个sheet
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"];
//int num_sheet = dgv.Rows.Count / 55;
//label5.Text = "完成 " + progressBar1.Value.ToString() + "%";
//label5.Update();
//for (int cur_sheet = 1; cur_sheet <= num_sheet; cur_sheet++)
//{
// if (num_sheet != 1)
// {
// progressBar1.Value = Convert.ToInt32(12 + cur_sheet * 87 / num_sheet);
// progressBar1.Update();
//label5.Text = "完成 " + progressBar1.Value.ToString() + "%";
//label5.Update();
// }
// else
// {
// progressBar1.Value = 50;
// progressBar1.Update();
//label5.Text = "完成 " + progressBar1.Value.ToString() + "%";
//label5.Update();
// }
// if (cur_sheet == 1)
// ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"];
// else
// ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;
// ws.Name = cur_sheet.ToString();
excel.Cells[2, 1] = "一班：王玉宝";
excel.Cells[2, 6] = "二班：蔡震";
excel.Cells[2, 11] = "三班：韩培元";
excel.Cells[2, 13] = "日期：" + mon_str + "月" + day_str + "日";
Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[2, 13], excel.Cells[2, 14]);
merge_range.Merge(Type.Missing);
Microsoft.Office.Interop.Excel.Range bold_range = excel.get_Range(excel.Cells[2, 1], excel.Cells[2, 15]);
bold_range.Font.Bold = true;
//生成字段名称
excel.Cells[3, 1] = "楼名";
excel.Cells[3, 2] = "日产箱数";
excel.Cells[3, 3] = "实际吨数";
excel.Cells[3, 4] = "平均吨数";
excel.Cells[3, 6] = "楼名";
excel.Cells[3, 7] = "日产箱数";
excel.Cells[3, 8] = "实际吨数";
excel.Cells[3, 9] = "平均吨数";
excel.Cells[3, 11] = "楼名";
excel.Cells[3, 12] = "日产箱数";
excel.Cells[3, 13] = "实际吨数";
excel.Cells[3, 14] = "平均吨数";
//填充数据
for (int x = 0; x < 15; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x + 4, y + 1] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x + 4, y + 1] = "";
}
}
for (int x = 18; x < 40; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x - 18 + 4, y + 1 + 5] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x - 18 + 4, y + 1 + 5] = "";
}
}
for (int x = 43; x < 59; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x - 43 + 4, y + 1 + 10] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x - 43 + 4, y + 1 + 10] = "";
}
}
for (int x = 15; x < 18; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x + 13, y + 1] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x + 13, y + 1] = "";
}
}
for (int x = 40; x < 43; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x - 12, y + 1 + 5] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x - 12, y + 1 + 5] = "";
}
}
for (int x = 59; x < 62; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x - 31, y + 1 + 10] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x - 31, y + 1 + 10] = "";
}
}
Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[30, 14]);//现有的
//all_range.NumberFormatLocal = "0.00_";
all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
all_range.EntireColumn.AutoFit(); //自动调整列宽
all_range.EntireRow.AutoFit();
Microsoft.Office.Interop.Excel.Range t1_range = excel.get_Range(excel.Cells[3, 1], excel.Cells[30, 4]);
t1_range.Borders.LineStyle = 1;
Microsoft.Office.Interop.Excel.Range t2_range = excel.get_Range(excel.Cells[3, 6], excel.Cells[30, 9]);
t2_range.Borders.LineStyle = 1;
Microsoft.Office.Interop.Excel.Range t3_range = excel.get_Range(excel.Cells[3, 11], excel.Cells[30, 14]);
t3_range.Borders.LineStyle = 1;
labelProgDay.Text = "完成 " + progressBarDay.Value.ToString() + "%";
labelProgDay.Update();
//}//end for每个datagridview,也就是每个table_team
//}//end for每个sheet
progressBarDay.Value = progressBarDay.Maximum;
progressBarDay.Update();
wb.Saved = true;
wb.SaveCopyAs(fName); //保存
excel.Quit(); //关闭进程
labelProgDay.Text = "已完成";
labelProgDay.Update();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
//label5.Text = "完成";
toolStripButtonDayCompExl.Enabled = false;
this.Enabled = true;
}
else
{
progressBarDay.Visible = false;
progressBarDay.Update();
labelProgDay.Text = "";
labelProgDay.Update();
MessageBox.Show("未导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
progressBarDay.Visible = false;
progressBarDay.Update();
labelProgDay.Text = "";
labelProgDay.Update();
this.Enabled = true;
}
this.Enabled = true;
this.dateTimePicker6.Enabled = true;
flag_everydayexl = false;
}
}
//基本对比函数，新旧报表左侧标题必须一样
private void DoCompare(Microsoft.Office.Interop.Excel.Application excelRes, Microsoft.Office.Interop.Excel.Application excelOld, Microsoft.Office.Interop.Excel.Application excelNew, int iStartRowID, int iStartColID)
{
Microsoft.Office.Interop.Excel.Worksheet sheetNew = excelNew.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Worksheet sheetOld = excelOld.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
int iNullRowCnt = 0;
int iNullColCnt = 0;
object MissingValue = System.Type.Missing;
for (int i = iStartRowID; i <= sheetNew.UsedRange.Rows.Count; i++)
{
object objTmp = ((Microsoft.Office.Interop.Excel.Range)sheetOld.UsedRange.Cells[i, 1]).Text;
string strTmp = "";// excelNew.Cells[i, 1].ToString();
if (objTmp != null)
{
strTmp = objTmp.ToString();
}
if (strTmp == "")
{
iNullRowCnt++;
if (iNullRowCnt > 50)
{
break;
}
}
else
{
iNullRowCnt = 0;
}
bool isSame = true;
if (iStartColID > 1)
{
string strLast = "";
object objLast = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[i, iStartColID - 1]).Text;
if (objLast != null)
{
strLast = objLast.ToString();
}
for (int j = 1; j < iStartColID; j++)
{
object objOld = ((Microsoft.Office.Interop.Excel.Range)sheetOld.UsedRange.Cells[i, j]).Text;
object objNew = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[i, j]).Text;
string strOld = "";
string strNew = "";
if (objOld != null)
{
strOld = objOld.ToString();
}
if (objNew != null)
{
strNew = objNew.ToString();
}
if (strOld.Trim() != strNew.Trim())
{
isSame = false;
}
else
{
excelRes.Cells[i, j] = strOld;
if (strOld == "" && i > 1 && strLast != "")
{
excelRes.get_Range(excelRes.Cells[i - 1, j], excelRes.Cells[i, j]).Merge(MissingValue);
}
}
}
if (isSame)
{
for (int j = iStartColID; j <= sheetNew.UsedRange.Columns.Count; j++)
{
object objTmpOld = ((Microsoft.Office.Interop.Excel.Range)sheetOld.UsedRange.Cells[i, j]).Text;
object objTmpNew = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[i, j]).Text;
object objTmp1 = ((Microsoft.Office.Interop.Excel.Range)sheetNew.UsedRange.Cells[iStartRowID - 1, j]).Text;
string strTmpOld = "";;
string strTmpNew = "";
string strTmp1 = "";
if (objTmpOld != null)
{
strTmpOld = objTmpOld.ToString();
}
if (objTmpNew != null)
{
strTmpNew = objTmpNew.ToString();
}
if (objTmp1 != null)
{
strTmp1 = objTmp1.ToString();
}
if (strTmp1 == "" && strTmpOld == "" && strTmpNew == "")
{
iNullColCnt++;
if (iNullColCnt > 50)
{
break;
}
}
else
{
iNullColCnt = 0;
}
string strRes = "";
try
{
double dOld = 0;
if (strTmpOld != "")
{
dOld = double.Parse(strTmpOld);
}
double dNew = 0;
if (strTmpNew != "")
{
dNew = double.Parse(strTmpNew);
}
double dRes = dNew - dOld;
strRes = dRes.ToString("f2");
if (dRes > 0)
{
excelRes.Cells[i, j] = "'+" + strRes;
}
else if (dRes < 0)
{
excelRes.Cells[i, j] = "'" + strRes;
}
}
catch
{ }
}
}
}
}
}
private void backgroundWorkerMonCheCiExl_DoWork(object sender, DoWorkEventArgs e)
{
try
{
progressBarMonCheCi.Value = 60;
progressBarMonCheCi.Update();
labelProgMonCheCi.Text = "正在处理...";
labelProgMonCheCi.Update();
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;//一个xls文档 new Microsoft.Office.Interop.Excel.Workbooks();
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(true);// new Microsoft.Office.Interop.Excel.Workbook
Microsoft.Office.Interop.Excel.Worksheet ws;//excel中的一个sheet
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["Sheet1"];
Microsoft.Office.Interop.Excel.Range merge_range = excel.get_Range(excel.Cells[1, 2], excel.Cells[1, 33]);
merge_range.Merge(Type.Missing);
excel.Cells[1, 1] = comboBox9.Text;
excel.Cells[1, 2] = comboBoxYear4.Text + "年" + comboBoxMon4.Text + "月份完成任务情况统计";
Microsoft.Office.Interop.Excel.Range bold_range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]);
bold_range.Font.Size = 16;
bold_range.Font.Bold = true;
bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
bold_range.EntireColumn.AutoFit(); //自动调整列宽
bold_range.EntireRow.AutoFit();
bold_range = excel.get_Range(excel.Cells[1, 2], excel.Cells[1, 33]);
bold_range.Font.Size = 20;
bold_range.Font.Bold = true;
bold_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
bold_range.EntireColumn.AutoFit(); //自动调整列宽
bold_range.EntireRow.AutoFit();
excel.Cells[2, 1] = "站名";
excel.Cells[2, 2] = "1";
excel.Cells[2, 3] = "2";
excel.Cells[2, 4] = "3";
excel.Cells[2, 5] = "4";
excel.Cells[2, 6] = "5";
excel.Cells[2, 7] = "6";
excel.Cells[2, 8] = "7";
excel.Cells[2, 9] = "8";
excel.Cells[2, 10] = "9";
excel.Cells[2, 11] = "10";
excel.Cells[2, 12] = "11";
excel.Cells[2, 13] = "12";
excel.Cells[2, 14] = "13";
excel.Cells[2, 15] = "14";
excel.Cells[2, 16] = "15";
excel.Cells[2, 17] = "16";
excel.Cells[2, 18] = "17";
excel.Cells[2, 19] = "18";
excel.Cells[2, 20] = "19";
excel.Cells[2, 21] = "20";
excel.Cells[2, 22] = "21";
excel.Cells[2, 23] = "22";
excel.Cells[2, 24] = "23";
excel.Cells[2, 25] = "24";
excel.Cells[2, 26] = "25";
excel.Cells[2, 27] = "26";
excel.Cells[2, 28] = "27";
excel.Cells[2, 29] = "28";
excel.Cells[2, 30] = "29";
excel.Cells[2, 31] = "30";
excel.Cells[2, 32] = "31";
excel.Cells[2, 33] = "合计";
excel.Cells[2, 34] = "实际";
excel.Cells[2, 35] = "差额";
//填充数据
for (int x = 0; x < dataGridViewMon.RowCount; x++)
{
for (int y = 0; y < dataGridViewMon.ColumnCount; y++)
{
if (dataGridViewMon[y, x].Value != null)
{
//MessageBox.Show(dataGridView1[y, x].Value.ToString());
excel.Cells[x + 3, y + 1] = dataGridViewMon[y, x].Value;
}
else
excel.Cells[x + 3, y + 1] = "";
}
}
Microsoft.Office.Interop.Excel.Range all_range = excel.get_Range(excel.Cells[2, 1], excel.Cells[1 + dataGridViewMon.RowCount, 35]);//现有的
all_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
all_range.EntireColumn.AutoFit(); //自动调整列宽
all_range.EntireRow.AutoFit();
all_range.Borders.LineStyle = 1;
all_range.Font.Size = 12;
progressBarMonCheCi.Value =100;
progressBarMonCheCi.Update();
labelProgMonCheCi.Text = "正在处理...";
labelProgMonCheCi.Update();
wb.Saved = true;
wb.SaveCopyAs(fName); //保存
excel.Quit(); //关闭进程
labelProgMonCheCi.Text = "已完成";
labelProgMonCheCi.Update();
MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
toolStripButtonMonCheCiExl.Enabled = false;
this.Enabled = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
progressBarMonCheCi.Visible = false;
progressBarMonCheCi.Update();
labelProgMonCheCi.Text = "";
labelProgMonCheCi.Update();
this.Enabled = true;
}
this.Enabled = true;
}
private void btn_OK_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(CustomerIds))
{
MessageBox.Show("客戶不能為空", "提示", MessageBoxButtons.OK);
return;
}
if (this.date_Start.EditValue == null || this.date_End.EditValue == null)
{
MessageBox.Show("日期區間不能為空", "提示", MessageBoxButtons.OK);
return;
}
DataTable dt = invoiceXODetailManager.ShipmentTable(CustomerIds, date_Start.DateTime, date_End.DateTime);
//導出Excel
try
{
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("無數據", "提示", MessageBoxButtons.OK);
return;
}
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
sheet.Cells.ColumnWidth = 15;
sheet.Cells.RowHeight = 20;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 3]).ColumnWidth = 20;
excel.get_Range(excel.Cells[1, 8], excel.Cells[1, 11]).ColumnWidth = 9;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1 + dt.Rows.Count, 13]).Borders.LineStyle = 1;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1 + dt.Rows.Count, 13]).HorizontalAlignment = -4108;
excel.Cells[1, 1] = "出货客戶名稱";
excel.Cells[1, 2] = "訂單號碼";
excel.Cells[1, 3] = "產品型號";
excel.Cells[1, 4] = "數量";
//excel.Cells[1, 5] = "單位PCS/PRS";
excel.Cells[1, 5] = "單位";
excel.Cells[1, 6] = "訂單交貨日";
excel.Cells[1, 7] = "盒裝/箱裝";
excel.Cells[1, 8] = "淨重KG";
excel.Cells[1, 9] = "毛重KG";
excel.Cells[1, 10] = "才積";
excel.Cells[1, 11] = "箱數";
excel.Cells[1, 12] = "外箱編號";
excel.Cells[1, 13] = "生產站";
int row = 2;
double startNumber = 0;
excel.get_Range(excel.Cells[2, 2], excel.Cells[1 + dt.Rows.Count, 2]).NumberFormatLocal = "@";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i > 0 && dt.Rows[i]["CustomerInvoiceXOId"].ToString() != dt.Rows[i - 1]["CustomerInvoiceXOId"].ToString()) //新的訂單編號，箱號重新開始排
{
startNumber = 0;
}
double boxCount = Math.Ceiling(Convert.ToDouble(dt.Rows[i]["BoxCount"]));
dt.Rows[i]["BoxNumber"] = string.Format("{0} ~ {1}", startNumber + 1, startNumber + boxCount);
startNumber += boxCount;
excel.Cells[row, 1] = dt.Rows[i]["CustomerFullName"] == null ? "" : dt.Rows[i]["CustomerFullName"].ToString();
excel.Cells[row, 2] = dt.Rows[i]["CustomerInvoiceXOId"] == null ? "" : dt.Rows[i]["CustomerInvoiceXOId"].ToString();
excel.Cells[row, 3] = dt.Rows[i]["CustomerProductName"] == null ? "" : dt.Rows[i]["CustomerProductName"].ToString();
excel.Cells[row, 4] = dt.Rows[i]["InvoiceXODetailQuantity"] == null ? "" : dt.Rows[i]["InvoiceXODetailQuantity"].ToString();
excel.Cells[row, 5] = dt.Rows[i]["SellUnit"] == null ? "" : dt.Rows[i]["SellUnit"].ToString();
excel.Cells[row, 6] = dt.Rows[i]["InvoiceYjrq"] == null ? "" : dt.Rows[i]["InvoiceYjrq"].ToString();
excel.Cells[row, 7] = dt.Rows[i]["Guige"] == null ? "" : dt.Rows[i]["Guige"].ToString();
excel.Cells[row, 8] = dt.Rows[i]["NetWeight"] == null ? "" : dt.Rows[i]["NetWeight"].ToString();
excel.Cells[row, 9] = dt.Rows[i]["GrossWeight"] == null ? "" : dt.Rows[i]["GrossWeight"].ToString();
excel.Cells[row, 10] = dt.Rows[i]["Volume"] == null ? "" : dt.Rows[i]["Volume"].ToString();
excel.Cells[row, 11] = dt.Rows[i]["BoxCount"] == null ? "" : dt.Rows[i]["BoxCount"].ToString();
excel.Cells[row, 12] = dt.Rows[i]["BoxNumber"] == null ? "" : dt.Rows[i]["BoxNumber"].ToString();
excel.Cells[row, 13] = dt.Rows[i]["Workhousename"] == null ? "" : dt.Rows[i]["Workhousename"].ToString();
row++;
}
excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
excel.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("Excel未生成完畢，請勿操作，并重新點擊按鈕生成數據！", "提示！", MessageBoxButtons.OK);
return;
}
}
public void printPegawai(String filename)
{
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Report Pegawai";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 4]].Merge();
worKsheeT.Cells[1, 1] = "Laporan Pegawai";
worKsheeT.Cells.Font.Size = 15;
worKsheeT.Cells[2, 1] = "Kode Pegawai";
worKsheeT.Cells[2, 2] = "Nama";
worKsheeT.Cells[2, 3] = "Tanggal Lahir";
worKsheeT.Cells[2, 4] = "Alamat";
worKsheeT.Cells[2, 5] = "Gaji";
worKsheeT.Cells[2, 6] = "Username";
worKsheeT.Cells[2, 7] = "Password";
worKsheeT.Cells[2, 8] = "IdJabatan";
//worKsheeT.Cells[2, 5] = "Kategori";
Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Koneksi k = new Koneksi();
k.Connect();
int baris = 3;
String q = "SELECT * FROM Pegawai";
MySqlCommand c = new MySqlCommand(q, k.KoneksiDB);
MySqlDataReader rd = c.ExecuteReader();
while (rd.Read())
{
worKsheeT.Cells[baris, 1] = rd["KodePegawai"];
worKsheeT.Cells[baris, 2] = rd["Nama"];
worKsheeT.Cells[baris, 3] = rd["TglLahir"];
worKsheeT.Cells[baris, 4] = rd["Alamat"];
worKsheeT.Cells[baris, 5] = rd["Gaji"];
worKsheeT.Cells[baris, 6] = rd["Username"];
worKsheeT.Cells[baris, 7] = rd["Password"];
worKsheeT.Cells[baris, 8] = rd["IdJabatan"];
/*if (worKsheeT.Cells[baris, 8] = "J1")
* {
* worKsheeT.Cells[baris, 8] = "Pegawai Pembelian";
* }
* else if (worKsheeT.Cells[baris, 8] = "J2")
* {
* worKsheeT.Cells[baris, 8] = "Kasir";
* }
* else if (worKsheeT.Cells[baris, 8] = "J3")
* {
* worKsheeT.Cells[baris, 8] = "Manager";
* }*/
baris = baris + 1;
}
Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("A3:H" + baris);
angkaStyles.NumberFormat = "##,#";
Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:H20");
Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:H2");
Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:H" + baris);
rangeStyles2.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles.Value2 = "'Style Test";
//rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles3.BorderAround2();
rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
k.KoneksiDB.Close();
/*
* MySqlConnection mc = new MySqlConnection(Koneksi.strCon);
* MySqlCommand cmd = new MySqlCommand(q, mc);
* MySqlDataReader rd = cmd.ExecuteReader();
*
*
* int barisS= 2;
* while (rd.Read())
* {
* worKsheeT.Cells[barisS, 1] = rd["KodeBarang"];
* worKsheeT.Cells[barisS, 2] = rd["NamaBarang"];
* worKsheeT.Cells[barisS, 3] = rd["HargaJual"];
* worKsheeT.Cells[barisS, 4] = rd["Stok"];
* barisS++;
* //worKsheeT.Cells[2, 5] = "Kategori";
* }*/
//DateTime dt = DateTime.Now;
worKbooK.SaveAs(filename);
worKbooK.Close();
excel.Quit();
/*ProcessStartInfo info = new ProcessStartInfo();
* info.Verb = "print";
* info.FileName = @filename;
* info.CreateNoWindow = true;
* info.WindowStyle = ProcessWindowStyle.Hidden;
*
* Process p = new Process();
* p.StartInfo = info;
* p.Start();
*
* p.WaitForInputIdle();
* System.Threading.Thread.Sleep(3000);*/
//if (false == p.CloseMainWindow())
//p.Kill();
}
catch (Exception ex)
{
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
